Comparing Data in two tables

I have a Crystal Report that has two tables with common data (Customer info) with a Link on the Customer Number.  I have a Left Join on this link.  I need to be able to filter the data that is in Table1 that is NOT yet in Table2.  Can someone tell me how to do this?
Paula WardControllerAsked:
Who is Participating?
 
GJParkerCommented:
Add in a selection formula of

IsNull({table2.customer_number})
0
 
JayConverseCommented:
IsNull({table2.field}) = True
0
 
mlmccCommented:
You can't do it with a selection formula in Crystal.  Crystal turms the left join into an equal or inner join when you filter on the second table.

You can do it through suppression based on the null field

mlmcc
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
James0628Commented:
Try the IsNull test that the others suggested.  What mlmcc said about LEFT OUTER JOIN's in CR may be generally correct, but in my experience, doing an IsNull test on a field in the joined table is one exception to that.  I've done it before, and just tried it again, and it works for me.  In the test I just did, I did a LEFT OUTER JOIN from tableA to tableB and then used IsNull on a field in tableB as my record selection, and got the records from tableA with nothing in tableB.

 James
0
 
Paula WardControllerAuthor Commented:
Actually I was able to use the second solution above.

Add in a selection formula of

IsNull({table2.customer_number})

Now I have another issue to resolve.  I needed to add a detail table they has the items billed to a customer.  When I run the report it shows the correct information, but shows it numerous times because (table3) has several detail items for each customer.  I just want it to show one line per customer. How can I do this??  I use table3 to filter out clients that had billing items.  It is only used in the selection formula.
0
 
James0628Commented:
Hmm.  This statement:

 > ... shows it numerous times because (table3) has several
 > detail items for each customer.

 seems to contradict this:

 > I use table3 to filter out clients that had billing items.

 If you're using the record selection to _exclude_ the customers that had billing items, then there won't be "several detail items" for the customers on the report.  There won't be any detail items.

 Unless there are different types of details and you're only excluding the customers that had certain types of details, but leaving the customers that had other types of details.  If that's the case, the following should help.

 The fact that you're only referencing table3 in the record selection formula doesn't matter.  You're reading the detail table now, so the report will read one record for each detail, and if you don't exclude those details from the report, you'll get multiple detail records for those customers.

 Just add a group on the customer ID field and put the fields that you want to see in the group header or footer, then suppress the detail section.  The group header and footer will be produced once for each customer.

 James
0
 
James0628Commented:
Oh, yeah.  For the record, the first solution (IsNull({table2.field}) = True) is really exactly the same as the second one.  It's a slightly different form, but it's really the same solution.  Just something that you might want to keep in mind when you're ready to close the question and assign points.

 James
0
 
James0628Commented:
Technically, JayConverse posted the same solution 2 minutes before GJParker, although Jay didn't specifically say that it was supposed to be a record selection formula.

 James
0
 
James0628Commented:
OK.  Fair enough.

 James
0
 
thermoduricModeratorCommented:
Starting the automated closure procedure to implement the recommendations from the CV.

- thermoduric -
EE Community Support Moderator
http://www.experts-exchange.com/Q_26662158.html

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.