Search Airtable guest list with partial surname matches

Other Questions Airtable
1

I need help with an Airtable query. I’m trying to find guests in my wedding database using info from a form. The problem is some guests have double surnames with hyphens. When someone only puts in the first part of their surname, Airtable can’t find them.

I tried using REGEX in the filterByFormula like this:

(AND({name} = "${name}",FIND(REGEX_REPLACE("${surname}", '[^-]*', ''), {surname})>0))

But it’s not working. Any ideas on how to match partial surnames? I’m using airtable.js if that helps. Thanks!

2

I encountered a similar challenge when managing a large alumni database. One effective solution is to use the SEARCH() function in Airtable, which is case-insensitive and can handle partial matches. Try this approach:

filterByFormula: `AND({name} = '${name}', SEARCH('${surname}', {surname}) > 0)`

This should catch partial surname matches, including hyphenated names. If you’re still facing issues, consider creating a computed field that combines all parts of the surname without hyphens. This can serve as a more reliable search field for your queries. Remember to handle potential edge cases, like surnames with apostrophes or spaces, in your form validation process.

3

hey charlielion22, wedding planning can be a pain! have u tried using like instead of regex? something like this might work:

filterByFormula: `AND({name} = '${name}', FIND('${surname}', {surname}) > 0)`

this should catch partial matches. lmk if it helps!

4

I’ve dealt with similar issues in my event planning business. One approach that’s worked well for me is using the LOWER() function combined with FIND(). It makes the search case-insensitive and more flexible. Try this:

filterByFormula: `AND(LOWER({name}) = LOWER('${name}'), FIND(LOWER('${surname}'), LOWER({surname})) > 0)`

This should catch partial surname matches regardless of capitalization. It’s been a lifesaver for handling hyphenated names and typos in our guest lists. If you’re still having trouble, you might want to consider creating a separate field for each part of hyphenated surnames to make searching easier in the long run.