Searching Airtable for guests with compound surnames

Other Questions Airtable
1

I’m stuck with a tricky Airtable query. My wedding guest list has people with hyphenated last names. The problem is when someone puts in just part of their last name on the RSVP form.

For example, if someone’s last name is “Smith-Jones” but they only write “Smith”, Airtable can’t find them. I tried using REGEX and filterByFormula, but it’s not working right.

Here’s what I attempted:

const filter = `(AND({name} = "${firstName}", FIND(REGEX_REPLACE("${lastName}",'[^-]*','' ),{surname})>0))`;

Any suggestions on how to make this work? I’m using airtable.js if that helps. Thanks!

2

I’ve dealt with a similar issue in my event planning business. What worked for me was using a combination of FIND() and SEARCH() functions in Airtable, coupled with some JavaScript magic.

Here’s a snippet that might help:

const filter = `OR(
  AND({First Name} = '${firstName}', FIND('${lastName}', {Last Name}) > 0),
  AND({First Name} = '${firstName}', SEARCH(LOWER('${lastName}'), LOWER({Last Name})) > 0)
)`;

This approach casts a wider net. It checks for exact matches using FIND() and partial matches using SEARCH(). The LOWER() function helps catch variations in capitalization.

In my experience, this method caught about 95% of the tricky surname cases. For the remaining 5%, I ended up doing a manual review. It’s not perfect, but it saved me tons of time compared to checking everything by hand.

Remember to test thoroughly with your specific data set. Good luck with your wedding planning!

3

hey ryan, have u tried using the SEARCH() function? it might work better for partial matches. something like this could do the trick:

AND({First Name} = '${firstName}', SEARCH(LOWER('${lastName}'), LOWER({Last Name})) > 0)

this checks if the lastname is anywhere in the full surname. hope it helps!

4

Have you considered using a combination of FIND() and SEARCH() functions in Airtable? This approach might be more effective for partial surname matches. Here’s an idea:

AND(
  {First Name} = 'FirstName',
  OR(
    FIND(LOWER('LastName'), LOWER({Last Name})) > 0,
    SEARCH(LOWER('LastName'), LOWER({Last Name})) > 0
  )
)

This formula checks if the first name matches exactly and if the last name is contained within the full surname field. It’s case-insensitive, which helps catch more variations. You could implement this in your JavaScript code like this:

const filter = `AND({First Name} = '${firstName}', OR(FIND(LOWER('${lastName}'), LOWER({Last Name})) > 0, SEARCH(LOWER('${lastName}'), LOWER({Last Name})) > 0))`;

This method should catch partial matches in compound surnames without relying on complex regex. Let me know if you need any clarification on implementing this approach.