Validate an email address in a contact list

TravisK

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

Paul Newcome

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

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

TravisK

https://community.smartsheet.com/discussion/comment/272586#Comment_272586

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

@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

Hi @TravisK ,

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

Mark

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