To create related tables, you define a relationship between two tables. A relationship is established when the value in one field, called the match field (sometimes called a key field) on one side of the relationship compares successfully with a value in the match field on the other side of the relationship according to the criteria you specify in the relationship.
For example, you can create a relationship such that when the value in the Student ID field in a Students table equals the value in the Student ID field in a Classes table, the records in the two tables are related.
To create, change, and view the relationships in your database, see Working with the relationships graph.
A match field can be one field or a combination of fields that identifies a record in a table. For example, a match field can hold dates that, when compared with the criteria of the relationship, determine whether the relationship is successful.
A key is a type of match field. Keys usually hold values that are used as IDs (such as a product ID). There are two types of keys:
•primary key – a field that is in the same table as the record it identifies. A primary key value must be unique and not empty (non-null). There is only one primary key in a table, but the key can consist of more than one field. By default, when you create a file or create a table in the Manage Database dialog box, the new table contains a primary key field.
•foreign key – a field in one table that identifies a record in another table. Values in foreign keys don't have to be unique in the table, and they can be empty (null). There may be multiple foreign keys in a table.
In a Sales database, the Customers table and the Invoice table can each use the Customer ID field to uniquely identify each customer and purchase. The Customer ID field in the Customers table is a primary key field, because this field uniquely identifies each customer. The Customer ID field in the Invoice table is a foreign key field, because its values originate in another table; in this example, it's the Customers table. But the Customer ID field in both tables are match fields.
When the two tables are related using the Customer ID field, a record in the Customers table can display a portal showing each invoice with a matching Customer ID, and in the Invoices table each invoice with the same Customer ID can display consistent customer data.
FileMaker Pro Advanced searches the indexed values of match fields in a related table to determine which related records meet the relationship criteria for a record in the current table. Therefore, the match fields in the related table must be able to be indexed.
Match fields must be one of the following field types: text, number, date, time, timestamp, or calculation (with a text, number, date, time, or timestamp result).
Relationships promote the entry of consistent data and enforce rules in a database. FileMaker Pro Advanced supports the following types of relationships.
Relationship type | Description |
One-to-one | One record in a table is associated with one and only one record in another table. See One-to-one relationships. |
One-to-many or | One record in a table can be associated with one or more records in another table. Or one or more records in a table can be associated with one record in another table. See One-to-many relationships. |
Self-join | Both primary keys are defined in two occurrences of the same table. See Self-joining relationships. |
Another type of relationship is a many-to-many relationship. Relational database systems usually don't allow you to implement a direct many-to-many relationship between two tables. However, you can design a database that supports many-to-many relationships by using a third, intermediate, table. See Many-to-many relationships.
•A match field used for one relationship can be a lookup target field, as long as the lookup is based on a different relationship.
•Because global fields can't be indexed, you can't use them as match fields in a related table. However, you can use them as match fields in the current table.
•Relationships also make it possible to group your data to resolve complex searches. For example, relationships can determine current inventory levels, sales projections, and other tasks where it is necessary to find data across multiple tables.
•Use access privileges in the source table to limit or prevent access to related data. For example, users must have access privileges to view a related match field to see the related fields from that relationship.