Conditional Logic-ish Linked Records

[W_Vann_Hall]

TL;DR version: Airtable’s recently added capability to limit linked-record selection to a specific view allows users to incorporate, to a degree, conditional logic.

The specifics: ‘Conditional logic’ can mean a lot of things: Any use of an IF() or SWITCH() statement is a form of conditional logic. Here, I’m using it in what’s become an increasingly common manner: to mean the limiting of available options based on an earlier choice.

For example, take a look at the following diagram.

The user selects one of five categories. Based on that selection, he or she is allowed to choose from a subset of five subcategories, taken from an overall population of 25 subcategories. This conditional restriction of choices not only helps reduce a potentially unwieldy array of options to a smaller, more manageable group, it also helps ensure the subcategory chosen is appropriate given the category already selected.

Admittedly, a 25-element list may not present an insurmountable challenge to the user — even though in the example illustrated, a full 80% of subcategories would be considered invalid for any of the potential categories. Imagine, though, a situation where each category has 25 possible subcategories, or where categories and subcategories consist of model and part numbers: The likelihood for error increases greatly.

Such is the situation this approach is meant to address. While it is far from a universal solution — as the following list of caveats makes clear — it should prove useful in a number of circumstances and use cases.

First, the caveats:

1. This version of the routines requires all selections be made in the form of linked records. While variations are possible where only the final, limited selection be presented as a linked record, they impose other limitations: namely, that each record of the main table be linked to a single record in the [Control] table for conditional logic to work. The version discussed here and in the referenced base requires only category and subcategory tables to be linked to [Control]; records may be added to the main table without any such preliminary housekeeping.
2. Currently, there is no way to limit user choice to already-existing linked records; a user may create a new linked record at any selection level. While there are ways to generate an alert should such a thing happen, there is, to my knowledge, no way to prevent it. (Yes, a user entering data from an Airtable form cannot create a new linked record — but see the next entry.)
3. Perhaps most disappointingly, because of the way Airtable creates records entered from a form view, these routines do not work with forms.

A demonstration base can be found here. To examine the base, open it and duplicate it into your own workspace.

Airtable

Conditional Logic demo - Airtable

Explore the "Conditional Logic demo" base on Airtable.

As you can see from the following screenshot, the base contains four tables: [Services] is the main table; [Control] is a one-record table used to manage display of the appropriate subset of subcategories; and [Categories] and [Subcategories] should be self-evident. (For the purposes of this illustration, I’ve borrowed categories and subcategories from MindBodyOnline, a SaaS business-management app for the wellness industry.)

To see conditional limiting in action, create a new record and select a {Category}; you’ll notice your selection mirrored in {ViewControl}. (In normal operations, both {ViewControl} and {Alert:Mismatch} would be hidden fields; I’ve left them visible to illustrate the underlying mechanism.) Next, select the ‘+’ in {Subcategory} and review the possible choices — but close the linked-record popup without selecting a subcategory. Delete your {Category} choice and select a different category. Now, when you go to add a {Subcategory}, your options will have changed.

If you select a {Subcategory} and then change {Category}, in most cases {Alert:Mismatch} will be set to '1' and (for Pro accounts) conditional coloring will flag the record as erroneous. (For all accounts, the {!} field — not shown on the screenshot — will be set to '🔥' to indicate an error.) (I say ‘in most cases’ because some {Subcategory}s are valid for more than one {Category}; 'Botox', for instance, is a valid subcategory of both 'Face treatments' and 'Med spa'.)

Details: The routines are largely self-explanatory. The only bit of trickery involved is using a single {Control} record to track the current record’s {Category} setting. The routine assumes only the record presently being edited will have a linked {Category} with no linked {Subcategory}, and sets the value of {ViewControl} accordingly. From there, a chain of rollup fields is used to pass along the current {Category}, eventually resulting in {Subcategories::DynamicFilter} being set to '1'. The value of {DynamicFilter} is used to determine which [Subcategories] records are visible in the <Subcategories::FilteredSelect> view — which, in turn, determines which {Subcategories} may be selected from [Services].

[Gespion]

Hello guys, I’ve also been looking for a way to do this conditional selection with categories and sub-categories.

Here’s my take on it which is working but might be hard to main if you have a lot of categories and sub-categories: Dynamic Drop down dependent on another dropdown

Let me know if it helps or how we can improve this.

[Diego_Angel_Hak]

Hi everyone!

I am tackling this same issue, but on steroids. My problem is that categories in my case have 4 steps or sometimes 5 steps (category - subcategory - subsubcategory - sub^3 category - and sometimes sub^4 category) so @W_Vann_Hall 's solution is hard to use. Also, I need a record to be linked to more than one category, which current examples don’t allow. One way could be to use conditional form fields, but the total number of valid combinations throughout all my categories is 1263, so it would take forever to set up. Is there a way to upload a spreadsheet or file so that the conditional form fields are created automatically? Making that many filtered views/new sheets is also impractical. Is there a way to solve my problem?

I have all the 1263 possible combinations arranged in a table with fields headings {category}, {subcategory}, {sub subcategory}, etc. So maybe we could start from there.

Thanks!

[Justin_Barrett]

Welcome to the community, @Diego_Angel_Hakim! :grinning_face_with_big_eyes:

Diego_Angel_Hakim:

Is there a way to upload a spreadsheet or file so that the conditional form fields are created automatically?

I’m afraid not. Airtable doesn’t yet provide any back-door tools for setting up conditional form fields. The other possible hiccup there is that conditional form fields only control which fields are visible in the form. They don’t control any settings within those fields (like choosing a specific view in a linked table, or changing single-select options).

[HQ_Team]

Hey! This is a great solution, exactly what we wanted to make data entry easier for our team. I tried using this conditional logic-ish linked records exactly how it’s done in the base you shared. But when I try to enter something in the Subcategory field in my table, it says “No matching records from FilteredSelect”

I’m not sure what I’m doing wrong. Can you help?!

[Garrett_Biddle]

@W_Vann_Hall Is it possible to allow multiple selection on the last step the subcategory?

I altered that field to allow linking to multiple records but as soon as I select the 1st category the rest are filtered out.

Any ideas?

Thank you!

[Axel_JOURDA]

Thank you very much for this explanation. I used your demo 2 days ago and it worked well (after copying it in my base). But, today, the filter for the “category” and “subcategory” remains empty after selecting the “type” level on the “services” tab. I don’t understand why…
Do you have any idea ?
Did you eventually change any function or settings meanwhile ?

[Dean_Arnold]

Thanks @W_Vann_Hall ! I re-created the fields within an existing base and it works great! Admittedly, I don’t fully understand the logic. Will this solution get screwy if multiple users start adding records at the same time?

[Moe]

If anyone is interested in a less technical solution, we’ve added this feature to our form recently. You can now dynamically filter linked fields.

miniExtensions

Conditional Filter for Linked Records in miniExtensions Form

Filter through linked records based on certain fields. e.g. instead of showing a list of all cities, select a county first to narrow down the options.

[Fritsen]

Is it in some way possible to restructure this so it’s possible to choose multiple linked records after its filtered based on the first linked record selection?

I’m working on creating this with Year as the first Linked Record and I want to filter it so I can choose Weeks “attached” to Year 2021, but I want to be able to select multiple weeks. The reason for this is that sometimes the record will be available for multiple weeks, and the base should run for multiple years.

[Brian_Davis]

@Jordan_Scott1 We desperately need this feature as the current workaround is too hard to maintain. Any updates on this?

It seems like this is the most important feature for both new and seasoned users since almost every contact or credit card authorization requires this sort of input scoping. I would love it if I could filter both directions. Imagine if filling in State, City or Zipcode narrowed any of the three related fields by partial or full entries. We have projects where we would love to set this up on hundreds of related fields. Isn’t this the real answer to boosting user adoption? Consultants can wow clients and the database could error check itself across multiple factors of data validation?

Imagine if typing the City narrowed the states and zipcodes or typing partial zipcodes narrowed the cities and states or typing states narrowed the cities and their codes. I think this feature alone would make Airtable a more central app over others that will never think to do this. There would be so many uses for this kind of inout control.