Limit record selection based on 2nd linked record

[Paul_Caswell]

I have an inventory base that I am creating with multiple tables for a hotel. Tables are as follows:

Inventory Table with linked fields for “Measurement” Table & “Department” Table.

We would like to limit record selection in the Measurement linked record field based on which Department has been selected in its linked record field.

For example: Maintenance department has the ability to select Feet, Inches, Pound, ect. as a selection of measurement when adding a new item to their inventory While Food & Beverage department would only see Gallons, ounces, pounds, ect.

Limiting record selection by a view solves the problem if I only had one department but in our case we have 9 departments to plan for.

I would greatly appreciate anyones suggestion on how to handle this situation.

[ScottWorld]

Unfortunately, one of the biggest missing features in Airtable is that it doesn’t support dynamic value lists like this. I would strongly recommend sending an email to support@airtable.com about adding this feature into the product, but they are notorious for ignoring customer requests.

There is a very elaborate and complex workaround to this problem that you can attempt to implement, but it comes with its own limitations as well:

Conditional Logic-ish Linked Records Show and tell

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. [Condition…

There are some other workarounds to this as well:

• You could probably script your own solution to this problem by using the scripting app, and then have your users use the scripting app to do their data entry.

• If you’re okay with your users doing their data entry using forms, the MiniExtensions.com form supports this feature, and so does On2Air: Forms (which requires JotForm).

[Jonathan_Lutz]

As @ScottWorld mentioned, there is currently no native Airtable functionality that would allow you to dynamically filter linked record fields based on another value in the record. This feature is often referred to as “dependent dropdowns”.

As previously noted, one way to accomplish what you’re after would be to utilize a front end that facilitates the user input. In its simplest form this can be a script. You could also utilize a tool like MiniExtensions. In order for this to work you may need to do a little more data modeling up front.

Specifically, you would add a linked field relationship between your Measurement & Department tables. Be sure to leave the default option selected that allows for many links on each side of the relationship. On the Measurement table, this new field would allow you to link each measurement with which Departments you want it to be selectable for. Conversely, on the Department table it would allow you to indicate which measurements would apply to each department.

You could then create a view in the Measurement table for each Department, & for each one, use the filter function to only show measurements that include that department in the new linked field created above. Obviously these views are statically created but since I don’t imagine departments changing often this is probably fine.

At this point, you could also create a lookup field on the Inventory table that shows the Measurement value of the Department field based on the mappings created above. Then, when you select a department, this lookup field will automatically show the measurements that are valid. This lookup field could be used as a source of valid selectable options for your front end.

Once you’ve done that, you can then configure your front end to utilize the views or lookup field value as described above in order to filter the selection of measurements based on department. In the case of a script, you could dynamically retrieve these values & present them as selectable button options using the input.buttonsAync method.

I know this wasn’t the answer you were hoping for, but hope it helps in some small way!

[Paul_Caswell]

Thank you Scott for the information and help.

[Paul_Caswell]

Thank you Jonathan, I was actually starting to take this same approach but figured I would ask the community since I didn’t find a reference to this problem in the forums and was thinking either my logic was wrong on how I was approaching the problem. Thank again for you reply.