Dynamic filter for Parent/Child relationships

[Ash_Nazarizadeh]

As far as I know, currently it’s not possible to create a parent/child style application in AirTable. A dynamic filter that could filter records in one table based on the current record in another related table would help tremendously.

Example:

Table: “Projects” (Parent)
Field: “Project ID”
Values: 1,2,3,4 (4 different records)

Table: “Resources” (Parent)
Field: “Resource ID”
Values: 1,2,3,4,5 (5 different records)

Table: “Project Resources” (Child of “Projects” and “Resources”)
“Project ID” , “Resource ID”
1, 1
1, 2
1, 3
1, 4
2, 1
2, 5
3, 1
3, 2
3, 5

Currently the filter in “Project Resources” would have to be hard-coded:
Project ID = 1 (hard-coded) to show only the resources with Project ID of 1.

Filter: Project ID = 1
“Project ID”, “Resource ID”
1, 1
1, 2
1, 3
1, 4

However, the below filter would help establish a Parent/Child Relationship dynamically:

Filter: Project ID = {Projects}.Key (or the “current row value” of any field in the “Projects” table.

Where:

• “Project ID” is a field in table “Projects Resources” linking “Projects Resources” table to “Projects” table (a foreign key).
• {Projects} is the table “Projects”
• Key is the value of the “current row” of a field called “Key” in the “Projects” table.

This would allow you to see only the “Resource ID” records that are associated with the “Current Project”. So, as you traverse the records in the “Projects” tab/table, you would only see associated resources in the “Project Resources” tab/table when you switch to that tab.

[W_Vann_Hall]

Take a look at my recent code for conditional logic in linked records. It makes use of just such a dynamic filter — based on a field called, oddly enough, {DynamicFilter} :winking_face: — to do, well, pretty much exactly what you want.

There are several caveats given in the post, most of which probably won’t affect you — and one more that isn’t given that will: The tables defining the parent-child relationships [probably] have to be created up front. However, now I’ve typed that, it occurs to me it might be possible to define a new parent-child relationship on the fly. I never tested that, but I can’t think of any reason it wouldn’t work. (Based on the current code, doing so might throw an error, at least until the child record is created and properly linked — but in practice all that means is conditional record coloring and/or emoji coding will be used to highlight a possible error.) In any case, I can’t imagine it would be that hard to modify… (The biggest hurdle would be remembering to link newly created parent and child records to the single record in the [Control] table. I designed the base in such a way that the link-every-record-in-one-table-to-the-single-record-in-another-table kludge only had to be implemented in defining parents and children. Obviously, if you’re defining parents and children on-the-fly, you’ll have to find a way to ensure that link gets made on-the-fly.)

To be clear, the code is designed to limit the possible options presented for a secondary choice (‘subcategory’) based on an earlier selection (‘category’); however, to do so it makes use of linking to a filtered view, with the filter based on {DynamicFilter} = 1 — so switching to that filtered view should display only the applicable records, as you describe.

[Ash_Nazarizadeh]

Thank you for your response W_Vann_Hall.

While you have created a brilliant method for a “Conditional Select”, the solution is different that what I was requesting from AirTable, which is a “Dynamic Filter” in one table based on the value of the “Current Row” in another table. Your “Conditional Select” works great when a new record is being inserted in the “Services” table. As shown below the “Subcategories” table is showing a filtered list (but the “Categories” table is not).

However, the method works only when a new record is being inserted. As shown below, I have moved to the “Spray tan, dark” record in the “Services” table and the “Categories” and “Subcategories” tables are not showing values related to that row (current row) in the “Services” table.

The “Dynamic Filter” that I was referring to would have a filter on the “Categories” table saying, Services = {Services}.ID and a filter on the “Subcategories” table, Category = {Categories}.ID where ID would be the unique keys (row identifiers) within the respective tables (“Services” and “Categories”).

So, when you’re in the “Subcategories” table, the records that you see are filtered based on the current row (or the last row you viewed) in the “Categories” table, which in turn are based on the current row (or the last row you viewed) in the “Services” table.

By the way, the implementation of the “Current Row” may not be as trivial as it seems in a multi-user platform (such as AirTable) because it will have to be “Session” aware. When two users hit the same table, the current row of one user may be different than the other user. This may also be an issue with the “Conditional Select” design. Would it work if two users (2 Sessions) are entering records in the " Services" table simultaneously?

[W_Vann_Hall]

Oh, got it. Yeah, there’s no user-accessible information stored as to on which row one’s cursor is currently positioned. (Actually, there appears to be no accessible information stored, period; if you position the cursor in a row on one table, switch to another table, and return to the first, relative positioning within the first view is held — for instance, if I’m on row 4736 with rows 4732 through 4751 displayed, when I return to the first table I still see rows 4732 - 4751 — but row 4736 is no longer highlighted.) When you said “Current Row,” I was assuming you meant something like a {CurrentRow} checkbox field being checked.

I typically provide single-user solutions in my examples, because while there are ways to support potential conflicts in a multi-user environment, all require token passing or other mechanisms outside core Airtable functionality. While many can be built using pure Airtable, the best method of doing so tends to be very implementation specific. You’d be surprised how often the 80% solution involves shouting over the cubicle wall, “Hey! Is anyone in Airtable?” rather than doubling complexity and tripling development time to build a collision-avoidance solution that might come into play once or twice a week…

[Ash_Nazarizadeh]

Since the system keeps track of the current “collaborator”, I think it’s stateful and probably keeps some kind of a cursor in the backend to traverse records. The problem is that a table’s filter only accepts a hard-coded value. If it accepted [table].[field] then you’d probably be able to look at a linked table’s current row because it wouldn’t make sense for the system to wipe off all the rows (lose its state) as soon as you click off of its tab.

This is easy to do in any low-code, no-code web application development platform. It can also be scripted into spreadsheets like Google’s. Without this feature it’d be hard to have users go to a header tab (say, order header) and then the details tab (say, order details) and constantly have to filter out relevant records by typing things into a filter field. Serious web application (which AirTable can be) just don’t work that way.

I’ll take a look at the API to see if there something there that can be used for this.

[W_Vann_Hall]

It doesn’t lose its state – but it doesn’t maintain a record-level state. As far as I can tell, once you return to a previously visited view, your previous view-level state is restored – that is, you are displayed the same columns of the same records you previously visited. However, nothing is maintained regarding which record was previously selected. (Not previously active – because you can’t switch to another view with an active record in the current view: The act of switching automatically deactivates the currently active record.)

Easy to prove: Open a table; select a record; switch to another table; switch back; press the space bar. Nothing happens. If there was a current record persistently maintained, that record would open in an expanded view.

This stateful information is [presumably] stored on a per-collaborator basis; that is, if I shift back to an earlier table, I see the columns and rows I last displayed, not the columns and rows last displayed by any collaborator. (I agree collaborator support is depressingly limited: There is virtually no per-collaborator data accessible, except through the UI. My guess is there was a command decision to keep most of this information under wraps rather than try to work through all the possible ramifications of exposing it.)

I also agree it’s unlikely to expect a casual user (or, as is often the case these days, a temporary or contract employee) to become proficient at building filter formulas — or to expect any user to cheerfully accept having repetitively to enter data into a filter to create reports. The official solution – the creation and storage of multiple filtered views — only goes so far. Accordingly, I’ve built query engines (and even a query engine generator) for clients. The dynamic filter method I use in the conditional logic demo is another variation.

Actually, I’d be inclined to argue that requiring an explicit record selection is actually preferable UX than allowing the implicit selection of a record to filter subsequent views. I can just imagine the potential for confusion possible if switching from [Table A] to [Table B] could result in two very different views depending entirely on whether or not the user had clicked a cell in [Table A]. I’m not certain Google Sheets or even Excel support such a function – that is, allowing Sheet B to make a decision based on the cursor location in Sheet A. Sure, if the cell is actively selected — but that’s not the same thing.

[Ash_Nazarizadeh]

I am not saying that Table 2 (Tab 2) would always (and automatically) show only the records relating to the current row in Table 1 (Tab 1). I am saying that a filter that the user creates would look like Table 2.Foreign Key = [Table 1].[Key].

This user-created filter would always be associated with a user-created view.

Example:
Table 1 (Tab 1)
Primary Key
1
2
3

Table 2 (Tab 2)
Value, Table 1 Foreign Key
A,1(Foreign Key to Table 1)
B,1
C,1
D,2
E,2

Currently you can only enter: Table 1 Foreign Key=1 in the filter field to see Table 2 records of:
A,1
B,1
C,1

But if your filter could be set dynamically to: Table 1 Foreign Key = [Table 1].[Key] (instead of a hard-coded “1”), then you could have a true Parent/Child relationship. Of course, you could always switch to a different view where that constraint (filter) is not enforced.

I don’t think this is a complex thing to implement. All they have to do is save the last record visited (current record) of tables in a token/value pair array (table name, Key) and expose it to filters. That’s why I put [Table].[Key].

[W_Vann_Hall]

I’m still not sure why this is unacceptable:

Airtable

Dynamic Filter demo - Airtable

Explore the "Dynamic Filter demo" base on Airtable.

Admittedly, it is a little klunky to have to deselect the checkbox in [Projects] before selecting a different one, but once a checkbox in [Projects] is selected, going to [Projects/Resources] provides you a dynamically filtered view.

For a multi-user system, each user gets his or her own ✅' field, his or her own{DynamicFilter}`, and his or her own filtered view…

Or is there something here I’m missing?

[Ash_Nazarizadeh]

Your method works. It’s a great way to do a Conditional Select and currently it seems to be the only way to do Parent/Child as well (in the absence of Dynamic Filtering).

However, with the Parent/Child thingamajig it may be less practical from a user perspective because user’s choices are:

1. two clicks in the Projects table (uncheck old + check new) or
2. type a hard-coded Project Key in the filter field of Project/Resource

The advantage that 1 has over 2 is that the user doesn’t have to remember a key.
Disadvantage would be the overhead.

[W_Vann_Hall]

Well, method 1 is (to me) much more organic. I’m a user, examining the project table, and I want to see the resources assigned to a certain project. I check the box for the project and then move to the project/resource table, which shows me the allocated resources immediately.

With the second method, when I switch from the project table to the project/resource table, I’m immediately shown all resources allocated for all projects – or, worse, I see only the resources allocated to whichever project was last entered into the filter. I then have to open the filter dialog; clear the old filter, if one exists; remember which field I need to filter by; select the field from the drop-down; remember what my filter criterion needs to be; and enter it – correctly – in the filter dialog. While I agree some sort of exclusive checkbox would be ideal – sort of a cross-record radio button – having to clear the checkbox doesn’t seem too onerous compared to manually crafting filters. (And with a slight modification to the {Select} formula, the dynamic filter method can support having more than one checkbox checked – for instance, to show all resources allocated to either Project 1 or Project 2. I’ve even build query generators that allow the user to check two boxes and show resources allocated to both Project 1 and Project 2 or resources allocated to Project 1 but not Project 2, and vice-versa.)

Almost completely off-topic, there is another way of providing not-really-dynamic-but-user-friendly access to filtered views. Take another look at the demo base – I’ve made a modification:

Airtable

Dynamic Filter demo - Airtable

Explore the "Dynamic Filter demo" base on Airtable.

I’ve been up too long today (+/- 20 hours) to want to fool around with screenshots, but if you open that share, you’ll see there is now a URL field populated for each project. The URL links to a view filtered for that project, so now the user simply clicks on the project-specific URL to open a filtered view in the projects/resources table. Yeah, it’s even kludgier than the dynamic filter method, and it requires a certain amount of overhead — but it also removes a different layer of overhead, and it simplifies the user experience as much as possible. Admittedly, we’re now miles away from your original use case — but for some implementations, it might prove the most desirable approach.

Thanks for the dialog on this mechanism — it’s made me think about ways one might be able further to refine and simplify the use of dynamic filters…

[Ash_Nazarizadeh]

Basically with your checkbox method your’re manually identifying the current row. Following your lead using the checkbox method, I came up with this method, which eliminates some of the overhead (like the “.” table).

All I did was, I added a checkbox to Projects and looked it up in Projects/Resources and then filtered on the checkbox there.