Creating and changing relationships

  1. Choose File menu > Manage > Database, then click the Relationships tab.

  2. In the relationships graph, locate the two tables you will be using for this relationship.

    They can appear anywhere on the graph, but they must be present on the graph in order to create the relationship. For self-joining relationships, locate the single table; FileMaker Pro will create the second table occurrence for you.

    To add a table to the relationships graph, click Add table button. See Adding and selecting table occurrences.

    To duplicate a table in the relationships graph, select the table and click Duplicate table button. To rename a duplicated table, click Edit button and type the new name.

    Note  You can't change tables within a relationship; you must create a new relationship.

  3. Drag from a match field in one table to the corresponding match field in the other table.

    You can also click Add relationship button and specify the relationship.

    Note  For self-joining relationships, drag a match field in the current table outside the table and then back onto the second match field within the current table. Rename the new table occurrence, or click OK to accept the default name.

  4. Repeat step 3 for each match field in this relationship.

  5. To change settings for a relationship, double-click the indicator line between the related tables. Then:

    6. To

    Do this

    Change or add a match field

    Select a new match field and click Change or Add.

    Change the relational operator

    Select a new relational operator from the list and click Change.

    Duplicate or delete a pair of join criteria

    Select the paired criteria from the list in the middle part of the dialog box and click Duplicate or Delete.

    Add a related record (to the related table) whenever you enter data into a related field in a record of the current table

    Select Allow creation of records in this table via this relationship. When this option is selected, entering data in a related field that has no corresponding related record creates a related record based on the match field in the current table.

    Delete related records (in the related table) whenever you delete a matching record in the current table

    Select Delete related records in this table when a record is deleted in the other table. This option deletes related records even when you're browsing a layout that doesn't display the related records.

    Note  When this option is selected in multiple relationships in the same database, deleting a record from one table can also delete related records in other related tables for which the option has been enabled. This is called a cascading delete. Consider this option carefully before enabling it.

    Sort related records

    Select Sort records. Then, in the left list in the Sort Records dialog box, double-click the fields to sort. To change the order in which fields are sorted, drag fields in the right list into the order you want. Select other options, then click OK.

    To change the sort order, click Specify in the Edit Relationship dialog box.

    Selecting this option does not affect the sort order in the related table.

Notes 

  • Select Sort records to display the first found record in a particular sort order (such as the lowest or highest value) when you have one related field directly on a layout of the current table. (For example, display the most recent checkout date for a piece of equipment.) Also sort related records to access multiple records that are in a particular sort order and display them in a portal. See Working with related data in portals.

  • You can increase the number of possible matching values by entering multiple values in the match field, separated by carriage returns. You can access related data by matching any single line of your match field, according to your relationship criteria. This is sometimes called a multikey field or complex key field.

    For example, you have a simple relationship joining records in TableA to TableB based on the contents of a single field in each table, and the match field in TableA contains the following values, separated by carriage returns:

    Copy 
    red
    green
    blue

    FileMaker Pro will match any record in TableB where the corresponding match field contains the single value red, green, or blue. However, FileMaker Pro will not return records where the match field contains the value red green blue. The carriage returns tell FileMaker Pro to treat each line as a separate value.

Related topics