Can I filter on a reference field using formula?

[Mohammed_Hamdy]

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?

[Justin_Barrett]

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

[Mohammed_Hamdy · Accepted solution]

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.

[Justin_Barrett]

Mohammed_Hamdy:

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?

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.

Mohammed_Hamdy:

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

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.