match data from one record to another record
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22701
Printed Date: 19 Apr 2024 at 12:09pm
Topic: match data from one record to another record
Posted By: OCTS
Subject: match data from one record to another record
Date Posted: 15 Nov 2018 at 7:51am
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.
|
Replies:
Posted By: DBlank
Date Posted: 15 Nov 2018 at 8:13am
|
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?
|
Posted By: OCTS
Date Posted: 15 Nov 2018 at 8:26am
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?
|
Posted By: DBlank
Date Posted: 15 Nov 2018 at 8:36am
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 ''
|
Posted By: OCTS
Date Posted: 15 Nov 2018 at 11:54am
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.
|
|