Forcing or reformatting email from forms to lower case

[0800-grizzly]

Hi,

Is there ways to (i) force an email address collected through a Airtable form and to (ii) convert an email address coming in from an external system to lower case?

We use Airtable as a CRM/ERP system, hence we register contacts and events for those contacts (for the purpose of this question). The first touch point is an Airtable form, after which the contact is directed to a Calendly event. The one piece of information connecting these inside Airtable (and a basis for a lot of automations) is the contact's email address.

Yesterday I debugged an automation, and realised it had not found the right contact due to a capital case beginning of the email in the contact's data. Had not thought about these (naturally) being case sensitive.

I see that one can user lower() in formulas, but would not want to use several "parallell" fields/columns for this. I tried to use lower() to encapsulate the data field in the automation, but that didn't seem to work.

Suggestions how to do this?

Rgds,

Björn

[Shawn_Peters]

Hi Björn,

• You can use a formula field to do this in the table the email resides in (where the form is submitted)
  
• Have it target the email field and use LOWER(email)
  
• then have the automation target the formula field for your search/find step
• alternatively you could have a new automation setup to update the email field when it does not match the lowercase formula field. 
  
  Cheers!
  Shawn

[kuovonne · Accepted solution]

You don't need to use a formula field or any additional fields. You do need an automation.

1. Use an automation with the trigger "when form submitted".
2. Use an "Update Record" action that updates the triggering record.
3. Set the {Email} field to the value of the {Email} field in the triggering record.
4. Then modify the token for the email value to lowercase. You might also want to modify the token again to trim any extra whitespace.

[0800-grizzly]

Thank you! I had seen these options at some point but didn't understand their context. What a timesaver :)!

Björn

[cch-online]

Hi @kuovonne – I have a similar scenario, expect I'm trying to fix the problem of user's submitting a form (via Fillout) using ALL CAPS for their names. I have an automation that checks if a user exists in our Contacts table, and if not, created a new user based on the Fillout form submission.

The problem I've run into is that if someone types their name in all caps, Airtable thinks it's a new user, even if the exact name already exists.

I saw someone elsewhere mention using CONTAINS instead of IS in the conditions for creating a new record, but that didn't seem to work for me. I also tried your suggestion above, but unfortunately the modifications aren't smart enough to know that if a name is JOHN that I would like to modify it to John. Lowercase makes it "John" and "capitalize" just affects the first letter, so since all letters in JOHN are already capitalized, there is no change.

Any other suggestions for how I might get around this?

Thanks much

[kuovonne]

It is possible to select both "lowercase" and "capitalize" for the token in the automation. You just have to manually go back and select each one.

Another possibility is using a helper formula field that converts all names into all lower case. Then have the automation look for records that match on the formula field. The existence of this helper field is what the original poster was trying to avoid.

[cch-online]

That's super helpful. Thanks very much!