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 an AND comparison; to match successfully, every match field must find a corresponding value in the other table.

For example:

An illustration of 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 video rental database has three tables:

•	Videos, a static database that stores video rental inventory

•	Customers, which stores customer names and addresses

•	RentalLineItems, which stores a record of each video rental, including the Video ID, the Customer ID, and the date rented

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

Although the solution uses three tables, the multi-criteria relationship is between the Customers and RentalLineItems 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 RentalLineItems table
	Date of Rental	Date field; the other match field to the RentalLineItems table
RentalLineItems	Customer ID	Number field; one of the match fields to the Customers table
	Date of Rental	Date field; the other match field to the Customers table
	Video ID	Number field

The tables are related as follows:

A multi-criteria relationship between the Customers table and the RentalLineItems table.

Customer ID and Date of Rental are the match fields in the relationship between the Customers and RentalLineItems 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 Date of Rental field is a date field with no additional options. The user enters the current date when videos are rented, or enters any previous date to display videos the customer rented on that date.

In the RentalLineItems table, Customer ID and Date of Rental are number and date fields, respectively, and are match fields in the relationship to the Customers table. Video ID is a number field, and stores the ID of the video being rented. Records from this table are displayed in a portal in the Customers table.

The Edit Relationship dialog box, showing the properties of the relationship between the Customers table and the RentalLineitems table.

In the Edit Relationship dialog box, the Allow creation of records in this table via this relationship option is enabled for the RentalLineItems table. This means that users can enter rental information in an empty portal row, and FileMaker Pro will automatically create a related record for that rental.

Finally, a portal to the RentalLineItems table is added to the Customers layout.

A portal showing the related records from the RentalLineItems table on the Customers layout.

To create a new related record, the current date is entered in the Customers::Date of Rental field and a video ID number is entered in the portal. Because Date of Rental is a match field and the relationship allows the creation of related records in the RentalLineItems table, the value in Customers::Date of Rental is automatically entered in the RentalLineItems::Date of Rental field.

To view rentals from a previous date, the user changes the value in Customers::Date of Rental. FileMaker Pro displays all related records with the same customer ID and rental date.