4D v14.3

About Relations

Home

 
4D v14.3
About Relations

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.

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

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.

 
PROPERTIES 

Product: 4D
Theme: Relations

 
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

 
ARTICLE USAGE

4D Language Reference ( 4D v12.4)
4D Language Reference ( 4D v11 SQL Release 6)
4D Language Reference ( 4D v14 R3)
4D Language Reference ( 4D v14 R2)
4D Language Reference ( 4D v13.5)
4D Language Reference ( 4D v14.3)
4D Language Reference ( 4D v14 R4)