Comparing Fields within a Table

[dg3249]

I have a table called Orders. With two fields Order_id and Parent_order_id. You can see that order_id 8122 is an original order because Parent_order_ID is -1 and the others are split orders from the original order.
I am trying to write a formula that will show beside the original order a comment saying "split orders 8123,8124"
I need to show this beside all orders that are split..

The fields look like this,
Order_id Parent_Order_ID
8122 -1
8123 8122
8124 8122
7856 -1
I would like my report to look similar to this;

Order Contractor
8122 (Split Order 8123,8124) Mary Smith
7856 Jim Bob

 

[mmaz]

One way to do it would be to display on your main report only the records with a parent order_id of -1.

Then you could create a linked sub report to show (Split Order 8123, 8124), if applicable.

Or maybe it would be possible to link the table Orders to itself. Then, for each order_id, you could build a string to display its parent_order_id and you would not need a sub report. I've never tried that in Crystal Reports, though.

Hope that helps,

Marie

 

[dg3249]

Thank you for your response, I am trying to create a Sub Report, but I am having trouble writing the actual query to only show the split orders.

 

[mmaz]

I think I would do something along the lines of:

StringVar splitOrders
splitOrders = "(Split Order " + Order_Id

if Parent_Order_Id = Previous(Parent_Order_Id) then
splitOrders = splitOrders + ", " + Order_Id
end if

splitOrders = splitOrders + &quot"