filterByFormula - multiple select

[Joe_Cool]

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?

[Get_Compras]

Chester_McLaughlin:

essa abordagem é potencialmente sujeita a erros porque você usa correspondência parcial de strings. Você deve primeiro consultar a tabela Roles e, em seguida, usar JS para construir um array de RECORD_ID () do Player associado a esses registros. Você pode então recuperar os jogadores por seu RECORD_ID ()

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?

[Get_Compras]

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!

[Get_Compras]

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.

[ScottWorld]

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.

[Get_Compras]

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)

Airtable - Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

[Get_Compras]

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