filterByFormula - multiple select

Topic Labels: API
19734 15
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I am running into some trouble when using the API to return entries which contain a user’s role, and in my case each user has multiple roles (ex. pitcher, left_fielder). Each user’s role is stored using comma separated values in a separate Airtable table (i.e. a “multiple select” table). I am currently using this filterByFormula call via API:

FIND(“USER_ROLES”, role) > 0

It only seems to return results if the user has one role [FIND(“pitcher”, role) > 0], but fails when searching multiple roles [FIND(“pitcher, left_fielder”, role) > 0].

There’s got to be a way to have the API return records that “contain” one or all of the multiple select fields. Does anyone know how I can return results using a string of user roles via one filterByFormula call?

15 Replies 15

I’m doing it this way, but it only returns data when there is only 1 record linked.
How do I do for cases where multiple records are linked?

I managed using OR (FIND (" linked_record_id ", Column)> 0)
I believe this is less performative, as it is a string comparator on the server.
Only when I have many records will I be able to know the impact of this.

I was surprised to learn that Airtable does not work with IDs when linking foreign keys, as this is the default.

Does anyone know any method more efficient than the ones mentioned, and that requires only 1 request?

The method mentioned by @Chester_McLaughlin OR (RECORD_ID () =" recRjdJSziwMjfhO8 " worries me when there is pagination. It will be very manual work.

@Chester_McLaughlin thanks for your contribution!

5 - Automation Enthusiast
5 - Automation Enthusiast

I came back here to say that I’m still in trouble.
The method mentioned above does not work when the ID is in the middle of other IDs.
For example, in the field that references the IDs of the linked records, if I have recX, recY, recZ, I cannot find “recY” records.

Airtable ridiculously never allows API searches of arrays, which is what happens when you have multiple linked records in a linked record field. It also happens with lookup fields, too, which are also arrays.

The only way to solve this issue is to clutter up your Airtable database by adding additional fields. You’ll need to create a lookup field that shows all of the linked record ID’s. That field is an array. (It sounds like you may already have this lookup field in your table.)

Then, you’ll need to create a formula field that converts that array into a string. You can do this by concatenating your lookup field with empty quotation marks like this:

{Your Lookup Field} & ""

Then, you’ll need to use the API to search that new formula field.

I came back here to say that I found the possible solution.
Just set the search pointer to start 1, because 0 = end.
It is counter intuitive, as usual with Airtable, but it goes like this:

FIND (“linked_record_id”, Column, 1)

Thanks, but I managed without converting to String.
See in the previous post.