match data from one record to another record

data is as follows:

ID     LOCATION     CONTROL_NUMBER     INGATE_ID
392,001     POLE 7           384287               0
393,341                      385581          392,001

Trying to create a formula which would add another column called OutLocation, which if INGATE_ID is greater than 0 would match the INGATE_ID (392,001) to the ID (392,001 in the above row) and return the LOCATION value (POLE 7) to the row below.

Edited by OCTS - 15 Nov 2018 at 7:52am

is this just for display or do you have to then do something more with the result? Is this evaluation only occurring for the next row or can it be more than one row apart?

This would be for display purposes. In this report it would be one row apart, but I also want to have it displayed on a form that would have it displayed in a field.

The row that has a 0 for INGATE_ID is the record that records the receipt of an inventory item. We have a form that is printed which is like a Proof of Delivery and it has a field where the LOCATION can be printed. The record where the INGATE_ID equals the ID of another record is the record that records the inventory item leaving. We also would like the LOCATION to be printed on that form. The only way I can see to do it is to match it to the record that recorded it coming in and pulling the data from there. Is this even possible with Crystal reports?

consider using a formula field to display using either Previous() or Next() functions

Something like this maybe...?

If Previous(table.ingate_id)=0 and table.Ingate_id=previous(table.Id) then previous(table.location) else ''


Edited by DBlank - 15 Nov 2018 at 8:36am

This worked for the report but not for the form.

I was able to come up with a solution for the form by creating a subreport linking the (table.ID) to the (table.Ingate Id).

thank you for your assistance.