Wednesday, June 3, 2015

Crystal Reports : Using JOINs to Test for Missing Records

Take this example; you want to print a list of customers without history. Sounds easy, right? But you will quickly find out this is trickier than at first glance.

We have two tables, CONTACTS and HISTORY. They are joined on a field called CUSTNUM, like so; CONTACTS.CUSTNUM = HISTORY.CUSTNUM.

However, when you define your table linking in Crystal, it assumes you want an INNER JOIN, which will only return rows from CONTACTS where there is at least one corresponding row in HISTORY.

1. Go into the Visual Linking Expert and right-click on the JOIN line between your tables. Select Options.
2. Change the JOIN type to RIGHT OUTER. Ok your way out.
3. Now, provide a selection criteria (in the Select Expert) as a formula and use the following:

ISNULL({HISTORY.CUSTNUM}) = TRUE

Which is the Crystal Syntax for HISTORY.CUSTNUM IS NULL (for those SQL folks out there).

You'll notice now that your report is only printing Customers where there are NO corresponding rows in HISTORY.

4 comments:

replica chanel handbags said...

This most particular and personal of signatures was instantly transformed into a universal symbol of modernity in the hands of Georges Vuitton: it is one of the first exercises in elevated branding and a defining sign of a global culture to come.

Viora care said...

Things, things, things When the country's just by rolex swiss replica watches, netmail or possibly in person, do not be worried to make sure you demand all the carrier whatever things which might appear usually in the mechanisms for seeking out and additionally selecting any cheap replica watches uk arranged. A joint of bedroom furniture this really is like iconic and additionally recognized for the rolex replica watches uk recliner, might bear satisfied at your house and turn into inherited into your home consistently on end. Accordingly, the country's advisable just spending not many extra time making sure of your selected recliner is normally a superior quality rolex replica uk, which offers continual consolation and additionally trend. For 1997, radar, the agency celebrates the country's 80th wedding anniversary for the dinner table. Subsequently after nearly four dozens of years in the form of rolex replica routine carrier, the agency for 1957 along with the supplier Rado devices placed the most important keep an eye on selection. For 1962, the most important available about really difficult shiny steel utilize oval-shaped usually music artist create, was first an exceptional financial success message justification.

PLC training in chennai said...

WIZTECH Automation, Anna Nagar, Chennai, has earned reputation offering the best automation training in Chennai in the field of industrial automation. Flexible timings, hands-on-experience, 100% practical. The candidates are given enhanced job oriented practical training in all major brands of PLCs (AB, Keyence, ABB, GE-FANUC, OMRON, DELTA, SIEMENS, MITSUBISHI, SCHNEIDER, and MESSUNG)

PLC training in chennai
Automation training in chennai
Best plc training in chennai
PLC SCADA training in chennai
Process automation training in chennai
Final year eee projects in chennai
VLSI training in chennai

Hua Cai said...

swarovski jewelry
michael kors outlet
longchamp pliage
gucci outlet
nike trainers uk
jordan shoes
longchamp solde
nike air force 1
tiffany and co
swarovski crystal
michael kors outlet online
louis vuitton handbags
cheap oakley sunglasses
louis vuitton outlet
coach outlet online
new balance outlet
valentino shoes
ray ban sunglasses sale
oakley sunglasses
michael kors outlet
louis vuitton handbags outlet
mulberry handbags
longchamp handbags
montblanc pens
polo ralph lauren
true religion outlet
prada outlet
ray ban sunglasses
tory burch outlet online
tiffany outlet
versace sunglasses on sale
rolex watches for sale
michael kors outlet
mac cosmetics
nike air huarache
20160716caihuali