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.
Match fields must be one of the following field types:
 •
text
 •
number
 •
date
 •
time
 •
timestamp
 •
calculation (with a text, number, date, time, or timestamp result)
Notes
 •
Container fields, summary fields, and calculations returning a container field as a result cannot be used as match fields.
 •
The match fields used in a relationship can have different names.
 •
A match field can be a global field.
 •
Values are matched based on their indexing.
 •
To force match fields to consider non-alphanumeric characters, change the default language of the match fields to Unicode. See Defining field indexing options and Choosing a language for indexing or sorting.
 •
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.
 •
In the example above, 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.
 •
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:
red
green
blue
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.
 •
You can also see and work with data from external ODBC data sources. See Accessing external data sources for more information about working interactively with data in SQL tables.
Related topics 
About relationships
About planning a solution
About the types of relationships
Creating relationships
Was this topic helpful? Send feedback.