Introducing the “Related” column prefix

As you know, currently the only way to match columns from related tables is to use relation match conditions. While this functionality will work in many cases, it has some limitations:

• Limited number of comparation options to match one column to another.
• All matching conditions are merged using the “and” logical operator.

To overcome these limits, we’re introducing a new “Related” column prefix, which allows you to refer to the columns on the other side of the relationship from summary columns’ and relation-specific record pickers’ filter conditions.

A Practical Example: Finding Duplicates

Let’s see how it works using a classic find duplicates example. For instance, if you have a “Contact” table with first and last name columns and you wish to find duplicate records using them, you’ll need to create a many-to-many relation to itself with two match conditions for first and last name. After that, you’ll be able to create a summary column to calculate the number of records and use it to detect duplicate records.

With the new “Related” prefix, you’ll only need to create a many-to-many relationship to the same table with no match conditions at all and a summary column to calculate the number of records using the [First Name]=Related[First Name] and [Last Name]=Related[Last Name] formula as the filter condition.

The advantage of this approach is that you can use a single relation and create many summary columns with different filter conditions on related records. Also, you can use any functions, logical, and comparison operators to perform record matches.

Advanced Record Picker Filtering

But we don’t stop here. We’re expanding the usage of the “Related” prefix for overridden record picker filter conditions, which allows us to implement advanced record picker filtering functionality that was not possible before.

For example, if you have a car reservation system with “From” and “To” dates columns and a many-to-one reference to the “Car” table. When creating a new reservation by entering “From” and “To” dates, you expect to see only available cars on those dates in the “Car” reference column dropdown. This was not possible before, but now with the newly introduced “Related” prefix, we can do the job.

First of all, you’ll need to create a many-to-many relationship to self in the reservation table. Calculate the list of already reserved cars within the current timeframe using a concatenate summary column on car reference column with a comma separator, using the [Id]<>Nz(Related[Id]) and [From]<=Related[To] and Related[From]<=[To] formula as the filter condition.

After that, you’ll need to override the record picker for the “Car” reference column. Use the not Any([Id], Related[Reserved Cars in Current Time Frame]) formula as the filter condition to exclude already reserved cars from the record picker dropdown.

A similar approach can be used in many other scenarios. For example, for event planning to exclude busy meeting rooms or for a job record to exclude already booked staff from the assigned dropdown.

Hope you’ll enjoy this new functionality!