Related Tables

You will have noticed that every time a dataset is imported or generated in your projects, an amigo_id column is generated, in this field a unique identifier of each record is stored. This code will be used as a foreign key in the records of its related peer.

To create a relationship, go to the Form Editor of any of the two datasets to match and add a related table field.

Adding a Related Table field between datasets using the Form Editor

Once the field is created, you will need to set other configurations: define which dataset will be paired and choose the direction of the relationship. You can choose from one of this 2 types:

• One-to-many: the amigo_id of the current dataset will be the primary key for the relationship. In other words, the dataset is set as the parent.
• Many-to-one:  the related table column that will contain the foreign key (the parent's primary key) will be created in the current dataset. In other words, we have select it as the child dataset.

Also, we can allow/forbid the creation of orphan records in the child dataset to guarantee that each child dataset record is related or not to a parent record.

Settings for Related Table fields

The related table column will be structurally in the child dataset and the stored data in these fields will be the amigo_id of the parent dataset record. Thus, even if we see other values ​​in the related table column (Check out about Display Field) the foreign key will always be the amigo_id of the parent record.

If we look at the forms for both datasets, this relational field will be visible by default showing access links between related records. This means that from the child form we will have access to the parent form record and from the parent form, we will have access to the children form records.

Let's look at this example of the Simpsons family members, with 3 datasets with the next structure:

• Elders (parent dataset for "Adults"): Name (text field), Age (number field). In the form configure elder_adult with "Add descendant" label
• Adults (parent dataset for "Children"): Name (text field), Age (number field), elder_adult (related table field).  In the form editor, configure elder_adult with "Main Parent" label and adult_children with "Child" label 
• Children: Name (text field), Age (number field), adult_children (related table field). In the form editor, configure adult_children with "Parent" Label

Orphan records are allowed in both relationships which means that we have disabled the option "All records in the child dataset must be related to a record in this dataset" 

Now we fill the "Elders" form to create our first record:

Adding Abraham record in Elders

From "Elder" we can access to create child form (from Adults dataset) with the ADD button:

Adding Homer record in Adults

From that “Adult” form record there is access to its parent record form as "Main Parent". If we select “VIEW”, we will return to the Elder related record.

If we want to add child records, we select “ADD” in the Homer form, then the create record form Children it will be displayed:

Adding child record in children

Now, Maggie record is ready after press "SAVE" button to return to Homer Adults form. We can continue adding two more records for Bartholomew and Lisa. Once all the Children records are ready, press "SAVE" to send Homer's record, and another time "SAVE" to send the grandpa Abraham's record.

Now we just need to complete the family with Homer's wife: Marge, by adding a new record in the Adults dataset.

If we view the table of attributes for Adults, Marge is an orphan record because it does not have any related parent record such as Homer's record.

Elders related field in Adults records

Database relationship operations

By understanding the premise of the location of the foreign key in the child dataset, we could perform operations in advanced queries to relate data under certain parameters after adding the related table column.

In the case of a dataset with geometries, it can perform geometric operations (intersections, containments, buffer, exclusions etc.) and add the value of the parent record (amigo_id) in the related table column as a foreign key in the child dataset.

You can also set qualitative rules to group records and add the foreign key to those child records that meet them.

Manual relationship selection

We can manually assign relationships one record at a time. This is possible by setting the access links as visible "" to modify the child records from the parent dataset form or to change attributes of the parent record from the child dataset form.

This are the steps required to access the form to make the manual selection:

Selection from the parent dataset: from the relationship field it will display options to ADD or EDIT related records.

• If we ADD a new relationship, it will send us to the form to create a new child record.
• If we EDIT a relationship, it will show us a table view of all its child records so we can select the record we want to edit by accessing the form of that child record. We can also remove the relationship from that child record* or delete that record from the dataset.

*: The relationship with the child record only can be deleted if it allowed the creation of orphan records from the properties of the Related Table field.

Selection from the child dataset: from the relationship field, it will display the options to VIEW, ADD, or SELECT

• If we choose VIEW, we will access the form of the current parent record, and also edit it.
• If we ADD a new relationship, it will display the form to create another parent record that will replace the previous one.
• If we SELECT a new relationship, it will show us a list of all existing parent records so we can choose one that will replace the previous relationship.