Video script

Today, we're going to learn how to create relations and see how they work.

4D is a relational database management system. Relations between tables are shown by links between the foreign key and the primary key, which is a unique identifier.
The power of 4D relations is based on several automatic features that greatly increase functionality, without writing a single line of code.
These automatic features can be, however, disabled when desired; they can be configured and used entirely by programming.

Nothing could be simpler than creating a relation: just click on a field (that will be the foreign key) and drag over to another field (the primary key).
You need to remember two things to make a relation:

• The fields must be the same type
• You always go from the field of the Many table (foreign key) to the field in the One table (primary key).

Example: if we try to draw from the intervention Time field towards an Alpha or Text field, 4D won't create the relation.

In the present case, we know that interventions are assigned to a technician, so one technician can have several different interventions. In this case, we draw the relation directly from the technician's initials field in the Interventions table to the ID field of the Technicians table.

To change the properties of a relation, just double-click directly on it to display the Inspector.

For this first approach, we want 4D to do the work for us and make the joins (that is, the retrieval of records from related tables) automatically without any programming.
To enable this automatic feature, we need to configure the relation as follows:

• Select "Automatic" instead of manual for the "Many to One" options group.
• Check the "Prompt if related one does not exist" box.
• And in the "One to Many" options, check the "Auto assign related value in subform" box.

When you choose these options, 4D carries out the requests required to load the related records into memory; in particular, when displaying records.

• When entering an intervention, the automatic "Many to One" relation lets you have the record loaded in memory for the technician whose ID was indicated. Once this record is loaded, we can display its information on screen, as well as print it or even modify it.
• When consulting the technician's record, the automatic "One to Many" relation lets you have the list of interventions performed by this technician loaded in memory and thus be able to display this list without any programming being necessary.

We'll come back to the other parameters for relations found in the Inspector a little later on.

To complete these automatic features for the relations, you can use the Deletion Control option in the Inspector. This lets you configure the way 4D handles the deletion of a record in the primary key table, for example, in our case, the Technicians table:

• If we choose "Leave related many intact": this means we are responsible for managing this ourselves using programming; 4D doesn't perform any controls.
• If we choose "Delete related many": when we delete a Technician, we automatically delete all the related records in the Many table, in order to avoid leaving orphan records in the Interventions table.
• If we choose "Cannot delete if related many": as long as there are related records in the Interventions table, we cannot delete the associated Technician.

Now we're going to put all these different options into practice in the next video which covers how to enter information using relations.