About match fields for relationships

When you create a relationship between tables, you choose one or more fields in each table as match fields. Match fields usually have common values. In a typical relationship, a record in one table will be related to records in another table that share a common match field value.

For example, a Customers table and an Invoice table can each use the field Customer ID to uniquely identify each customer and purchase. If the two tables are related using Customer ID as the match 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.

In this example, the Customer ID field in the Customers table is sometimes called a primary key field, because this field uniquely identifies each customer. The Customer ID field in the Invoice table is sometimes called a foreign key field, because its values originate in another table, namely the Customers table.

Match fields must be one of the following field types, and they must be able to be indexed:

•A match field used for a relational database can be a lookup target field, as long as the lookup isn't based on a relationship that involves the match field.

•You can increase the number of possible matching values by entering multiple values in the match field, separated by carriage returns. You can access related data by matching any single line of your match field, according to your relationship criteria. This is sometimes called a multi-key field or complex key field.

For example, you have a simple relationship joining records in TableA to TableB based on the contents of a single field in each table, and the match field in TableA contains the following values, separated by carriage returns:

FileMaker Pro will match any record in TableB where the corresponding match field contains the single value red, green, or blue. However, FileMaker Pro will not return records where the match field contains the value red green blue. The carriage returns tell FileMaker Pro to treat each line as a separate value.