Working with related tables and files > About the types of relationships > About single-criteria relationships
 
About single-criteria relationships
In a single-criteria relationship, data in one field is matched to data in another field.
For example, a record in either table is related to any record in the other table when the values TextFieldA and TextFieldB are the same.
Two tables with lines between two fields showing a single-criteria relationship
Using a single-criteria relationship
Use a single-criteria relationship when you want to relate two tables based on a single common value, such as a serial number or an ID number. For example, a database has two tables:
Customers, which stores names and addresses
Contacts, which stores phone numbers, types of phones numbers (such as work, home, fax, and so on), and email addresses
The data is split between two tables because a single customer can have multiple phone numbers and email addresses.
The tables have these fields:
 
Table
Field name
Comment
Customers
Customer ID
Number field, auto-enter serial number; this will be the match field in the Customers table
 
First Name
Text field
 
Last Name
Text field
 
Address1
Text field
 
City
Text field
 
State
Text field
 
Country
Text field
Contacts
Phone Number
Text field
 
Phone Type
Text field
 
Email Address
Text field
 
Email Type
Text field
 
Customer ID
Number field; this will be the match field in the Contacts table
The Customer ID field is the match field in the relationship between the two tables. In the Customers table, the Customer ID field is set to auto-enter a serial number, giving each record in the Customers table a unique ID number. In the Contacts table, the Customer ID is a simple number field, and no auto-entry options are set.
In the relationships graph, a relationship is between the Customer ID fields in each table.
A single-criteria relationship between a Customers table and a Contacts table
In the Edit Relationship dialog box, the Allow creation of records in this table via this relationship option is enabled. This means that you will be able to enter a phone number in an empty portal row, and FileMaker Pro will automatically create a related record for that phone number. Also, the Delete related records in this table when a record is deleted in the other table option is enabled, because there is no reason to retain old phone numbers if the matching contact record has been deleted.
Because the relationship is defined to allow the creation of related records in the Contacts table, the Customer ID number from a Customers record will be automatically inserted in the Customer ID field in the Contacts table in each new related record. This allows many records in the Contacts table to be related to a single record in the Customers table.
Finally, a portal is added to the Contacts table on the Customer layout. When you enter a phone number or an email address in an empty portal row in the Customers table, FileMaker Pro creates a related record for that phone number or email address in the Contacts table.
Related topics 
About multi-criteria relationships
About relationships using comparative operators
About relationships that return a range of records
About self-joining relationships
Creating portals to display related records