Help

Filtering Rollup fields through the API

Topic Labels: API
8603 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Joao_Proenca
4 - Data Explorer
4 - Data Explorer

Hi,
I’ve been trying to filter a Rollup field through the API, using the filterByFormula option. It returns a string of numbers, since it uses the ARRAYCOMPACT(values) as the aggregation function. (ex: Product age : [“10,11,12,13,14,15,16,17”] )

I can’t find how to filter through these number, Find or Search doesn’t return any values ( ex: (SEARCH({Product Age}, “13”) > 0) , and also not with a simple “=” ({Product Age} = 13) .

I want to be able to filter by that field, but I’m currently unable to find out what’s the correct format for the filterByFormula option.

Can anybody help me?

Best,
João

13 Replies 13
Tuur
10 - Mercury
10 - Mercury

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.

Joao_Proenca
4 - Data Explorer
4 - Data Explorer

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.

Joao_Proenca
4 - Data Explorer
4 - Data Explorer

Some more information to see if it helps:

This is how the Product age field is created:
8e395444b2f3494184266a3416f1492c11fcb0de.png

Joao_Proenca
4 - Data Explorer
4 - Data Explorer

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:
1c574e47ac7ef5cf2c7c9af98216576f9320645a.png

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?

Joao_Proenca
4 - Data Explorer
4 - Data Explorer

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:

Joao_Proenca
4 - Data Explorer
4 - Data Explorer

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:

Tom_Soderlund
4 - Data Explorer
4 - Data Explorer

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()

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()