What's the best way to search Airtable for records with a specific string plus other content?

Other Questions Airtable
1

I’m trying to use Airtable’s API to find records that have a certain string in a column. But I’m stuck because the current setup only gives me records that have just that string and nothing else.

Here’s what I’ve got so far:

const findRecords = (mySearch) => {
  dataTable.select({
    view: 'Main',
    filterByFormula: `TargetColumn = "${mySearch}"`
  }).eachPage((pageRecords, fetchNextPage) => {
    pageRecords.forEach(record => {
      console.log(record.get('ResultColumn'));
    });
    fetchNextPage();
  });
};

This code only finds records where TargetColumn matches mySearch exactly. But I need it to find records that have mySearch and maybe other stuff too.

I looked at the Airtable formula docs but couldn’t figure it out. These columns are ‘multiselect’ types, if that helps. Any ideas on how to make this work?

2

hey there! for that, u can use the SEARCH() function in ur filterByFormula. Try something like this:

filterByFormula: SEARCH("${mySearch}", TargetColumn)

this’ll find records where mySearch is anywhere in TargetColumn, not just exact matches. hope that helps!

3

I’ve encountered a similar issue before. The SEARCH() function suggested earlier is a good start, but for multiselect fields, you might want to consider using the FIND() function instead. It’s more reliable for exact matches within a list. Try modifying your code like this:

filterByFormula: `NOT(FIND(\"${mySearch}\", TargetColumn) = 0)`

This approach will return true for any record where mySearch is found within TargetColumn, regardless of other content. It’s been quite effective in my projects dealing with multiselect fields. Remember to properly escape any special characters in mySearch if needed.

4

I’ve dealt with this exact problem in one of my projects. What worked for me was using the FIND() function combined with the ARRAYFORMULA() function. This approach is particularly effective for multiselect fields. Here’s the formula I used:

ARRAYFORMULA(OR(FIND("${mySearch}", ARRAYJOIN(TargetColumn, ","))))

This searches for your string within each option of the multiselect field. It joins all options into a single string and then looks for your search term. It’s been rock-solid in my experience, even with complex multiselect data.

Just replace the filterByFormula in your code with this. It should give you the flexibility you need without sacrificing performance. Let me know if you need any clarification on implementing this!