cancel
Showing results for 
Search instead for 
Did you mean: 

Linking Fields In Database Expert

Former Member
0 Kudos

I am using Crystal Reports 2008 and need help with the Database Expert. I have a transaction table with customer # and a master table with several customers. My question is I need to use my transaction table and compare against the master table using the field, customer #. If there isn't a match from the using the transaction table customer # with the customer # of the master file than I need to print the customer # from the transaction table that doesnt have a match. Not sure how to do this, any help would be greatly appreciated.

Thanks Jim Dyer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Sorry this may be lacking in detail but here are the steps.

1, Create a link from the transactions.customer# to the master.customer# (left click, hold and drag)

2. Right click on the line, from the menu choose "Link Options", select Left Outer Join.

3. Place transactions.customer# and master.customer# fields into the details section of the report

4. Add a the suppression formula from below into to the details section (right click on details section, choose section expert, click on the x+2 beside the "Suppress (No Drill Down)" setting

Suppression formula should look like this just substitute the names of your tables and customer# field.

transactions.customer# = master.customer#

The resulting report will only show records where there was no match, the master.customer# should be blank or null. The left outer join link forces all the transactions to be included in your report and if no match is found in the master table then all the fields from the master table will be joined as null values.

Please let me know if you need more detail then what is provided.

Marshall

Former Member
0 Kudos

The above use of a Suppress formula (a) may not work, and (b) should be changed to a record selection.

(a) It may not work because the customer.customer# field will be null when there is no match, so the expression checking for = will not return true or false, it will return null. Whether or not this will suppress the line or not, I'm not sure...

(b) If you put the logic the the format's suppress formula, all of the data will be returned from the database to Crystal. It will all be read and processed, causing a (potentially very) slow report. If instead you use


IsNull({customer.customer#})

for record selection, Crystal should be able to push this to the database, so only the records that you want to report on will be returned to Crystal, making for a (much) faster and more efficient report.

HTH,

Carl

Former Member
0 Kudos

This worked the first time I tried, thank you very much for your help.

Answers (0)