About Relations

4D - Documentation   Français   English   German   Spanish   4D v11 SQL, Command Theme List   4D v11 SQL, Command Alphabetical List   4D v11 SQL, Constant Theme List   Back   Previous   Next

version 6.0


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.

Using Automatic Table Relations with Commands


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.

CommandOne-to-Many established
ADD RECORD Yes
ADD SUBRECORD No
APPLY TO SELECTION No
DISPLAY SELECTION No
EXPORT DIF No
EXPORT SYLK No
EXPORT TEXT No
EXPORT DATA No
MODIFY RECORD Yes
MODIFY SUBRECORD No
MODIFY SELECTION Yes (in data entry)
ORDER BY No
ORDER BY FORMULA No
QUERY BY FORMULA Yes
QUERY SELECTION Yes
QUERY Yes
PRINT LABEL No
PRINT SELECTION Yes
QR REPORT No
SELECTION TO ARRAY No
SELECTION RANGE TO ARRAY No

Using Commands to Establish Table 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.

See Also

CREATE RELATED ONE, GET AUTOMATIC RELATIONS, GET FIELD RELATION, OLD RELATED MANY, OLD RELATED ONE, RELATE MANY, RELATE MANY SELECTION, RELATE ONE, RELATE ONE SELECTION, SAVE RELATED ONE, SET AUTOMATIC RELATIONS, SET FIELD RELATION.


4D - Documentation   Français   English   German   Spanish   4D v11 SQL, Command Theme List   4D v11 SQL, Command Alphabetical List   4D v11 SQL, Constant Theme List   Back   Previous   Next