Self-joining relationships

A self-joining relationship (or self-join) is a relationship in which both match fields are defined in the same table. FileMaker Pro generates a second occurrence of the table upon which you are basing the relationship in the relationships graph. This prevents the relationship from forming a cycle, which would be impossible to evaluate.

For example, use a self-join in a portal on a layout of the current table to display a subset of data that's in the current table, such as all the employees who report to each manager. In this example, you would use a table named Employees.

To define a self-joining relationship:

  1. In the relationships graph, select the table to use for the self-joining relationship. Then click Add relationship button.

    See Working with the relationships graph and Creating and changing relationships.

  2. In the Edit Relationship dialog box, for Table, choose the same table for each side of the relationship.

    In the example above, choose Employees on both sides of the relationship.

  3. Select the match field for each table occurrence, define the relationship, click Add, then click OK.
  4. Type a name for the second table occurrence, then click OK.

    For example, rename Employees 2 to Managers.

In the example above, the match fields are Employees:Manager ID and Managers::Employee ID. This relationship returns related records in Employees when the value in Employees::Manager ID is the same as the value in Managers::Employee ID.

This example looks like this in the relationships graph:

Two occurrences of the same table with a line between fields showing a self-join

For more examples of using a self-join, search the Knowledge Base.

Notes 

  • You can also select the table in the relationships graph and click Duplicate table button to create a self-join.

Related topics