About multi-criteria relationships

In a multi-criteria relationship, you increase the number of match fields, which increases the criteria that FileMaker Pro evaluates before successfully joining related records. In a multi-criteria relationship, FileMaker Pro compares the values from each match field on each side of the relationship in the order in which the fields appear. This is called an AND comparison; to match successfully, every match field must find a corresponding value in the other table.

Two tables with lines between four fields showing a multi- criteria relationship

In this relationship, both TextFieldA and NumberFieldA are match fields to TableB. A record in TableA with a TextFieldA value of Blue and a NumberFieldA value of 123 will only match those records in TableB where both TextFieldB and NumberFieldB have values of Blue and 123, respectively. If TextFieldA and TextFieldB have matching values, but NumberFieldA and NumberFieldB do not, no related records are returned by the relationship.

Using a multi-criteria relationship

Use a multi-criteria relationship when you want to relate two tables based on more than one common value, such as a customer ID and a date. For example, a database has three tables:

•	Equipment, a static database that stores equipment rental inventory

•	Customers, which stores customer names and addresses

•	Catering Line Items, which stores a record of each equipment rental, including the equipment ID, the customer ID, and the event date

The purpose of this database is to track equipment rentals and display all rentals by a selected customer on a selected date.

Although the database uses three tables, the multi-criteria relationship is between the Customers and Catering Line Items tables. These two tables have these fields:


Table	Field name	Comment
Customers	Customer ID	Number field, auto-enter serial number; one of the match fields to the Catering Line Items table
	Event Date	Date field; the other match field to the Catering Line Items table
Catering Line Items	Customer ID	Number field; one of the match fields to the Customers table
	Event Date	Date field; the other match field to the Customers table
	Equipment ID	Number field

The tables are related as follows:

A multi-criteria relationship between a Customers table and a Catering Line Items table

Customer ID and Event Date are the match fields in the relationship between the Customers and Catering Line Items 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. The Event Date field is a date field with no additional options. You enter the current date when equipment is rented, or enter any previous date to display equipment the customer rented on that date.

In the Catering Line Items table, Customer ID and Event Date are number and date fields, respectively, and are match fields in the relationship to the Customers table. Equipment ID is a number field, and stores the ID of the equipment being rented. Records from this table are displayed in a portal in the Customers table.

In the Edit Relationship dialog box, the Allow creation of records in this table via this relationship option is enabled for the Catering Line Items table. This means that you can enter rental information in an empty portal row, and FileMaker Pro creates a related record for that rental.

Section of the Edit Relationship dialog box showing the Allow creation of records in this table via this relationship option selected

Finally, a portal is added to the Catering Line Items table on the Customers layout.

To create a new related record, enter the current date in the Customers::Event Date field and an equipment ID number in the portal on the Customers layout. Because Event Date is a match field and the relationship allows the creation of related records in the Catering Line Items table, the value in Customers::Event Date is automatically entered in the Catering Line Items::Event Date field.

To view rentals from a previous date, change the value in the Customers::Event Date field. FileMaker Pro displays all related records with the same customer ID and event date.