Filtering Airtable records containing a specific string plus other values?

Other Questions Airtable
1

Hey folks, I’m struggling with the Airtable API. I want to filter records that have a certain string, but they might have other stuff too. Right now, my code only grabs records with just that one string.

I’m using the filterByFormula method, but it’s not doing what I need. Here’s what I’ve got:

base('MyTable').select({
  view: 'Main View',
  filterByFormula: `SomeColumn = "${searchTerm}"`
}).firstPage((error, data) => {
  if (error) {
    console.log('Oops!', error);
    return;
  }
  data.forEach(item => {
    console.log(item.get('OtherColumn'));
  });
});

This only pulls records where SomeColumn matches searchTerm exactly. But I need it to find records where SomeColumn has searchTerm plus maybe other stuff too.

I checked the docs but couldn’t find anything helpful. These columns are ‘multiselect’ types, if that matters. Any ideas on how to make this work? Thanks!

2

As someone who’s worked extensively with Airtable’s API, I can tell you that the SEARCH() function suggestion is spot on. But there’s another approach worth considering, especially for multiselect fields.

Try using the IS_AFTER() function in combination with FIND(). It’s a bit of a hack, but it works wonders:

filterByFormula: `IS_AFTER(FIND('${searchTerm}', SomeColumn), 0)`

This essentially checks if the position of your searchTerm in SomeColumn is after the 0th character (i.e., it exists). It’s particularly effective for multiselect fields and handles special characters better.

One caveat: this method is case-sensitive. If that’s an issue, you can wrap both the searchTerm and SomeColumn in LOWER() functions to make it case-insensitive.

Remember, Airtable’s API can be finicky with large datasets. If you’re dealing with a lot of records, consider implementing pagination to avoid timeouts.

3

yo, have u tried using FIND() instead? it’s pretty sweet for this kinda thing. something like:

filterByFormula: `FIND('${searchTerm}', SomeColumn) > 0`

this’ll grab any record where ur searchTerm shows up in SomeColumn, no matter what else is there. Works great with multiselect fields too. Give it a shot!

4

I’ve encountered a similar issue when working with Airtable’s API. The solution lies in using the SEARCH() function within your filterByFormula. Try modifying your code like this:

filterByFormula: `SEARCH('${searchTerm}', SomeColumn)`

This will return all records where searchTerm appears anywhere in SomeColumn, regardless of other content. For multiselect fields, it searches across all selected options. Remember to handle potential errors if searchTerm contains special characters.

If you need more complex filtering, consider using multiple conditions with AND() or OR() functions. The Airtable API is quite powerful once you get the hang of these formula functions.