Linking Tables
Whenever there are two or more tables, they need to be linked so that Crystal Reports knows how they are related. For example, a pet store that wants to print a list of its products by Animal ID needs to build a report using an animal table and a product table. To match the product to the appropriate type of animal, both tables will be linked by the Animal ID. It would be impossible to determine which products are associated with which animal without this link. The Links dialog box, shown in Figure 13-2, sets the linking fields.
Figure 13-2. The Links tab.
When the Link dialog box is first displayed, it creates default links. This is the equivalent of Crystal’s “best guess” for the relationships between the tables. In an effort to make it easier for you, Crystal Reports tries to figure out which fields in each table should be linked to each other. It does this based upon indexes and fields that have the same name and data type.
To get the most benefit out of the auto-arranged links, design your tables with field names that use a consistent naming convention and have well thought out indexes. This will result in a higher probability that Crystal Reports will create the appropriate default links.
The default links are not set in stone. You are free to delete or add more, according to your needs. To delete a link, simply click on it (to select it); then click on the button labeled Delete Link. You can also just press the Delete key after selecting it. To add a new link, drag and drop the field from one table onto the matching field in the other table.
There are a couple of buttons that are helpful for managing links. The Auto-Arrange button rearranges the tables into an easier-to-read layout, which is useful when handling a report with many tables. A multitude of tables makes difficult the visualization of their relationship with one another and the overall structure. The Auto-Link button rebuilds the links based on whether you want to link by field name or by index. This comes in handy for undoing any new links you added, should you want to start from scratch. The Clear Links button removes all the links between the tables. The Link Options button opens the Link Options dialog box, shown in Figure 13-3.
Figure 13-3. The Link Options dialog box.
The Link Options dialog box establishes the type of relationship between the two fields. It sets the type of join (Inner, Left Outer, Right Outer, or Full Outer) as well as how the fields are compared (equal to each other, less than, etc.). Table 13-2 shows a list of the different linking options and how they affect the resulting data.
Table 13-2. Join Options
| Join Type | Resultset Description |
|---|---|
| Inner Join | Records from the left table are matched with records from the right table. Only records with an exact match are included. |
| Left Outer Join | All records from the left table are included. Field values are included if there is a matching record in the right table . NULL values are stored in the corresponding fields if there is no matching record in the right table.. |
| Right Outer Join | All records from the right table are included. Field values are included if there is a matching record in the left table. NULL values are stored in the corresponding fields if there is no matching record in the left table. |
| Full Outer Join | Every record from both tables is included. When records from both tables match, the fields in the new recordset are filled in as normal. The other fields are set to NULL if there is no matching record for one of the tables. |
The Enforce Join options let you determine whether a field is forced to be included in a SQL statement. For example, if you join together Table A and Table B, but you don’t add and fields from Table B to the report, then Crystal Reports will drop Table B from the SELECT statement. In certain advanced circumstances you might still want Table B to be included to force the relationship between the two tables to be maintained. The default is Not Enforced and only tables that have data on the report are included in the SELECT statement. The other three options can force either the From, To or Both tables to be included even when their data isn’t used.
The Order Links button sets the order in which the links between the tables are created. This is used only when there is more than one link shown. The tables will be linked automatically, in the order that they are shown in the dialog box. The default linking order processes the links on the left before the links on the right. Changing the default linking order is useful when there is a hierarchy of tables joined together and the order to which they are joined is important. This can happen when you are using a link to return a subset of records from two tables, and these records must therefore be linked to another table. Changing the order of the links changes the resulting data.
Question: I’m using .NET 2005 and I can’t get the left outer join to work. No matter what I do, it only prints parent records that have a matching child record. How do I fix this?
Answer: Unfortunately, this is a bug with .NET 2005 and there is no way to fix it. It wasn’t fixed in Service Pack 1 either. However, left outer joins are working in .NET 2008.
