Table Linking

[pomster]

Hi everyone,
I'm using CR10 on SQL2000 and am having problems with table linking. I have 2 tables, SerialMaster and CallHeader the link between the 2 tables is SerialNo. I am reporting on serialno in SerialMaster and CallDate in CallHeader. I need to link the tables so that even if there is no record of serialNo in CallHeader I still get SerialNo from SerialMaster. (hope this makes sense)

Thanks,

David

 

[Katy44]

You need to make the link a left outer join (right click on the link and choose options). Check the arrow is pointing the right way (from SerialMaster to CallHeader) - if not reverse the link.
However, if you have any selection criteria using fields from the CallHeader table this will still appear not to work.

 

[scottostanek]

>However, if you have any selection criteria using fields from the CallHeader table this will still appear not to work

You can get around this limitation by making the select check to see if the field is not null before it uses the field.

IE
(If not isnull({CUST-PRODUCT.Cust-Product-Name}) then {CUST-PRODUCT.Cust-Product-Name} startswith ["Misc", "Equip", "Labor"]) else True)

The else True just says to ignore this condition altogether if the left-outer table returns nothing.

Scotto the Unwise

 

[lbass]

Scotto,

Your solution would eliminate records from the left table where {CUST-PRODUCT.Cust-Product-Name} was not null but did not meet your criteria, e.g., {CUST-PRODUCT.Cust-Product-Name} startswith "Supplies". Instead of using a select on the right hand table, to display those results which meet the criteria, I would use a formula in the detail section like:

if isnull({table.field}) or
not({table.field} in ["x","y","z"]) then "" else
{table.field}

-LB