Selection Formula

[Randygk]

I need a formula to look further to see if the same item exists in an invoice. I am extracting items which have been backordered or cancelled, if an item has been cancelled I need to look further on the order and see if it was reentered on another line if it was I do not want it to show on the report.

order# line_no Item_id disposition qty_ord qty_ship
110003 1 27089 5 5
110003 2 30985 C 10 0
110003 3 30986 B 100 0
110003 4 27077 15 15
110003 5 10105 B 25 0
110003 6 21117 5 5
110003 7 25755 C 100 0
110003 8 23777 500 500
110003 9 30985 10 10
110003 10 30355 1 1

I want the report to show all with a disposition of B and only those which do not show up on another line as being shipped.

order# line_no Item_id disposition qty_ord qty_ship
110003 3 30986 B 100 0
110003 5 10105 B 25 0
110003 7 25755 C 100 0


Thanks,
Randy

 

[FVTrainer]

I don't think you can do that in the record selection formula. The record selection formula can't search the entire recordset to figure out if another record should be included.

Your options within Crystal would include doing something like the following:

Group by Order # and Item_ID. In the Item_ID footer, sum the qty_ship field. Then in the group selection formula, only select records where the sum(Item_ID.qty_ship) = 0 or Max(disposition) = "B".

I'm not sure if that will cover all the bases, but it should get you thinking in the right direction.

If you're using CR 9, then using a command object as the datasource would give you more flexibiltiy in writing the sql statement in a way that would accomplish your goal.

Outside of Crystal, you could get the exact recordset you
want using a stored procedure...but that's my standard response to most data collection issues. And probably not that helpful.

 

[Randygk]

But on item 30985 first entry it is a C disposition and 0 shipped and the second entry 10 were shipped I don't want the first entry to show up since they were shipped on the second entry. so in this case neither of the item 30985 would show up on the order.

 

[lbass]

I think FV's approach will work, as long as you create the two groups on Order and ItemID. The group selection formula (report->edit selection formula->GROUP) should look like:

Maximum({table.disposition},{table.itemID}) = "B" or
sum({table.qtyshipped},{table.itemID}) = 0

-LB