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?
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.
If you wanted to stick to using the Airtable interface itself, there is only one workaround that I am aware of that could accomplish this, which is described in the thread below. There is also a demo base in this thread which uses 3 tables like what you are describing:
There are a few limitations to this workaround. I think the key limitation for you is that ONLY ONE RECORD at a time will inherit this functionality. So 2 people can’t both be adding new records at the same exact time — only 1 new record at a time will receive this functionality.
If you need something more advanced than these options, you would need to move outside the Airtable platform by upgrading to a more advanced database tool — such as FileMaker Pro. (I am both an expert Airtable consultant & a certified FileMaker developer, so if you need to hire someone to help you with either platform, feel free to send me a private message.)
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.