filterByFormula API not working for Linked Record fields with multiple records

[Dylan_Golow]

I’m trying to use the Airtable JS library to pull a list of records filtered by a Linked Record field, and although I can verify the data from a certain record by pulling by ID, I cannot filterByFormula for this particular record using a Linked Record field where multiple records are linked. I’ve also just went straight to postman to ensure it wasn’t the JS library but in fact the API itself.

I found an answer here on how to use filterByFormula with Linked Record fields, however the solution is not working although I’ve followed it to the T: https://community.airtable.com/t/filterbyformula-multiple-select/2075/8. It actually works with Linked Record fields that are 1:1 and not when 1:many.

Please see my description of the problem here:

filterByFormula option does not work when used for the following scenario (filtering on Linked Record field with multiple records):

• Tables: Albums, Songs, Categories
• Albums have a Linked Record field for Categories
• Songs have a Linked Record field for an Album and a Lookup field to show the Album Categories in the Songs table

When searching via the API for a Question with a category (everything is URL encoded properly), it pulls zero results when searching for category name or by category record_id

• Album query {Categories} = 'category_record_id
• Song query {Album Categories} = 'category_record_id'
  The same is true when using the != operator, as well as when these are used in AND/OR operations.
  I also tried to use the Name of the Category instead of the record id, which didn’t pull any results.

My alternative for now is to use the FIND(“Category To Find”, {Album Categories}) > 0 when I want to include a Song from an album with that category, or FIND(“Category To Find”, {Album Categories}) = 0 when I want to exclude it. This works with AND/OR operations as well.

The issue with this though, is that it limits how we name Categories, because the names cannot collide with any starting substring. For example if I want to use the above logic for “Rock” but also have a category named “Rock n Roll” (contrived example).

Another alternative would be write my own filterByFormula function within my API since I can’t get any results using the filterByFormula here. Then I can just filter using the Airtable JS library and add my filter function on the records returned.

Posting this here to hopefully find out what I might be doing wrong, or at least help others if they run into the same issue.

[JonathanBowen · Accepted solution]

Dylan_Golow:

The issue with this though, is that it limits how we name Categories, because the names cannot collide with any starting substring.

Hi @Dylan_Golow - yes, the names collision is going to cause problems, but you could use FIND() with with the record IDs. For example, with a table like this:

and a formula of:

FIND("recftwPJRdcKrE9cH", {Category Record IDs} & '')

you won’t get any identifier collision. (Note: you have to stringify the lookup of record IDs to get the formula to work). In an API request you’re going to end up with something like:

https://api.airtable.com/v0/YOUR_APP_ID/Albums?filterByFormula=FIND(%22[THE_CATEGORY_REC_ID_HERE]%22%2C+%7BCategory+Record+IDs%7D+%26+'')

But, at this point, as you are already in code, it might be easier to pull all records back and filter in the code (as you suggest above).