I’m stuck with a tricky Airtable query. Here’s the deal:
I’ve got a form that collects name and surname. I need to search my Airtable base of wedding guests using these inputs. The problem is some guests have hyphenated surnames.
For example, if a guest’s full surname is Smith-Jones but they only enter Smith in the form, Airtable doesn’t find them.
I tried using Airtable’s REGEX in a filterByFormula with AND, but no luck. Here’s what I attempted:
(AND({name} = "${name}",FIND(REGEX_REPLACE("${surname}",'[^-]*','' ),{surname})>0))
Any suggestions on how to make this work? I’m using airtable.js if that helps. Really appreciate any input on this!
I’ve dealt with a similar issue when managing guest lists for corporate events. Your approach using REGEX is on the right track, but Airtable’s REGEX capabilities are somewhat limited. Here’s what worked for me:
Instead of trying to match the entire surname, I found it more effective to use the FIND() function to check if the entered surname is contained within the full surname in your Airtable base. You could try something like this:
AND({name} = '${name}', FIND(LOWER('${surname}'), LOWER({surname})) > 0)
This searches for the entered surname (converted to lowercase) within the full surname in your base (also converted to lowercase). It’s not perfect and might give some false positives if surnames share common parts, but it should catch hyphenated surnames.
If you’re using airtable.js, you can incorporate this into your query. Remember to handle potential errors and edge cases in your code. Hope this helps!
Having managed wedding guest lists before, I can relate to this challenge. While the REGEX approach is clever, Airtable’s implementation can be finicky. I’ve found success using a combination of FIND() and LOWER() functions for partial surname matching.
Try this formula:
AND({name} = ‘${name}’, OR(FIND(LOWER(‘${surname}’), LOWER({surname})) > 0, FIND(LOWER({surname}), LOWER(‘${surname}’)) > 0))
This checks if either the entered surname is within the full surname or vice versa, accounting for hyphenated names. It’s not foolproof, but it should catch most cases. When implementing with airtable.js, remember to properly escape your strings and test thoroughly with various surname combinations.
hey there! i’ve run into this problem before. have you tried using the SEARCH() function instead? it’s case-insensitive and can handle partial matches. try something like:
AND({name} = ‘${name}’, SEARCH(LOWER(‘${surname}’), LOWER({surname})) > 0)
this should catch hyphenated names without much fuss. good luck with ur wedding planning!