Record selection criteria - First two orders

[jercisneros]

Hi all, I want to create a report the shows the customer creation date:
{CUSTOMER_MASTER.DATE_CREATED}
by Customer ID:
{CUSTOMER_MASTER.CUSTOMER_ID}
and want to report when this customer ships the first and second orders only. The shipping information would be in a table:
{SO_MVMNT.TABLE}
and would have a status of 4 (in warehouse) or 8 (ready to invoice).
How do I create a record selection criteria to where in selects only those first and second orders that have shipped from the Sales Order Movement table?

I hope I am being clear on what I want to accomplish. Any help would be greatly appreciated.

BTW Using CR.8

Thanks,
Jerry

 

[Reebo99]

I'm assuming the 2 tables are linked by {CUSTOMER_MASTER.CUSTOMER_ID}?

Group by {CUSTOMER_MASTER.CUSTOMER_ID}. Put {CUSTOMER_MASTER.CUSTOMER_ID} and {CUSTOMER_MASTER.DATE_CREATED} in the group header.

Put the status field from {SO_MVMNT.TABLE} in the detail. In the record selection formula {status field from {SO_MVMNT.TABLE}} in [4, 8].

Hope this helps.

Reebo
Scotland (Going mad in the mist!)

 

[jercisneros]

Yes, they are linked by {CUSTOMER_MASTER.CUSTOMER_ID}. But, my question is, how do I get the 1st and 2nd order from {SO_MVMNT.TABLE} if there are more than 3 order details?

 

[Reebo99]

Are there any distinct values for the first 2 records in {SO_MVMNT.TABLE}. If not, what information is held in this table (example). Reebo
Scotland (Going mad in the mist!)

 

[jercisneros]

This table has a lot of fields regarding the Sales Order movements. It has item codes, quantities, dates, weights, etc.

Example:

Order# 123456

Customer Name
Ship To Address
Ship To City, State, Zip

Order Date: 2/25/03

Order Detail:
Item #: Description: Qty: Weight:
9999999 XYZ PRODUCT 3 45
9999998 YZX PRODUCT 1 55
9999989 YYY PRODUCT 6 120
9999991 ZZZ PRODUCT 5 72

Total Quanity: 15


I hope this further explains what you need.

Jerry

 

[Reebo99]

Jerry,

Sorry for not getting back sooner. Right, this should work, but it might be a bit slow.

Create a group based on customer id.
under 'Database' click 'perform grouping on server'
If you have a field within orders database for date, then create a group based on this field.

Create a formula (in basic syntax) like :

dim counter as number
if previousvalue({customerID}) <> {customerID} then counter = 1
if previousvalue({customerID}) = {customerID} and counter = 1 then
counter = 2
else
counter = 0
end if
formula = counter

The above should allocate the first 2 instances as 1 and 2 with everything else being allocated 0.

In selection formula put:
{Formula} in [1,2]

Might work, tell me how you get on.


Reebo
Scotland (Going mad in the mist!)