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.