Help

Re: Conditional Logic-ish Linked Records

3756 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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.
ConditionalLogic01
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.

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.)

ConditionalLogic02.png

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].

32 Replies 32
siliceous
6 - Interface Innovator
6 - Interface Innovator

Hi all,

I am just wondering is this still works. I have just tried implementing it and it isn’t working for me. When selecting the “Category”, all “Subcategories” display instead of the ones associated with that category.

I copied the base and set up all fields according to the base. The only thing that works is the Mismatch Alert when I select a subcategory that doesn’t belong to the category.

@Scott_White and @Tim_Deng - this is what the majority of us are really, really needing. I would guess that this one feature alone - Conditional Linked Records - prevents Airtable from being adopted by countless manufacturers to help manage and process products.

I’ve checked the base just now, and it still works - but the overhead needed to create/support this base is simply unspeakable - and the biggest problem is that it only works with one entry at a time. If you half complete one record, and then start another, all field selections fail.

Valentin_GUENIC
6 - Interface Innovator
6 - Interface Innovator

Is there a way to use this workaround not having dependant fields but based on the value of a common field?
I have 3 tables with a field in common (to manage compatibility). This field in common is a "linked to record" field.
I have another table (a master one) with the 3 previous tables as "linked to record" fields.
I want to be able to filter any of the 3 fields based on the common field (the field can exist in the master table too, that's not a problem).
It can't be done with views because I want it dynamic and it can't be interdependant because each of the 3 fields are optional.

I tried to use this workaround but it's a pain to use in my case and if a field remains empty, the whole thing is not working anymore.

It's a challenge, but I don't figure out how to get it.
Today I'm just considering not using Airtable while they don't give us the ability to filter "linked to record" (and not use views) or to update views through automations when a field has been filled.

This is excellent thank you.

giulia_mg
6 - Interface Innovator
6 - Interface Innovator

Greetings W_Vann_Hall!

I used your 3 step conditional logic demo (great work by the way) to fill out with the informations a need to be conditional at my company.
Now I am trying to make a form view out of this were the respondent can only choose a subcategory based on a category choosen. However this is not working at all, when I open the form, I have all options to be selected and not only the ones I inteend to see.

Do you have any idea/solution for this case?

 

Izaac_Post
6 - Interface Innovator
6 - Interface Innovator

You are a genius. This solves a problem I’ve been having with AirTable for years.

Izaac_Post
6 - Interface Innovator
6 - Interface Innovator

Now that AirTable has released the List view, I was hoping that they would build conditional logic into the linked-records field. One possibility for implementation is for it to be enabled by selecting "list" view from the "limit record selection to a view" menu. Once enabled it could use the levels of hierarchy setup in list view to present a "level 3 : level 2 : level 1" as the field’s value where level 3 filters the records for level 2 which filters the records for level 1.

Jessica_Pena
7 - App Architect
7 - App Architect

This is such a needed feature, I wish Airtable would bump this up in the queue ASAP 😩 

Dear W_Vann_Hall Many thanks again for demonstrating a great problem. I managed to simplify your database a bit without the "Control" table.
However, I encountered a problem with the "form" because when entering an empty form, filtering does not occur in the "Subcategories" table, and it also does not work in the form when I use it in the Interface.
Do you have any ideas?