Filtering Rollup fields through the API

Hi João,

I’m not completely sure what you mean, but maybe this will point you in the right direction…

http://timdietrich.me/blog/airtable-api-filter-records-and-fields/

Best,

Arthur.

Hi Arthur,
thanks for the link but unfortunately doesn’t solve my problem (while it help me a lot previously).

The thing is I can search for strings in “normal” fields, I’ve used for example, (FIND(“California”,Cities) > 0) and it works, returning me the rows that have the Cities field with California. This column Links to another table (named Cities), where the values come from.

The Product age on other hand, is a Rollup field that is fetched from another table, and the fields are ARRAYCOMPACT before presentation (returning the array I’ve exemplified in the first post). I thought that I could search it as a string, since it looks like a string, but in that case it only returns fields that don’t have data in them, when I use this formula (FIND({Product age}, “18”) > 0), or ({Product age} = 18).

So I can’t find a way to filterByFormula through a Rollup field, don’t know if its related to the type of field, or if I’m doing the formula wrong.

Best,
João

Are you sure it looks like this? I’d say the quotes are suspect…

I’ll build your example in a minute to see if I can help.

Some more information to see if it helps:

This is how the Product age field is created:

And this is how the data of that field arrives through the API json response, if I don’t use a filterByFormula on the field:

Two things. First of all the FIND function works the other way around.

So

(FIND({Product age}, “18”) > 0)

becomes

(FIND(“18”, {Product age}) > 0)

Just to be sure. :slightly_smiling_face:

You’re probably right. The FIND function only operates on strings. You could do a conversion first using ARRAYJOIN.

E.g. something like (FIND(“10”,ARRAYJOIN(myIntAgeRollup))

The fact that ARRAYCOMPACT output looks like a string is a bit misleading indeed. Underneath the values keep their original data type. It’s a pretty common JSON trap (read: I’ve bumped my head multiple times). :winking_face:

Does this help?

Thanks that helped a lot!

It’s now returning values, and helped me understand a bit better how the formula works, meanwhile I figured that using FIND it’s not the best approach, since if I try to get results with 8, I will get everything that has an 8 in it (18, 28, etc).
So my follow up question is, how can I search for the number? I’ve tried {Product age}=18 for example, but it’s just returning rows that don’t have any Product Age, do you know why that is?

What if I want a range? From the docs I though about AND({Product age}>16,{Product age}<20), but it gives me the same response as the previous, just sends me the rows that don’t have any data in that row.

Again thanks for the help! :grinning_face_with_big_eyes:

Ok so the Product Age that is fetched from another table is a Multiple Select, so its text (the table was created by someone else, I’m currently trying to create a simple react app that uses the API to help filter the results in a specific way).

Is there a way the Multiple Select field can be changed to some type that has multiple numbers, or can I convert the results to a array of numbers, so I can search for a number, or a range of numbers?

Unfortunately that doesn’t work for arrays.

This works, but only if the array as a whole is a match (meaning one number in the array: 18).

Right now it looks like you have two options: get the record and write some code to search through the array the proper way or do some advanced string trickery in your query (like using delimiters and combine those with for example left, len & value functions).

That is a pretty shaky solution though. IMHO.

I’ve updated the table, and changed the Product Age, by using 2 values Min Age, and Max Age, as the date will be a simple range. This way I can get a single date and or a range of dates.

Thanks for all your help! :slightly_smiling_face:

:thumbs_up: Nice one!

My pleasure. :slightly_smiling_face:

This page was very helpful for me when using filterByFormula and the Airtable JS API.

The formula FIND("CATEGORY_NAME", ARRAYJOIN({Categories})) > 0 will return 1 if CATEGORY_NAME is found in Categories field.

Full JavaScript code example:

const airtableBase = () => new Airtable({ apiKey: process.env.AIRTABLE_API_KEY }).base(process.env.AIRTABLE_DATABASE)
const query = { filterByFormula: `FIND("${categoryName}", ARRAYJOIN({Categories})) > 0` }
const records = await airtableBase()(TABLE_NAME).select(query).firstPage()

const airtableBase = () => new Airtable({ apiKey: process.env.AIRTABLE_API_KEY }).base(process.env.AIRTABLE_DATABASE)
const query = { filterByFormula: `FIND("${categoryName}", ARRAYJOIN({Categories})) > 0` }
const records = await airtableBase()(TABLE_NAME).select(query).firstPage()

Updated version, since Airtable uses basic string matching, my above code would return both “Apple” and “Pineapple” on the query “Apple”. This fixes that

const query = { filterByFormula: `FIND(", ${categoryName}, ", ", " & ARRAYJOIN(Categories) & ", ") > 0` }

Here’s a handy helper function filterByRelatedTable that you can use:

const filterByRelatedTable = (tableName, recordName) => ({ filterByFormula: `FIND(", ${recordName}, ", ", " & ARRAYJOIN(${tableName}) & ", ") > 0` })
const records = await airtableBase()(TABLE_NAME).select(filterByRelatedTable('Categories', 'My Category')).firstPage()