Jan 12, 202310:41 AM - edited Jan 12, 202310:42 AM
Hi Airtable community,
I have a Airtable CRM base that stores contact records. I input additional contact records into this base from a variety of other platforms via Make/Integromat, such as Mailchimp, WooCommerce, WordPress, Google Sheets, etc.
An example automation workflow in Make/Integromat would look like this:
Step 1: New contact record (via a new row) in Google Sheets >
Step 2: Search Airtable (contact) record (by searching a contact's email) >
Step 3: Upsert Airtable (contact record)
In other words, "upsert Airtable contact record" means if the Airtable contact record's email field matches from Step 2 (search contact by email), then update the record. If Airtable does not have a contact record with the email from G.Sheets, create new record.
This automation works great, but the problem is if a contact record already exists in Airtable, but that person enters a different email from their pre-existing email in Airtable, a new Airtable contact record will get created. For example, John Doe's email in Airtable = email@example.com, but he enters in GSheets a different email like firstname.lastname@example.org. Now I have two "John Does". I don't want Make/Integromat to search Airtable by contact name, because there might be hundreds of John Does out there.
To address this, I've added in a new field called "Email Secondary". But I don't know how to set this up in Make/Integromat.
Below is the ideal logic I'd like to happen. For understanding's sake, here's definitions:
emailGSheets = email entered into a G.Sheets row
emailField1 = email field in Airtable called "Email Primary"
emailField2 = email field in Airtable called "Email Secondary"
If emailGSheets = emailField1, enter emailGSheets into emailField1.
If emailGSheets != emailField1, skip emailField1, check emailField2.
If emailGSheets = emailField2 OR emailField2 is blank, fill in emailField2 with emailGSheets.
Any help would be greatly appreciated! Thanks so much!!
If the email is only reliable identification of your customer and they previously provided you email@example.com and now again firstname.lastname@example.org. -> you will end up with 2 records. After that it really looks like a task for a human if the John Doe above is the same person.
However it you have both emails in Airtable already you could use following formula in Search Module in Make.: OR(emailField1="email from GSheets ",emailField1="email from GSheets ")
This way future submissions are matching correctly.