Validate an email address in a contact list

TravisK
TravisK ✭✭✭
edited 12/18/20 in Formulas and Functions

Hello,

We have a situation where we have two employees with the same name, "Test User", and they have the following email addresses:

test.user@smartsheet.com

test.user2@smartsheet.com (Please notice the "2" in this email.)

We use a form to populate our sheet and the form user is required to type(paste) the email address into the form. The form users routinely get confused and put the wrong email address in, which assigns the case to the wrong person. In this case, test.user@smartsheet.com is always the incorrect email address but often gets pasted into the form.

I want to build a formula that I can ultimately use with an automation to send an alert or update request to the form submitter to fix the email address when "test.user@smartsheet.com" is submitted. However, because the submitted email address is mapped to a Contact List column, Smartsheet automatically changes it to the Contact Name. Since both users are named "Test User", I haven't been able to figure out how to use a formula to determine if this contact is test.user or test.user2...

I've tried using FIND, CONTAINS, and HAS, but none of these can check the actual email address once Smartsheet has displayed the Contact Name. See my tests below. None of the formulas return a "match" when searching for an email address...

Any help would be greatly appreciated.

Thanks,

Travis

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How many different names/emails could be entered into the form?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @TravisK ,

    I'm curious about this as well. The only solution I've found is to use automation to copy the row to another sheet. When you do that the other sheet also contains the email address. From that, you can parse our the email into a separate column. Then use VLOOKUP (index/match) in your original sheet to return the email from the other sheet. If you add Auto-Number to your main sheet that becomes a good key for the lookup.

    Hope that helps, or leads to another solution.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • TravisK
    TravisK ✭✭✭

    Could be hundreds, and it could change at anytime. The email address belongs to an employee supervisor and an existing automation will send that supervisor an email that a case has been entered.

    We had considered a drop-down, but that became unrealistic due to the number of potential email address that could get added.

  • TravisK
    TravisK ✭✭✭
    edited 12/20/20

    @Mark Cronk - Thanks for the idea. Copying over to another sheet copies the values and then we could use the formula. We could even set it up so that the only purpose of the second sheet is to perform that function.

    We might have to do something like that if we can't find a better solution.

    It's unfortunate that there's no way to access the other details of a Contact. I'll put in an enhancement request for a new formula. Something like:

    =ContactEmail(lookup cell)

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @TravisK ,

    Good idea. Worth submitting as an enhancement. I'll be monitoring to see if someone else has a solution.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • TravisK
    TravisK ✭✭✭

    I submitted the following as a product enhancement:

    I'd like to be able to access the email address(es) of a Contact that is in a Contact List column.  Currently, when an email address gets put into a Contact List formatted column, if a contact that matches that email is available, Smartsheet will replace the email address with the contact name.  At that point, there is no way of accessing the stored email address with a formula for any type of data validation. This does not work with FIND, CONTAINS, or HAS.  

    See my community forum post here: https://community.smartsheet.com/discussion/comment/272642

    I propose a new formula that will extract the email address or email addresses as a text string that can then be used within formulas. For example:  

    =ContactEmail([Assigned To]@row) 

    The above would return "Travis@example.com" for a single contact column, or "Travis@example.com, Heather@example.com" for a column that allows multiple contacts. 

    Thanks,

    Travis

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!