Email Correction after Form Response

[JennJ]

Hi everyone! 

I have a follow up survey that we send to customers via Airtable form. No prefilled info, pretty simple rating, feedback, and customer email to be filled out. Here's the kicker - we got a response back, and the customer entered @gmail.con instead of @gmail.com. The problem is, I have several follow on automations that are dependent on that email field to match it to the original survey request and then update both records accordingly, notify the user, etc. I already have modifiers on my other automations that corrects any caps to all lowercase, trim, etc to ensure the data is clean and matches. I considered using the "length" modifier, but considering I have no way of knowing how long any given customer's email address will be, that's not really an option. 

Now, I know I COULD just manually correct the email address, but I don't want to do that every time, and that also effectively avoids the trigger for one of my automations, "when form is received" do XYZ. 

I'm probably going to have to code something in JS and run the automation as a script, but I'm not really sure how to do that, and that's where I'm hoping you guys can help! Does anyone have sample code to replace text in my {Customer Email} field, while leaving the correct text alone? Some sort of replace ".con" with ".com" in that field only. Not sure if it's needed, but the table itself is called "Re-Surveys".


Hoping something like this would work? I'm getting an error still, and not sure why. 

let inputConfig = input.config();
let email = inputConfig.Email;
let recID = inputConfig.Record;
let table = base.getTable("Re-Surveys");

async function updateRecord() {
  const recordId = recID;
    console.log("recID: "+recID)
    console.log("recordID: "+recordId)
  const stringValue = email.replace(".con",".com")
    console.log("stringValue: " + stringValue)
  try {
    const updatedRecord = await table.update(recordId, {
      'Customer Email': stringValue
    });

    console.log('Record updated:', updatedRecord);
  } catch (error) {
    console.error('Error updating record:'+error);
  }
}

updateRecord();

[JennJ · Accepted solution]

GOT IT!!!!! 

I'm going to have to add the steps from my "when form is submitted" triggered automation, but this code works to correct the email domain. Also added a correction for Case Sensitivity. 

Input variables (both from the trigger "when record matches condition" w/ {Customer Email} contains .con):

let inputConfig = input.config();
let email = inputConfig.Email;
let recID = inputConfig.Record;
let table = base.getTable("Re-Surveys");
let stringVal = email.replace(".con",".com")
    console.log("stringVal: " + stringVal)
async function updateRecord() {
  const recordId = recID;
    console.log("recID: "+recID)
    console.log("recordID: "+recordId)
  const stringValue = stringVal.toLowerCase()
    console.log("stringValue: " + stringValue)
  try {
    const updatedRecord = await table.updateRecordAsync(recordId, {
      'Customer Email': stringValue
    });

    console.log('Record updated:'+updatedRecord);
  } catch (error) {
    console.error('Error updating record: '+error);
  }
}

updateRecord();

[ScottWorld]

@JennJ 

That's a good solution, but it will only catch that one particular typo. It won't catch any other typos.

Your best solution would actually be to do a full email validation to make sure that it actually is a valid & working email address that was typed in 100% correctly.

It would be even better for the email address to be validated PRIOR TO the form being submitted, to catch the errors ahead of time and alert the user that they have a typo.

1. The #1 best way of doing this would be to use Fillout’s advanced forms for Airtable, which natively offers this email validation ability — in addition to letting your users update Airtable records directly from a form (i.e. no automations needed on your end).

Please see screenshot below for how easy it is to enable this email validation in Fillout.

2. If you want to validate the email AFTER the form is submitted, you can use Make's advanced integrations to validate your email addresses after the form is submitted.

Here are the different email validation services that Make natively supports, but you can always write your own custom-code to use any email validation service that you would like to use.

If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one way that you can instantly trigger a Make automation from Airtable.

3. Since you know Javascript, you can write your own custom Javascripts to tap into any of those email validation services as well!

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo]

You could also try using "Update Record" instead of putting that functionality in the script.  That way changes to the field or table names won't affect the automation and the script becomes a lot simpler!

let {email} = input.config()

output.set("Fixed email", email.replace(".con",".com"))