Dynamic Rollup Filters

[Brian_Schuster]

Hey I am working on an app that uses the Airtable API to upload and download data, and I need to roll up a filtered set of records to put that list into a new record field. Essentially I’m looking for a way to enter a field name in the filtering options rather than a static value.

[Justin_Barrett]

Field names won’t work in the condition options for lookups, rollups, etc. The only thing recognized there are literal values (numbers, text, etc.), and Airtable looks for these values in the target table—the table whose records are being linked in the current table, not the table where the rollup field lives.

Brian_Schuster:

I am working on an app that uses the Airtable API to upload and download data, and I need to roll up a filtered set of records to put that list into a new record field.

Because you’re using the API (which, admittedly, I’ve not used myself), I believe the preferred method for filtering records is to use its “filterByFormula” option to filter one table’s records using a specific formula. That would return the filtered list, which you can then use elsewhere as needed. Or am I missing something?

[Brian_Schuster]

FilterByFormula is not working with multiple inputs. Can I put in a support ticket for that, or is this the right place to get that fixed? Dynamic rollup filtering would also be an essential feature to have working for Airtable in order for it to be a business-grade tool.

[Justin_Barrett]

Brian_Schuster:

FilterByFormula is not working with multiple inputs.

What do you mean by “multiple inputs”? Are you talking about querying more than one table at the same time? From my understanding, the API isn’t designed to do that. If you need to collect data from more than one table, you make separate API requests of each table, filtering as best you can with each one using filterByFormula, then do more detailed filtering in your code.

Brian_Schuster:

Can I put in a support ticket for that, or is this the right place to get that fixed?

This is primarily a user community. While Airtable staff do post on occasion, they prefer direct contact via Airtable support in situations where Airtable is truly not performing as designed. However, as I said above, the API isn’t designed for robust queries spanning multiple tables, so it’s not something that needs to be fixed per se. It’s currently designed for single-table queries, and I’m not sure how soon (if ever) that paradigm is going to change. Airtable is notoriously tight-lipped regarding their development roadmap.

[Brian_Schuster]

I understand. It’s just that it is an essential feature for the data behind modern web and mobile apps.

Here’s what I shared via the support ticket system:
“Hi, I posted in the forum about a couple issues. The filterByFormula function is not accepting filters that use AND() for multiple fields. I thought I could work around it with a rollup within Airtable, but that currently only accepts static inputs with those data filters. If I could enter something in the format of {field name here} for the filter value, that would enable me to work around the filterByFormula issue.”

[Brian_Schuster]

Dynamic rollup filtering would also help with counts for the number of items that match a certain set of criteria. If rollups could be done without linked fields (instead using only static and dynamic filters), it would be even better.