We help IT Professionals succeed at work.

Comparing Data in two tables

Paula Ward
Paula Ward asked
on
599 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?
Comment
Watch Question

Top Expert 2010

Commented:
IsNull({table2.field}) = True
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
CERTIFIED EXPERT

Commented:
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
Paula WardController

Author

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.
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
OK.  Fair enough.

 James
thermoduricModerator

Commented:
Starting the automated closure procedure to implement the recommendations from the CV.

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.