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?

[Chester_McLaugh]

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

[Joe_Cool]

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?

[Chester_McLaugh]

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?

[Joe_Cool]

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”

[Chester_McLaugh]

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?

[Joe_Cool]

This worked wonderfully. Thank you!

[Robert_Andrews1]

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.

[Jon_Eynon]

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-networ... Link to error I am getting…

[Dylan_Golow]

@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!