Skip to main content
Solved

Forcing or reformatting email from forms to lower case

0

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

Best answer by kuovonne

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.

 

View original
    Did this topic help you find an answer to your question?

    6 replies

    Shawn_Peters
    • Shawn_Peters
    • Participating Frequently
    • 18 replies
    • May 10, 2024

    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
    -Shawn?Time Wizard
    kuovonne
    Forum|alt.badge.img+17
    • kuovonne
    • Brainy
    • 5996 replies
    • Answer
    • May 10, 2024

    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.

     

    Shawn_Peters
    RonniEloff_VKDe
    0
    3 people like this
    • Shawn_Peters
      Shawn_Peters
    • RonniEloff_VKDe
      RonniEloff_VKDe
    • 0
      0800-grizzly
    0
    • 0800-grizzly
    • Author
    • Known Participant
    • 58 replies
    • May 13, 2024
    kuovonne wrote:

    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.

     


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

    Björn

    C
    • cch-online
    • Participating Frequently
    • 7 replies
    • September 27, 2024
    kuovonne wrote:

    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.

     


    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
    Forum|alt.badge.img+17
    • kuovonne
    • Brainy
    • 5996 replies
    • September 27, 2024
    cch-online wrote:

    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


    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.

    C
    1 person likes this
    • C
      cch-online
    C
    • cch-online
    • Participating Frequently
    • 7 replies
    • September 28, 2024
    kuovonne wrote:

    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.


    That's super helpful. Thanks very much!

    Reply


    Most helpful members this week

    Terms & Conditions