When writing a report using MET/CAL database tables in Crystal Reports the tables must be linked. In MET/CAL the asset number is the unique identifier (default index). When an asset it added to inventory a unique identifier tag (MTAG) is assigned to the asset. The MTAG links the Inventory table to the Calibration, Standards, Repair, Location and Calresults tables. So for example the MTAG for an asset in Inventory is linked to the MTAG for that Assets Calibration in the Calibration table and the MTAG for the end user of the Asset in the Location Table.
When linking, there are eight possible Join types in Crystal Reports:
Equal [=] join.
The result set from an Equal join includes all the records where the linked field value in both tables is an exact match.
Left Outer [=(+), *=] join.
THIS IS NORMALLY THE CORRECT JOIN FOR METCAL REPORTS. The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table.
Right Outer [(+)=, =*] join
The result set from a Right Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary table.
Greater Than [>] join
The result set from a Greater Than join includes all records in which the linked field value from the primary table is greater than the linked field value in the lookup table.
Less Than [
The result set from a Less Than join includes all records in which the linked field value in the primary table is less than the linked field value in the lookup table.
Greater Than Or Equal [>=] join
The result set from a Greater Than Or Equal join includes all records in which the linked field value in the primary table is greater than or equal to the linked field value in the lookup table.
Less Than Or Equal [
The result set from a Less Than Or Equal join includes all records in which the linked field value in the primary table is less than or equal to the linked field value in the lookup table.
Not Equal [!=, <>] join
The result set from a Not Equal join includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table.
If smart linking in Crystal Reports is used the linking will be equal joins (Figure 1). This is not the join type we want to use in most reports. Notice that Location is linked to Inventory only through Calibration. With equal joins when a particular item in inventory does not have a Calibration and/or Location record the join condition is not satisfy and the item will not show up in the report (figure 2). To avoid this we need to link the tables using Left Outer join.
By using left outer join all rows in Inventory, the Primary (left) table will be included whether or not there is a row in calibration or location that satisfies the join condition.
To set up the left outer join in the example shown in the first figure, select the link from the Calibration table to the Location table and delete it. Next, Select and drag the MTAG from the Inventory table to MTAG in the Location table as in the figure below.
Next select the MTAG from the Inventory table to the Calibration table, select Options. change SQL Join Type from Equal to Left Outer and click on OK.
Next, select the MTAG link from the Inventory table to the Locaion table, select Options. change SQL Join Type from Equal to Left Outer and click on OK. Calibration and Location are now linked directly to Inventory.
Now, if a particular row in Inventory has no matching row in Calibration and/or Location, the fields in the join corresponding to INventory will contain the NULL value.
