Lets say I have a Customer File and a Customer Maintenance file.
Customer File has the following fields:
Customer Number
Customer Name
Customer Address
Customer Maintenance has the following fields:
MaintCustNumber
MaintType
MaintDate
MaintDescription
I want to join the two files so that I see every Customer File record even in their are no matching Customer Mainteance records. So I figure this is perfect for a left outer join.
Other factors:
I also am prompting for a customer number and MaintDate via parameter fields. The customer number is option, they could enter nothing to get all of them.
I also only want Maintenance records that MaintType = "1".
In my link options I use a "left outer join","Not enforced", and "=". ( have messed with all the "Enforce Join" options with no luck).
After reading several other posts here dealing with left out join pain my selection criteria looks like this:
if hasValue({?CustomerNumber}) then
{CustomerMaster.CustomerNumber} = {?CustomerNumber}
and
( ({CustomerMaintenance.MaintDate} >= {?From Date}) or (isnull({CustomerMaintenance.MaintDate})))
and
( ({CustomerMaintenance.MaintType} = "1") or (isnull({CustomerMaintenance.MaintType})))
Sorry the post is so long but I wanted to make sure I covered everything. This works fine if their are matching records. When their are no Customer Maintenance records of MaintType = "1" I would still like to see the Customer Number, Name, and address on the report but I get nothing.
If somebody could offer this Crystal green horn I would be very happy.
p.s. I took off the "else" portion of my selection criteria, I figured I would put it back in once I got it working. Just assume I always put a customer number in.
Edited by ronhextall - 19 Aug 2010 at 6:52am
IP Logged
