Question Crystal Report - Linked table

[Arg81]

Good afternoon

I'll try to explain this as best I can.

Have a report that shows main table fields and then "links" to lookup tables
I have 2 fields "Technician A" and "Technician B"
In my DataSet, there are 2 datatables, TechA and TechB (both with EmployeeID, EmployeeName)

Technician A is always going to have a value. B will only be selected for larger projects.

In Database Expert, I have linked TechnicianA to EmployeeID (TechA) and TechnicianB to EmployeeID (TechB)

On the report, I have dragged EmployeeName from both dataTables.

ISSUE
If the project DOES have a value set for EmployeeB, the report loads OK.
If the project DOES'NT have a value set for EmployeeB, the report loads Blank.

If I remove EmployeeName from the report (TechB), the report loads OK.


There's obviously an issue with the link, any ideas? I need the report to load whether or not TechnicianB has a value or not.

 

[Arg81]

If I set this to a LeftOuterJoin (right join is greyed out) then the report loads... but it repeats for the number of employees that are in that DataTable, in this case 7 Technicians so it will repeat 7 times....

 

[Arg81]

Left Outer Join works perfectly if there is no TechnicianB.
It'll load the report but leave the field blank.

If I try it for a project that has a TechnicianB, that's when it'll repeat for the amount of users in the TechB dataTable......

 

[Arg81]

Now solved...

Left outer join is correct.

The reason for the duplication was due to me linking my CustomerID to the 2 Customer tables I had, both of which had 2x Primary Keys.
Instead I needed to link the main table to my first customer table on CustomerID, then link this to the second customer table on CustomerID.