Relation between Tables

Tables and subtables can be linked in two ways: Either the tables are linked using an actual relation at the data source level, or the relationships are defined using filter conditions.

Link via Relations

Many applications pass the relationships between tables so that these links are available in the designer. In the "Select Data Source" dialog these relations are displayed hierarchically, e.g. Customers>Orders>OrderDetails.

If you select the Orders table in this dialog, you have directly placed a Customers table and additionally the sub-table "Orders".

Alternatively, first place the Orders table, then click on "Append a sub-element" in the Object window. Since there is a direct relationship to the "Orders" table in the schema, select "Link via relations" in the dialog. Then select the Orders table.

Figure 5.1: Tables linked by a relation

Link via Filter

There are some typical application cases for the relation via filter condition:

* An SQL database in which the relations are not defined at the server level (which is more common than you might think).

* Two loosely connected tables in which the key can be a combination of several fields.

* A mixed data source, such as a CSV file and an XML file linked by an ID field.

* An application that does not pass the relations between tables.

Proceed as follows to define it:

1.  To define such a relationship, click Append Sub-Element in the Object window to add a sub-table to a defined table. There is no direct relationship between the two tables in the schema, so choose "Link via filter" in the dialog.

Figure 5.1: Select link type

2.  You can now select another table from the database.

3.  In the following dialog box, you specify the filter condition for the sub-element. Fields from the two tables involved are available for the condition. Note that the filter should be able to be translated into a filter expression of the database system for speed reasons. Depending on the data source, you can easily check this in the following dialog.

In this case, the assignment of the required filter is of course very simple. Note that in this case the filter can be executed on the database system:

Figure 5.1: Filter condition for the sub-element