About single-criteria relationships

In a single-criteria relationship, data in one field is matched to data in another field.

An illustration of a single-criteria relationship.

For example, a record in either table is related to any record in the other table when the values in TextFieldA and TextFieldB are the same.

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 contacts database has two tables:

•	Contacts, which stores names and addresses

•	Phone Numbers, which stores phone numbers and phone types, such as work, home, fax, and so on

The data is split between two tables because a single contact can have multiple phone numbers.

The tables have these fields:


Table	Field Name	Comment
Contacts	Name	Text field
	Address	Text field
	City	Text field
	State	Text field
	Postal Code	Text field
	Contact ID	Number field, auto-enter serial number; this will be the match field for the Contacts table
Phone Numbers	Phone Number	Text field
	Phone Type	Text field
	Contact ID	Number field; this will be the match field for the Phone Numbers table

The Contact ID field is the match field in the relationship between the two tables. In the Contacts table, the Contact ID field is set to auto-enter a serial number, giving each record in the Contacts table a unique ID number. In the Phone Numbers table, the Contact ID is left as a simple number field, and no auto-entry options are set.

Because the relationship is defined to allow the creation of related records in the Phone Numbers table, the Contact ID number from the Contacts record will be automatically inserted in the Phone Number Contact ID field in each new related record. This allows many records in the Phone Numbers table to be related to a single record in the Contacts table.

In the relationships graph, a relationship is created between the Contact ID field in each table.

The relationships graph, showing a single-criteria relationship between two match fields.

In the Edit Relationship dialog box, the Allow creation of records in this table via this relationship option is enabled. This means that the user 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. The Delete related records in this table when a record is deleted in the other table option is also enabled, because there is no reason to retain old phone numbers once the matching contact record has been deleted.

Finally, a portal to the Phone Numbers table is added to the Contacts layout.

Portal to the Phone Numbers table, showing a number of related records.