Matching Columns

When a relation is created, you can set additional restrictions (Match Conditions) for related records. These restrictions allow you to filter reference column values in a One-to-Many relation or filter detail records in a Many-to-Many relation.

If you have several match conditions, they are merged using the "and" logical operator.

If you need a more complex match condition or want to merge them using the "or" logical operator, consider using a reference column’s overridden default record picker filter condition or a custom formula for a summary column or recordset column filter condition. You can write a custom filter formula that references columns from the other side of the relation using the Related prefix. Refer to this blog article for usage examples.

Match Conditions are needed when a dependent dropdown is created. Let’s imagine that a database contains three tables: Contacts, States, and Cities.

The "States" table and the "Cities" table work like a catalog, keeping state and city names, respectively. These tables are related via a One-To-Many relation.

Each city is related to a certain state, while each state can be related to many cities. It means that there is a State reference column comprising state Id values in the "Cities" table.

Cities

The "Contacts" table is related to the "States" and "Cities" tables. It comprises the State reference column and the City reference column. When a contact form is edited, a user should first select a state name, and then a city related to this state should be selected.

Contacts

To make the City reference column dropdown dependent on the State value, you should add the Match Conditions to the relation between the "Contacts" and "Cities" table.

To add the match condition, follow these steps:

  1. Click the Setup link in the top right corner of the window.

  2. Select the table you need.

  3. From the setup menu, select Tables > Manage relations between tables.

  4. Select the appropriate relation and click the Edit button.
    Relation Match Conditions

  5. Click the New button in the Match Conditions section:
    Match Conditions New button

  6. On the displayed form, you should specify matching columns and conditions.
    Match Condition
    When columns are selected, their values will be used for matching. In our example, select the "State" column in the "City" table and the "State" column in the "Contact" table.

  7. In the Condition field, select a condition. You can choose one of the following conditions:

"=", "<>", "<", "<=", ">", ">=", "Contains"

The Contains option allows matching a multi-reference column and a single-reference column.
For instance, when picking the assignee for a task, you may want to display only those users for whom the task project was made accessible. Basically, all you need to do is add a match condition where you need to specify that the User Projects multi-reference column contains the Task Project single-reference column. You may find the example in this template.

Currently, the match condition Multi-reference Contains Single-reference can be used only in Many-to-One relation. It does NOT apply to Many-to-Many relations.

In the Dependent Dropdown example, specify the "=" value in the Condition field. As a result, when you edit a Contact record and select the State value, only Cities belonging to this State will be shown in the dropdown list.

Match Conditions result

You may review the adjustment of the matching columns in the Dependent Dropdown database example.