Can I filter on a reference field using formula?

So I have a table with a field Client that references a record from another table. Client column as such can contain a value such as ['rec1234abc']. Now I want to do a filter to return all records that reference a specific record. I tried this (using the Python client):

my_table.get_all(formula="ARRAYJOIN(Client, '')='rec1234abc'")

But that returns me no results. How can I accomplish this?

When referencing the data from a link field using the API, what you get back might be an array of record IDs. However, that’s not what comes back when referencing a link field in an Airtable formula, and the last part of that statement is key: you need to design your formula based on how the actual formula would operate within Airtable, not based on how you get data back from the API.

A common misconception is that a formula referencing a link field will return an array of values, but that’s not the case. What Airtable returns from a link field is simply a string containing the text from the primary field of all linked records, separated by commas.

To get the record ID of the linked record, you’ll need to first make a {Record ID} field in your [Clients] table (guessing the name based on the {Client} link field), roll that up in the table where the {Client} link field lives—I’ll call this rollup field {Client Record ID}, and suggest using ARRAYJOIN(values, "") as the aggregation formula so that you get a string—and then compare against that in your API call:

my_table.get_all(formula="{Client Record ID}='rec1234abc'")

Thank you for your clarification @Justin_Barrett.

Based on your answer then the following should work and we don’t need a formula here:

my_table.search("Client", "rec1234abc")

However, this also returns empty. Maybe I’m missing something here. Can we reference a reference field only using a formula?

Also your answer suggests using ARRAYJOIN and then it goes ahead and doesn’t use it in query.

First off, the field type is a link field, not a reference field. You’re creating a link between records across specific tables.

That aside, I’m not sure how the Python client operates (it’s not an official Airtable offering as far as I know), so I can’t offer much help with the specifics of the implementation. For example, what data types does the search method expect and return? Are you sure that searching a link field returns the record IDs of the linked records? It’s possible that it’s returning the primary field text (similar to a formula), so searching that text for a record ID won’t work.

That’s because it’s used in the aggregation formula of the rollup field that I described. I rewrote the formula in the query to look at the output of that rollup field, which is going to be the string created by the aggregation formula.

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