When designing tables in Ability, you will often find that you are about to create a field for information that is already held in another table. For example, you may have a table that contains customer account information and another that contains invoice details. It would be inefficient and unnecessary to have customer names and addresses in both tables. In this case you would create a field, say for a unique customer account number, that both tables could have in common, and put the names and addresses of the customers in the customer accounts table alone.
In order to relate such tables, Ability allows you to use the device of Relations to join tables. Joined tables must have at least one field in common, called the join field, by which they are related, thereby making it possible to view the contents of two tables together. The resulting table is called a Relation and you are allowed to apply Queries, Column Views, Sort Orders and Filters to it, just as you can with normal tables.
You can join more tables to the tables you have already joined, with the proviso that each join is between common fields for adjacent tables. For example, a name field in one table may be joined to a name field in a second table, and an age field in the second table may be joined to an age field in a third table, even though the first and third tables do not share a common field.
Note: The term common field requires that the joined fields have at least the same data type. For a join to make sense the fields should also contain similar data, that is to say, there should be the possibility that some of the records in the joined tables contain exactly the same data in the joined fields, otherwise there can be no match.
The data contained in a relation is a live reflection of the data contained in the tables that are joined in the relation, that is to say, any change of data in the original tables is reflected in the related table. Likewise, any changes you make in the related table cause similar changes in the original tables. You can therefore update several tables while working only in the one related table.
Whenever you join tables, all the fields from the original tables are shown in the relation. However, what records are shown from each of the original tables depends on the type of join you use. There are three options:
Inner - only the records that have matching values in their join fields are shown
Left outer - all the records in the left hand table are shown but only the matching ones from the right hand table
Right outer - all the records in the right hand table are shown but only the matching ones from the left hand table
See: