Matching Columns

When a relation is created, it is possible to set additional restrictions (Match Conditions) for related records. These restrictions allow to filter reference column values in the One-To-Many relation or to filter details in the Many-To-Many relation.

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 states and cities names respectively. These tables are related via 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 the State reference column comprising state Id values in the Cities table.

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

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, make the following steps:

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

Select a table you need.

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

Select an appropriate relation and click the Edit button.

Click on the New button in the Match Conditions section:

On the displayed form you should specify matching columns and condition.

When columns are selected, their values will be used for the matching. In our example select the State column in the City table and the State column in the Contact table.

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

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

Contains option allows to match a multi-reference column and a single-reference column. For instance, when picking up 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 to 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 for Many-to-Many relation.

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.

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