Compatibility: Version 6.7.x and 6.8.x

This tech tip provides the information needed to understand and debug a problem that is sometimes encountered by beginning 4D users.

Recently a new 4D user described the following situation: "When entering data into a field in a record which has a many-to-one relation, the wildcard choice list shows up per usual. When I choose from the list, the correct information is displayed in the fields from the One record on the form. But if I accept that record and then go back to it, what is displayed in those fields is data from the FIRST record in the One table that matched my entry."

Example: enter last name=Brown.
The wildcard choice list displays a list of all 10 Browns, along with the additional field value of first name. I select "Brown,Kevin", and Kevin's information is displayed in the fields from the One record on the form. When I accept this (Many) record and come back, Adam Brown's information displays in the fields from the One record.

What is going on here?

The solution to this problem lies in investigating the field that you are using as the primary key in the One table, and in understanding that the primary key field must ALWAYS be unique.

Here is what is going on:

Suppose you are using Last Name as the Primary key in the One table, and you have designated First Name as the second field to appear in the wildcard choice list. When you select "Brown,Kevin" from the list, the value Brown is entered into the Foreign key field (in the Many table) and the correct data displays in the fields from the One table on the Many table form.

Now, once the value Brown is entered into the Foreign Key field in the Many table, 4D will use that value every time it attempts to establish a link to a record in the One table, by doing a lookup in the One table
for records that have a matching value in the Primary Key field. If your Primary Key in the One table is Last Name, the values in that field are not likely to be unique; you will have multiple records with the value of "Brown" in the Last Name field. When 4D does a lookup in the One table on Last Name=Brown, it will link to the first record that it encounters with that value, and load that record making data from that record visible in the fields from the One table on the Many table form. Additionally, this linking process is dynamic. Therefore every time you link back to the One table, the link is created by doing a lookup in the One table. So if you delete the original record that you intended to link back to, 4D will find the next record where Last Name = Brown. If you sort the table, causing the position of the records to change, 4D will find a different record where Last Name = Brown. Change the current selection in your One table and go to the Many table and open the record activating the automatic relation (lookup) between the tables, and 4D will find a different record where Last Name = Brown, or no record if there isn't one in the current selection of the One table. (The fields from the One table will be appear blank on the form from the Many table).

Is this a flaw in 4D? Absolutely not. Why? Because relational databases are deliberately designed to link records based on the values in fields, not on some behind-the-scenes pointer to a specific record.

Does this mean that you should avoid using automatic Many to One relations? No, the problem is not with 4D or automatic relations.

Then what is the source of this problem?

The source of this problem as well as the solution lie in adhering to the rule that the primary key in the One table must ALWAYS be unique. Therefore you must create a field that holds unique values in the One table, and use that as the Primary Key field upon which you link the two tables.

Remember that 4D's built-in behaviors are designed to work based on proper database design. Of course, you can use any design that you like, and you can create your own alternatives to 4D's built-in behaviors.
If you would like to learn more about database design theory, you might want to download the free book on Relational Database Theory available on the following page of the 4D site: http://www.4D.com/academic/lessonplans.html