Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Comparing Data in two tables

Posted on 2010-09-15
13
Medium Priority
?
580 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:nursecore
13 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 33686069
IsNull({table2.field}) = True
0
 
LVL 19

Accepted Solution

by:
GJParker earned 2000 total points
ID: 33686083
Add in a selection formula of

IsNull({table2.customer_number})
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33688911
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 35

Expert Comment

by:James0628
ID: 33689275
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
 

Author Comment

by:nursecore
ID: 33693730
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
 
LVL 35

Expert Comment

by:James0628
ID: 33698816
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
 
LVL 35

Expert Comment

by:James0628
ID: 33698822
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
 
LVL 35

Expert Comment

by:James0628
ID: 34289303
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
 
LVL 35

Expert Comment

by:James0628
ID: 34298874
OK.  Fair enough.

 James
0
 

Expert Comment

by:thermoduric
ID: 34328683
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question