About Relations

The commands in this theme, in particular RELATE ONE and RELATE MANY, establish and manage the automatic and non-automatic relations between tables. Before using any of the commands in this theme, refer to the 4D Design Reference manual for information about creating relations between tables.

Two tables can be related with automatic table relations. In general, when an automatic table relation is established, it loads or selects the related records in a related table. Many operations cause the relation to be established.

These operations include:

• Data entry
• Listing records on the screen in output forms
•  Reporting
• Operations on a selection of records, such as queries, sorts, and applying a formula

To optimize performance, when 4D establishes automatic relations, only one record becomes the current record for a table. For each of the operations listed above, the related record is loaded according to the following principles:

• If a relation selects only one record of a related table, that record is loaded from disk.
• If a relation selects more than one record of a related table, a new selection of records is created for that table, and the first record in that selection is loaded from disk.

For example, using the database structure displayed here, if a record for the [Employees] table is loaded and displayed for data entry, the related record from the [Companies] table is selected and is loaded. Similarly, if a record for the [Companies] table is loaded and displayed for data entry, the related records from the [Employees] table are selected.

In this database structure, the [Employees] table is referred to as the Many table, and the [Companies] table is referred to as the One table. To remember this concept, think of “there are many employees related to one company” and “each company has many employees.”

Similarly, the Company field in the [Employees] table is referred to as the Many field, and the Name field in the [Companies] table is referred to as the One field.

It is not always possible to have the related field be unique. For example, the [Companies]Name field may have several company records containing the same value. This non-unique situation can be easily handled by creating a relation, which will always be unique, on another field in the related table. This field could be a company ID field.

The following table lists commands that use automatic relations to load related records during operation of the command. All of the commands will use existing automatic Many-to-One relations. Only those commands with Yes in the One-to-Many established column below will use automatic One-to-Many relations.

Automatic relations do not mean that the related record or records for a table will be selected simply because a command loads a record. In some cases, after using a command that loads a record, you must explicitly select the related records by using RELATE ONE or RELATE MANY if you need to access the related data.

Some of the commands listed in the previous table (such as the query commands) load a current record after the task is completed. In this case, the record that is loaded does not automatically select the records related to it. Again, if you need to access the related data, you must explicitly select the related records by using RELATE ONE or RELATE MANY.