Link different tables with table relations

  • 6 minutes

TableRelation is a table property that is used to establish a relation between two tables, such as between the Customer table and the Countries table. For example, a customer is located in a certain country; therefore, a relation exists between the Customer and Country tables.

The TableRelation property lets you establish lookups into other tables. This property will show a drop-down menu on pages, where a user can select a value from the linked table.

Screenshot of the TableRelation property drop down menu.

Regular table relation

A regular table relation is a simple relationship between two tables. In this type of relationship, you can specify the table that you want to link with in the TableRelation property. If you specify the table, you will automatically link to the primary key of that table.

Screenshot example of a regular table relation.

Filtered table relation

A regular table relation shows all the records from the linked table. With a filtered table relation, you can limit the records that are displayed by using one or more filters on the table records. As a result, a filtered table relation only shows a subset of the records.

In the following example, the Country/Region code is filtered to show only countries/regions where the EU Country/Region Code field is not blank.

Screenshot of the filtered country/region code table relation.

Conditional table relation

A conditional table relation is a dynamic table relationship because it's not fixed to one table. The link depends on a condition; therefore, depending on the evaluation of a condition, you might relate to one or another table.

A conditional table relation is used in the sales lines of a sales order. Depending on the Type field, the No. field will link to other tables.

Screenshot of a sales line linked to item table.

Screenshot of a sales line linked to resource table.

The following code example shows a snippet of the Sales Line table (table 37), where the No. field is linked to other tables, dependent on the Type field value. This conditional table relation is also using a number of filtered table relations.

Screenshot example of a code snippet of a conditional table relation.

Use option access syntax in formulas

When setting up SourceTableViews, TableRelations, and so on that relate to other tables by table IDs (or, less frequently, specify codeunit IDs to run), developers had to specify the integer ID of the target object. This wasn't user friendly or readable and could be prone to errors.

The AL language now supports using Option Access syntax (names) in formula properties. This removes the need for the hard-coded integer ID values and improves the readability and the maintainability of the code.

So instead of:

al-languageCopy
SourceTableView = where("Source Type" = const(18));

It will be possible to use the option name:

al-languageCopy
SourceTableView = where("Source Type" = const(Database::Customer));