filterByFormula - multiple select

It does seem like the API would/should provide more array functions. Something like PHP’s in_array() function would be extremely useful. That and being able to access fields from linked records in a single query. But until then, here’s how I handle this scenario:

I would recommend not using the FIND function as it will match partial strings. Consider if you have two roles, “Pitcher” and “Pitcher’s Assistant”. You’ll never be able to use the FIND function to retrieve only “Pitcher” users as it will also match “Pitcher’s Assistant”.

I would recommend instead to do two queries: 1) to retrieve the records from the Roles table that match what you’re looking for. 2) to use the RECORD IDs from the Users field in the Roles records to construct a query that will return the full Users records.

So, if you wanted to get all users who are either Pitchers or Catchers you would use this filterByFormula on the Roles table:

OR(
    {Name} = 'Pitcher',
    {Name} = 'Catcher'
)

That returns a result set like:

In each of the returned records, you would have an array of RECORD IDs in the Users field for any linked records for that Role from the Users table. You could then construct a single query (or one query per role) for the Users table using a filterByFormula that looked a little something like this:

OR(
    RECORD_ID() = 'recMzWnfK8wuT9MFZ',
    RECORD_ID() = 'recdRonUzKAIMPOxb',
    RECORD_ID() = 'recRjdJSziwMjfhO8',
)

You would then have two result sets, one for Roles and one for Users and both would be “filtered” by the Roles you initially queried from the Roles table. And if you need to additional filter based on number of roles (i.e. I only want Pitchers if that’s their ONLY role) you could use a COUNT field in the Users table and use this as your filterByFormula:

AND(
    {Role Count} = 1,
    OR(
        RECORD_ID() = 'recMzWnfK8wuT9MFZ',
        RECORD_ID() = 'recdRonUzKAIMPOxb',
        RECORD_ID() = 'recRjdJSziwMjfhO8',
    )
)

So, all that said, as long as you don’t think you’ll end up with the “Pitcher” and “Pitcher’s Assistant” issue, you still could do this using the FIND function as you initially did—all you were missing was the use of the AND or OR function:

OR(
    FIND("Pitcher", Roles) > 0,
    FIND("Left Fielder", Roles) > 0
)

You could use AND instead of OR to ensure the user has all the roles specified.

You can get even more complex with nested conditions. This will return users who are both Pitcher and Left Fielder OR catcher.

OR(
    AND(
        {Roles} = "Pitcher",
        {Roles} = "Left Fielder"
    ),
    {Roles} = "Catcher"
)

OR(
    {Name} = 'Pitcher',
    {Name} = 'Catcher'
)

OR(
    RECORD_ID() = 'recMzWnfK8wuT9MFZ',
    RECORD_ID() = 'recdRonUzKAIMPOxb',
    RECORD_ID() = 'recRjdJSziwMjfhO8',
)

AND(
    {Role Count} = 1,
    OR(
        RECORD_ID() = 'recMzWnfK8wuT9MFZ',
        RECORD_ID() = 'recdRonUzKAIMPOxb',
        RECORD_ID() = 'recRjdJSziwMjfhO8',
    )
)

OR(
    FIND("Pitcher", Roles) > 0,
    FIND("Left Fielder", Roles) > 0
)

OR(
    AND(
        {Roles} = "Pitcher",
        {Roles} = "Left Fielder"
    ),
    {Roles} = "Catcher"
)

Thank you for the detailed writeup, @Chester_McLaughlin . You’ve detailed a lot of great methods here and I just implimented nested conditions on another page of my application.

The problem right now is that I am using dynamic data inside of filterbyformula to display upcoming games that each player is capable of playing in. My application only wants to display games that have an open position the player can fill in for…in other words, it reads the players’ roles from the Player table (“pitcher, left_fielder”) and inserts that array into the filterbyformula to query the Upcoming Games table and then list out on the webpage any upcoming games that specific player can play in. It seems like creating a separate column for each role (Pitcher, left_fielder, etc.) is the only way around this as there is no way to split the array into separate queries. Is this correct?

No, you don’t need to create a separate column per role.

I’ll send you a private message with my email address and if you share the base with me I can provide more specific examples. Also, in which language is your application written?

Thanks for sending over more info in the PM. I’m just writing a basic json request to display the info a JS repeating group. Here is my API call:

GET https://api.airtable.com/v0/mybasexxxxx/players

Parameters are as follows:

api_key: xxxxxxxxx
filterByFormula: role=“INSERT_AIRTABLE_ARRAY_OF_ROLES”

You can’t use an array inside a filterByFormula string. You need to create your filterByFormula string by looping through your ARRAY_OF_ROLES.

And while using the FIND function could work like this:

OR(
    FIND("Pitcher", Roles) > 0,
    FIND("Left Fielder", Roles) > 0
)

that approach is potentially error prone because you using partial string matching. You should first query the Roles table, then use JS to build an array of Player’s RECORD_ID() associated with those records. You can then retrieve the players by their RECORD_ID()

OR(
    RECORD_ID() = "recRjdJSziwMjfhO8",
    RECORD_ID() = "recdRonUzKAIMPOxb",
    ......
)

Does that make it any clearer?

OR(
    FIND("Pitcher", Roles) > 0,
    FIND("Left Fielder", Roles) > 0
)

OR(
    RECORD_ID() = "recRjdJSziwMjfhO8",
    RECORD_ID() = "recdRonUzKAIMPOxb",
    ......
)

This worked wonderfully. Thank you!

How do you use filterByFormula SEARCH inside a curl URL?
Let’s say I want to return only the record whose “Published URL” column value is “http://www.example.com/folder/file.html”

I don’t quite understand how to formulate it.

I am running this or script on a record id and I am finding that I am running into issue on IE 11. Could my filterbyformula be too long?
“We were also running into this issue and the problem was the target URL was exceeding the maximum length of ~2,000 characters in IE11 .”
https://stackoverflow.com/questions/29903550/how-to-solve-the-error-script7002-xmlhttprequest-network-error-0x80070005-ac Link to error I am getting…

@Chester_McLaughlin thanks for this tip! I noticed I can’t get it to work when trying to retrieve data in this way from a lookup field of a linked record. For instance Song A has an Album B and I’ve added Categories (also its own record) to a Linked Record field to Album B. When I set the lookup field on the Songs table (Lookup from Album > Categories), it shows the categories for Album B in the table, but when pulling via the API is doesn’t work to enter the filterByFormula={Album Categories} = ‘random_record_id’.

Have you encountered this and found workarounds? I figure I must be doing something wrong.

Thanks for any help!

OR(
    FIND("Pitcher", Roles) > 0,
    FIND("Left Fielder", Roles) > 0
)

OR(
    RECORD_ID() = "recRjdJSziwMjfhO8",
    RECORD_ID() = "recdRonUzKAIMPOxb",
    ......
)

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!

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.