How to search Airtable database for individuals with compound surnames?

Other Questions Airtable
1

I’m working on a wedding guest list project using Airtable and airtable.js. Here’s my problem:

Some guests have hyphenated last names like Smith-Jones. But when they fill out the form, they might just put Smith. Airtable doesn’t match this automatically.

I tried using Airtable’s REGEX and filterByFormula to solve this. My attempt was something like:

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

But it’s not working as expected. Any suggestions on how to make this work? I need to match both the first name and at least the first part of the last name, even if it’s hyphenated in the database.

Has anyone dealt with a similar issue before? What’s the best way to handle partial matches for compound surnames in Airtable? Thanks for any help!

2

hey there flyingeagle! have u tried using the SEARCH() function instead? something like this might work:

SEARCH(LOWER({surname}), LOWER(“${lastName}”))

it’s case-insensitive and finds partial matches. could solve ur hyphenated name issue. lemme know if that helps!

3

Having worked extensively with Airtable for various projects, I can attest to the complexity of name matching, especially with compound surnames. One effective approach I’ve implemented is using a combination of FIND() and LEFT() functions. Here’s a formula that might solve your issue:

AND({First Name} = LOWER(“${firstName}”),
OR(
FIND(LOWER(“${lastName}”), LOWER(LEFT({Last Name}, LEN(“${lastName}”)))) = 1,
FIND(LOWER(“${lastName}”), LOWER({Last Name})) = 1
)
)

This formula checks for an exact match on the first name and then looks for the last name at the beginning of the surname field. It accounts for both full and partial matches of hyphenated surnames. Remember to adjust field names according to your specific Airtable setup.

Additionally, consider implementing data normalization during form submission to standardize name formats and improve match accuracy.

4

I’ve faced similar challenges with name matching in databases. One approach that worked well for me was using a combination of SEARCH() and REGEX_EXTRACT(). Here’s a formula that might help:

AND({First Name} = LOWER(“${firstName}”),
OR(
SEARCH(LOWER(LEFT({Last Name}, FIND(“-”, {Last Name}) - 1)), LOWER(“${lastName}”)) > 0,
SEARCH(LOWER({Last Name}), LOWER(“${lastName}”)) > 0
)
)

This checks for an exact match on the first name and then looks for the last name either as a full match or as the first part of a hyphenated surname. It’s not perfect, but it caught most cases in my experience. You might need to tweak it based on your specific data patterns.

Also, consider implementing a fuzzy matching algorithm if you’re dealing with a large dataset. It can help catch typos and slight variations in names.