filterByFormula API not working for Linked Record fields with multiple records

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: 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.

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:[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).


This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.