How to "match" similar records created by Airtable form

[South_Boston_Ne]

I am totally stumped as to how approach this and looking for advice!

We have an Airtable form that members of the public submit to ask for help from our nonprofit. The form creates a record in the table where we keep track of requests and our responses/outcomes.

We need a way to identify and “match” records from the same household so we can track duplicate entries and multiple requests from the same household over time. One complicating factor is that the name, street address, email, and phone number fields are all entered manually by the submitter, so they are rarely identical for all records submitted by the same person (e.g. email address may be capitalized in one instance and not in another). Also, not everyone shares a phone number or email address. I’m attaching a screenshot of the form below for context.

I did configure a Household ID field that does a mediocre job of assigning the an ID to records from members of the same household, but it’s definitely not foolproof and still dependent on messy data entries.

Ideally, for each new record I’d like to have a field that automatically searches existing records and links to the ones that match, using the Household ID field or some other identifier. That would allow us to see whether we’ve been in contact with the household before processing the submission. I would also like a way to rollup or group all records from the same household so we can see trends in repeat requests over time.

Is there a way to do any of this in Airtable? Are there integrations I should explore? A totally different approach that would be more appropriate?

All suggestions welcome! Thank you in advance!

[Databaser]

Hi @South_Boston_Neighbo and welcome to the community!

A noob answer could be to use the “dedupe” app manually (which works quite well I find) to identify exact/similar/fuzzy records and easily give them the same household ID. But no idea how you would do that automatically in Airtable without some scripting…

[Jeremy_Oglesby]

Here’s a possible starting place for a solution that involves the Script App:

Scripting Block: Link Surveys/Forms Submitted to Contacts Show and tell

The Problem: A common workflow in Airtable is the gathering of Surveys or Forms, and often we want to link multiple surveys or forms submitted by the same person back to a single Contact record for that person. However, exposing Linked Record fields to do this in the public form itself is undesirable because we don’t want form users to see all the names of existing contacts in our base, and it also precludes form users that don’t yet exist in our Contacts table. This leaves us using manual pro…

Please make sure to read the whole thread, because I made an important amendment to the original script, which is a few responses down.