Filtering Airtable records by multiple category values

Other Questions Airtable
1

I’m working with an Airtable database and need some help with filtering. My table is called ‘Inventory’ and has a ‘ProductType’ column. I want to let users pick multiple product types to search for.

For example, if a user selects ‘hats’, ‘gloves’, and ‘scarves’, how can I set up my query to return all items that match any of those types? Is there a way to use something like an ‘IN’ clause in Airtable?

Here’s a simplified version of what I’m trying to do:

let userChoices = ['hats', 'gloves', 'scarves'];
let query = `SELECT * FROM Inventory WHERE ProductType IN (${userChoices.join(',')})`;

Obviously, this isn’t the correct syntax for Airtable, but it illustrates what I’m aiming for. Any tips on how to achieve this kind of filtering in Airtable would be super helpful. Thanks!

2

hey, i’ve dealt with this before. try using the FIND() function in airtable. it’s pretty slick for this kinda thing. somethin like:

let filterFormula = 'OR(' + userChoices.map(c => 
  `FIND('${c}', {ProductType})`
).join(',') + ')'

this’ll match exact product types. works great for me, hope it helps ya out!

3

I’ve tackled a similar challenge with Airtable before. The approach I found most effective was using the ‘OR’ operator in combination with multiple filter conditions. Here’s how I structured it:

let query = base('Inventory').select({
  filterByFormula: `OR(
    ProductType = '${userChoices[0]}',
    ProductType = '${userChoices[1]}',
    ProductType = '${userChoices[2]}'
  )`
});

This method worked well for me, though it does require you to know the number of choices in advance. For dynamic filtering, I ended up using a function to generate the filter string based on the user’s selections. It’s not as elegant as a straightforward ‘IN’ clause, but it gets the job done efficiently.

One caveat: if you’re dealing with a large number of potential choices, you might hit Airtable’s formula character limit. In those cases, you may need to split your query into multiple requests and combine the results on your end.

4

I’ve found that using the SEARCH() function in Airtable can be quite effective for this type of filtering. Here’s an approach that might work for you:

let userChoices = ['hats', 'gloves', 'scarves'];
let filterFormula = 'OR(' + userChoices.map(choice => 
  `SEARCH('${choice}', {ProductType}) > 0`
).join(',') + ')';

let query = base('Inventory').select({
  filterByFormula: filterFormula
});

This method creates a dynamic filter that checks if any of the user’s choices appear in the ProductType field. It’s flexible and can handle varying numbers of selections without hitting character limits. Just be aware that this approach will also match partial strings, so ‘hat’ would match ‘hats’ and ‘hatbox’. If that’s an issue, you might need to adjust the logic slightly.