Search code examples
airtable

Airtable API filter to return all records with a specific entry in a linked entity column

I need to be able to filter an Airtable table through the public API (using the Airtable.Net client>) so that it only returns rows that have a specific entry in a linked record field.

The field is configured to allow multiple linked records.

I've tried:

  • {fieldname} = "<the entity's object_id>"
  • FIND("<the entity's object_id>", ARRAYJOIN({fieldname}, ' ')) > 0

and I was working under the assumption that since all the Airtable API will return for the linked column is an array of Object Id's as strings then that is what I would search on - but both of these return no rows.

Can anyone tell me how to construct a filter formula that will filter based on a linked entity's presence in the column.

Thanks!

Solution

  • You were very close. When using FIND() on a linked record field, you have to search by primary field value of the foreign record (the one you see in the UI). Generally, when dealing with linked records, rollups, lookups or other array type values, Airtable treats them as strings in the formula engine.

    If we look at the User Studies template as an example: https://airtable.com/shrmLyfKRQYHMoeyT

    The Feedback Session table has a linked record field, Features Requested/Complaints Mentioned, to the Features/Complaints table.

    If I wanted all feedback that has been tagged with "Better search" then my filterByFormula value looks like:

    FIND("Better search", {Features Requested/Complaints Mentioned})

    Sometimes, you want to be able to use FIND and reference the underlying Airtable record IDs for the foreign records. To do that, we can create a rollup in the local table that concatenates all of the record IDs of the foreign records. To do this:

    1. In your foreign table (Features/Complaints in this example), create a new formula field called "Record ID" and use the formula RECORD_ID(). This field will now show the underlying record ID for each field in your foreign table
    2. In your local table (Feedback Session), create a new rollup field called "Foreign Record IDs". This field should use the linked record field that links to your foreign table. And then select the Record ID field we created in the previous step. Finally, the formula is ARRAYJOIN(values)

    Now in our local table, we have a comma separated string of every foreign record ID. So if we wanted to perform the same search using the foreign record ID rather than primary field:

     FIND("recvyl20eHSARMtVj", {Foreign Record IDs})

    Searching for Multiple Linked Records

    This solution works well when you only need to find local records which have a single linked record relationship. If we needed to find all records with links to "Better search" or "Mobile app" features, then you should use the OR function to chain multiple FIND functions together. Since this all based on checking string values, you cannot guarantee that linked records are represented in a specific order.

    This request:

        FIND("Better search,Mobile app", {Features Requested/Complaints Mentioned})

    would only provide records where the linked record has exactly "Better search" followed by "Mobile app." Instead we want to do:

        OR(
        FIND("Better search", {Features Requested/Complaints Mentioned}), 
        FIND("Mobile app", {Features Requested/Complaints Mentioned})
    )