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 to relate two tables based on a single common value, such as a serial number or an ID number. For example:

  1. Create a database with these two tables:
    • Customers, which stores names and addresses
    • Contacts, which stores phone numbers, types of phones numbers (such as work, home, mobile, 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.

  2. Create the following fields:
  3. Table

    Field name

    Comment

    Customers

    Customer ID

    Number field

     

    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

  4. Use the Options for Field dialog box to set the Customers::Customer ID field to auto-enter a serial number and to validate that the field holds unique values. See Setting options for fields.

    These settings ensure that each record in the Customers table has a unique ID number.

    In the Contacts table, the Customer ID is a simple number field, so do not set any auto-entry options, but set the field to validate as not empty.

  5. In the relationships graph, create a relationship between the Customer ID fields in each table. (Customer ID is the match field in each table.)
  6.  

    A single-criteria relationship between a customers table and a contacts table

  7. In the Edit Relationship dialog box:
    • Select Allow creation of records in this table via this relationship for the Contacts table. When you enter a phone number in an empty portal row, FileMaker Pro creates a related record for that phone number.

      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 inserted automatically 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.

    • Select Delete related records in this table when a record is deleted in the other table for the Contacts table. There is no reason to retain old phone numbers if the matching contact record has been deleted.
  8. Add a portal to the Contacts table on the Customer layout, and add these related fields to the portal: Phone Number, Email Address.

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.