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

[Joao_Proenca]

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:

[Tuur]

:thumbs_up: Nice one!

My pleasure. :slightly_smiling_face:

[Tom_Soderlund]

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

[Tom_Soderlund]

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