Filtering Rollup fields through the API

[Joao_Proenca]

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

[Tuur]

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]

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

[Tuur]

Joao_Proenca:

[“10,11,12,13,14,15,16,17”]

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]

Some more information to see if it helps:

This is how the Product age field is created:

[Joao_Proenca]

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:

[Tuur]

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:

Joao_Proenca:

don’t know if its related to the type of field

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]

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]

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?

[Tuur]

Unfortunately that doesn’t work for arrays.

Joao_Proenca:

{Product age}=18

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.