Dynamically filter linked record field

[Alexandra_Scott]

I’ve read several posts asking for solutions to something similar to my requirement (below), but haven’t seemed to hit on the solution. I’m desperately hoping someone can either help me find it, come up with a great hack, or just confirm that there is currently no solution or workaround:

PROBLEM: I would like to be able to dynamically filter the options shown in one linked record field based on the value in another linked record field in an individual entry.

BASE STRUCTURE overview:
Activities Table: records are entered for individual team ACTIVITIES (e.g. “write weekly email”)
Products Table: Activities can be associated via linked record field with a number of different PRODUCTS
Departments Table: Products AND Activities are both associated via linked record field with a set number of DEPARTMENTS.

QUESTION: when I use the linked record field to identify which DEPARTMENT an activity belongs to, is there any way to DYNAMICALLY filter the PRODUCTS fields to show only those items that have also been linked to that DEPARTMENT?

EXAMPLE:
ACTIVITY : I enter “write weekly email” in the primary field in the Activities table
DEPARTMENT : I select from the linked record field “department A”
RELATED PRODUCT : When I choose a related Product, I want to limit the list of Products (in the linked record field) to only those that have been associated with the same Department in the Products table.

NOTES : This is a very large base, and records are added and removed on a regular basis, making some manual workarounds prohibitive; and there are many other tables that use these linked fields (Department, Product) as well, so I believe that they need to remain as separate tables (can’t be set up as a single or multi-select field).

I’d be grateful for some definitive insight into whether I’m up against an immovable object, or I should keep trying to get this to work somehow.

[ScottWorld]

This is not yet available in Airtable, but dynamically linked records are a native built-in feature of:

- Fillout’s advanced forms for Airtable

and

- Noloco’s advanced interfaces for Airtable. 

[kuovonne]

Welcome to the Airtable community!

I agree with @ScottWorld. I think your best bets for solutions are

• leaving the base as is, and depending on user training and formula field checks
• using a custom script or a custom block for creating the new record

While W_Vann_Hall’s solution in the Show and tell is an amazing bit of engineering, I think that Scripting Block and Custom Blocks is moving Airtable into a new era where such complex base designs will no longer be necessary. Instead, of having a separate control table with a single control record and having to remember to link new records to it, all of that business logic (and other business logic) can be moved to the scripting block or custom block.

Instead of creating your new record in the out-of-the-box Airtable interface, you would create your record by running the scripting block or custom block.

By the way, even if you could turn products or departments into single/multi-select fields, that would not solve your problem.