CRXI: Need help removing duplicate workorders, only showing the one with the highest sequenceID less than 900

Question

I've been struggling trying to figure out how to deal with duplicate work orders showing up on a report in Crystal Reports XI. Each work order has a sequenceID and I want to show the record that has the highest sequenceID < 900, and if there is duplicates that have a sequenceID higher than 900, show both the highest record with sequenceID < 900 and the records >= sequenceID 900.

Ex Output:

WorkOrderNumber            SequenceID
WO1234                        4
WO1234                        6
WO1234                        902

Output Desired:
WorkOrderNumber            SequenceID
WO1234                        6
WO1234                        902

Ex2. Output:

WorkOrderNumber            SequenceID
WO1234                        20
WO1234                        50

Output Desired:
WorkOrderNumber            SequenceID
WO1234                        50

Answer 1

If you add 2 groups in your report: 1.) Workordernumber 2.) SequenceID

So your data is sorted by these fields. Then we need to find the max sequenceIds for a work orders below 900 and above.

Then you could create 2 formulas to handle this:

1.) If sequenceID < 900 then sequenceID else 0

2.) If sequenceID >= 900 then sequenceID else 0

You would then get the max result of each formula based on the workordernumber group by adding a summary or also add this to your formulas above. You would then suppress the sequenceID group if it did not equal one of the max summary value formulas. You could handle this by adding a simple formula in the suppress condition formula.