Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare records to get true or false

Status
Not open for further replies.

veles

Technical User
Sep 1, 2006
57
CA
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
 
 http://files.engineering.com/getfile.aspx?folder=1dcbe027-3a78-4ea3-83bd-9f2ac447b8b3&file=1.gif
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
 
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
 
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
 
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
 
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
 
Yes, Table1 and Table2 are left joined. All Table1 values are displayed no matter if there is a value in Table2 or not
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top