Entry and deletion

With relations, you can use a wildcard (@) anywhere in the foreign key to display the records of the primary keys. Here are some examples:

• ab@ = shows all records starting with "ab"
• @ab = shows all records ending with "ab"
• @ab@ = shows all records containing "ab"
• @a@b@ = shows all records containing "a" and containing "b" ("a" first and then "b" next) 

In this video, we're going to learn how to enter and delete data using relations and their properties.

In previous sections, we imported records of the Technicians and Interventions tables.

Now we're going to carry out entry while taking into account the relations set up in the previous section and more specifically the automatic Many to One relation, the "Prompt if related one does not exist" option and the automatic One to Many relation.

• First go to the form to enter the first record of the Interventions table.
• Go to the ID_Technicians field and enter a new technician's ID.

The XY ID does not actually exist.

4D uses the automatic Many to One relation to check whether this ID is in the Technicians table.

Since it doesn't exist, 4D offers to create it.

This verification takes place because we checked the "Prompt if related one does not exist" box. This option manages database integrity during data entry to avoid creating orphan records.
If you get an error, click on "Try Again" and enter an existing code.

If we want to create a new code, for example this one:

• We can just click on "Create it"
• and fill in the Technicians record with their first and last name
• then validate the record.

This record appears above the Interventions record. When we close it, the Interventions record is shown again.

Thanks to this automatic relation, and without any programming, we have just entered data in multiple tables with automatic integrity control.

We can use this relation again to simplify data entry. Since we don't know all the record IDs for each table, we can just start entering an ID and then add the @ symbol to get a list of all the records that "start with" the same letters or numbers.

We saw that among the imported technicians, there were several records beginning with "M" so we can just enter "m@" to see all the IDs the match.

Next we'll see that it is possible to display the name of the technician concerned here.

You can also enter just the @ symbol and all the IDs including the one just created will appear automatically.

@ is the wildcard character which corresponds to the * in other tools so it can be used at the beginning, middle or end of a character string.

We're going to modify the relation in the Structure so that we can see the last name of the technician concerned.

By clicking on the relation, we can then choose the Last name as Wildcard Choice; the "Auto Wildcard support" box is automatically checked.
Making this change means that now when we enter the @ symbol, we get both the technician's ID and his or her last name in parentheses, which makes entering data even easier.

Of course, these automatic entry modes can be completed by programmed functions. This will be covered in other videos later.

Record deletion also depends on the configuration of the relation. In this case, it's the "One to Many" relation and, more specifically, the Deletion Control setting that is taken into account .
Now that there is a relation between Interventions and Technicians, we can try to delete a technician (deliberately or by hitting the wrong key). What will happen?

If we take the NEY technician and try to delete it (by hitting backspace), 4D asks for confirmation. OK.

Automatically, 4D check Interventions to see whether this technician actually exists and since we checked the "Cannot delete if related many" box, 4D refuses to delete this technician in order to preserve database integrity.

The + here (at the bottom left of the error message) usually gives you additional information about the cause of the error. So, we have the first "Cannot delete" message like we see here and below we can see the reason why it is not possible to delete it. You can click on Continue.

Normally, we haven't linked this technician with any interventions so we should be able to delete him and, so we see that he has been removed.

Note that the deletions mentioned above also depend on the record access mode (read orwrite) and on any locking of the records by other users or other processes.

This remains valid regardless of the way the records are deleted (whether by a user action or by programming).

So by analyzing your database specifications, you can find out the best way to configure relations.
In any case, draw them manually because you can make them automatic through programming and then disable the automatic features.

Now, how can we use automatic relations to display the fields of the Technician record in the Intervention record and display the list of interventions in the Technician record.

To do this, we're going to go back to an intervention and, at this level, we see that we only have the technician ID. We're going to add the description for the technician below.

• First we display the Explorer
• Go to the Forms
• Expand the Table Forms
• Then the Interventions forms
• and select its Input form

In this form, we're going to:

• duplicate this field
• indicate that the source of the field is the Technicians table
• and that it is the Last name of the technician

Then

• we re-duplicate it using the Ctrl+D shortcut to display the first name
• and Ctrl+D again to display the email.
• We save (Ctrl+S or File>Save)
• then we go back to an intervention
• and we automatically see the information about the technician appear.

When we change records, the update is automatic; we still have not programmed this automatic feature. Of course, if the relation was not automatic, we could have managed all this using programming.

Now let's see about the other thing we mentioned, which was displaying the list of interventions in the technician record.

So we display the technician record, then go back to the Explorer where we're going to drag the output form directly onto the technician's record.

Naturally, we don't actually need the technician's initials in this form but for the moment, the important thing is to understand how it works. So, we save this form as well.

Now we're going to check that in the structure this relation is an automatic One to Many relation; then during use, automatically, the list of interventions changes depending on the technician whose record we're looking at.

If we change the relation back to manual, when we go back to the technician's record, we see the same interventions displayed since the loading of the corresponding records (the join) was not performed automatically by 4D. In this case, it will be up to us to do it.

Later we'll see how to manage these functions using programming, more specifically because automatic relations are enabled when there is a screen display for a user, but not in every case when we use programming, which means that we can take control and manage relations however we want.