Solved

Comparing Data in two tables

Posted on 2010-09-15
13
524 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 500 total points
ID: 33686083
Add in a selection formula of

IsNull({table2.customer_number})
0
 
LVL 100

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
 
LVL 34

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

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 34

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 34

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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now