Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Conditional Logic-ish Linked Records

17901 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

I’ve published a base that demonstrates 3-step conditional logic here.

Selecting {Type} limits {Categories}; selecting {Categories} limits {Subcategories}


  1. Thanx and a tip of the hat to MindBodyOnline, a SaaS business-management app for the wellness industry, for their collection of categories and subcategories pertaining to beauty-, fitness-, and wellness-related services.

That’s some wonderful tablecrafting. Thanks so much for sharing! :thumbs_up:

haim_co
4 - Data Explorer
4 - Data Explorer

good job,
wish there was built-in sub category feature…

agg_kafer
4 - Data Explorer
4 - Data Explorer

That is just great. Thanks @W_Vann_Hall

What do you guys of the Airtable-Support think: Will this be displayable and thus usable in Form-View any time soon?

Eagerly awaiting word.

I doubt you’ll see it available through this method in forms any time soon, as that would require a bottom-up re-engineering of how forms operate. For more info, see this earlier comment.

Josh_Cooper
6 - Interface Innovator
6 - Interface Innovator

This is a pretty good work around and thanks so much for sharing. I attempted to use this and I found another caveat you may want to mention. The subcategory will become blank after you select the “+” (with no choices available to select) if more than one category has been selected before the subcategory is chosen. This is because the filtered view of the Subcategories table is looking to the Control table rollup that is ultimately from the Main table, ViewControl field. If more than one category is selected, there will be multiple categories in the Control table rollup, leading to no match on the Subcategories table, therefore every record is filtered out. Wow, that was a twisted explanation, but I hope you can follow. I only found this out because our workflow has one person put in all the categories before another person puts in the subcategory information.

Boy, I hope Airtable is working on this. It is really making my payment harder and harder to make.

Good catch – thanks!

Yeah, there are ways to set up per-record conditional choices, but they all require each new record in the main table — [Services] in the demo — to be linked to the single record in [Control]. (Assuming that would even work; I’d have to think a bit more about it.) I built it this way so that the link-every-record-in-a-table-to-one-record-in-another-table overhead takes place only during category/subcategory definition, rather than during data entry. I have to admit, your process never even occurred to me; I’ll update the documentation with the needed caveat.

Good afternoon,

Thank you very much for the base you created. However, I am having the same problem as Josh. Has anybody found a solution?

Hi Victor,

Unfortunately, I have not. We came up with a workaround. Basically we have several single selects with almost the same name and different views created for different circumstances, essentially filtering the dropdown based on view choice instead of the choice made in a single select. This was the best we could do and maintain data integrity.

I really hope this is in the long term plan for Airtable!!