Compare records to get true or false

[veles]

Hi guys,

I need some help on a formula that compares records

I have {table1}that has record ID like 1, 2, 3, 4 .....

In another table {table2}there is a field that uses the record ID from{table1} for a parent ID

So a record in {table2} will have a parent ID matching one of the record ID in {table.field1}

I need a formula (probably it will be a variable) that will run the parent ID field through the {table.field1} and when it finds the match to dislpay a meesage something like "match".

I hope I explained it so it can be understood.

Thanks in advance

 

[hilfy]

Try this:

1. Add Table1 and Table2 to the report.
2. Link from {Table1.ID} to {Table2.ParentID}.
3. Right-click on the link and go to "Link Options". Make this a Left Outer join. This will give you all of the records in Table1, regardless of whether there is a matching record in Table2.
4. Create a formula that looks something like this:

if not IsNull({Table2.ParentID}) then "Match"

Use this formula on your report to indicate whether the two tables match.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[veles]

Thanks for the answer.

I need to display all records as in the attachment. Basically I need to use the value of the parent ID somehow to run through the records in {table1} and find the match. Once the match is found to display the "match" string

 

[hilfy]

If you want to show all of Table2 and the matches from Table1, reverse the link between Table1 and Table2. Then change the formula to something like this:

if not IsNull({Table1.ID}) then "Match"

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[veles]

The issue is that the parent ID from Table2 is from another record, see below

Table1 Table2 Formula Result
1 null match
2 null
3 1
4 null
5 null

Table1 record ID 1 is the parent ID in Table2 for record 3.

What I need is a formula to use the 1 from Table2 find the match in Table one and display "match

 

[hilfy]

So you're already joining from table 1 to table 2 on another field?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[veles]

Yes, Table1 and Table2 are left joined. All Table1 values are displayed no matter if there is a value in Table2 or not

 

[hilfy]

And now for the ones that have a Table2 record, you want to know whether there is a corresponding parent record that is also in Table1, but is not based on the way that Table1 and Table2 are currently joined? If that's the case, try this:

1. Add a second copy of Table1 to the report. When you do this, Crystal will show a warning and ask if you want to "alias" the table. It will then add a second copy of the table with "_1" on the end of the table name.

2. Left join from {Table2.ParentID} to {Table1_1.ID}.

3. The formula you want will now look like this:

if not IsNull({Table1_1.ID}) then "Match"

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com