FilterByFormula - Search linked field value in formula only (no rollup nor count fields)

[lsagetlethias]

Already seen:

• /t/if-contains-formula/10126
• How can I count the number of linked records in a linked record field (column)? – Airtable Support
• /t/counting-linked-records-formulaically/20537
• /t/formula-to-count-records-in-linked-tables-based-on-values/32018

Hello Airtable Community :slightly_smiling_face:

I’m currently consuming Airtable via the API in Javascript and I want to fetch some records accordingly to some linked field based criteria.

I have a linked field (called “trainings”) and I need to return records containing only a specific linked value.
The way is obviously with filterByFormula options in my base.select() function BUT I cannot use SEARCH() or equivalent in my formula:
e.g.

base.select({ filterByFormula: "SEARCH('Some Training Name', trainings)" });

The reason is that “training”'s names may have a comma or any other special char and a formula is computing a linked field by default by concatenating corresponding primary field to a single comma separated string…

I want to have the same behaviour as a view’s filter regarding linked fields ; but I cannot rely on views as their it’s not possible to preconstruct them programatically and because my criteria is dynamic.

Reproduction : Airtable - Repro Count LinkedField Formula

Thanks in advance for the help.

[lsagetlethias]

Up! Any help on this subject is very warm welcome!

[kuovonne]

Welcome to the Airtable community!

There are a few ways of going about this. One way is to create a new rollup field for the value in question and include manual beginning/ending delimiters around each value.

For example, have the rollup produce results like this:
[A][B][A,B]
[A][B]
[A, B]
[B][A][A, B]
[B][A]

Then you can search on the rollup field for the value surrounded by the delimiters.

lsagetlethias:

Any help on this subject is very warm welcome!

By the way, this is a community forum, and most of the people who answer questions here are volunteering their time and knowledge. Your question may have gone unanswered because many members of the forum are not familiar with using the rest API and simply didn’t know how to answer.