Match Contact column list to Email Address

ker9 · 01/18/22

Has anyone found a practical solution to match a Contact column to an email address?

It can't be done in DataMesh and while I've done some searching on the board and help files, I haven't found a method to do this that doesn't require a lot of manual intervention.

The problem with Smartsheet Contacts is that people can change their name to be anything they want. Email addresses, however, are static, which allows matching across systems.

We are using Dynamic View to add new data to a sheet, which allows accurately capturing the contact information much better than a form. We want to DataMesh by email address to insert other related information.

TIA.

Genevieve P. · 01/20/22

Hi @ker9

There currently isn't a way to extract the email address from a Contact in a Contact column. (Please submit your feedback and request to the Product team, here!)

The way I would do this is to set up a Reference Sheet with each contact listed down one column and the associated email as text in the column next to it. Then you can use this table to pull into your sheet the email address associated with that contact, either using DataMesh or cross-sheet formulas.

However, if your users are adding new rows to the sheet, you could set up a Created By system column in your sheet. This will only capture the email address of the user who created that specific row. See: Use a System Column to Automatically Add Information to a Row

Cheers,

Genevieve

ker9 · 01/20/22

Thank you for the response. I have done that (requested Enhancement), I believe more than once. It shouldn't be this difficult.

Please setup a method to pull out the email address or possibly allow an ID to be added (to the user profile) that matches an ID from our system that a user cannot modify.

I'm trying to avoid setting up yet another sheet that must be updated regularly/manually.

ker9 · 01/31/22

I found that I can use automation to copy rows to a different sheet and then use formulas to get the email address of the contact.

Setup a new incoming (“Lookup”) sheet with the SAME COLUMN NAME as the outgoing sheet but format it as TEXT instead of Contact. Use automation to copy rows from one sheet to the Lookup sheet.

The Lookup sheet TEXT column receives the contact name as the name and email address like this:

Joe Smith <jsmith@someemail.com>

Use formulas in additional columns to pull out the pieces. These can be setup as Contact columns.

Formula for Contact Name:

=MID(Contact@row, 1, FIND("<", Contact@row) - 2)

Results: Joe Smith

Formula for Email:

=MID(Contact@row, FIND("<", Contact@row) + 1, FIND(">", Contact@row) - FIND("<", Contact@row) - 1)

Results: jsmith@someemail.com

(“Contact” in the formulas above is the name of the column containing the data.)

Note: If you are not familiar with the copy rows process, all columns from the Copy From sheet will be copied to the Copy To sheet. The columns do not need to be created manually. Create one column (not Primary column) that will be formatted as Text with the exact same name as the Copy From sheet.

It's quite a bit of work and formulas to get to this point and I believe Smartsheet could make it easier by providing the ability to get to the email address and/or providing an option to add an Employee ID to user profiles (that cannot be changed by a user). This would be particularly helpful to Enterprise users. (Yes, I have requested this, many times.)

An ID would help eliminate problems that stem from marital status changes (that change email addresses) and other issues.

Genevieve P. · 02/01/22

Hi @ker9

Thank you for this feedback and for explaining your current solution! This is helpful to see.

Cheers,

Genevieve

ker9 · 02/08/22

DataMesh can also be used on the same sheet (no additional lookup sheet).

I created a TEXT column on the same sheet and used DataMesh to copy the Contact column to the Text column. (I used the source column for both lookup values.)

The results look like this: jsmith@someemail.com <jsmith@somemail.com>

Create an additional column for the formula to pull out the email (since I had this formula I reused it):

=MID(Contact@row, FIND("<", Contact@row) + 1, FIND(">", Contact@row) - FIND("<", Contact@row) - 1)

Results: jsmith@someemail.com

(“Contact” in the formula above is the name of the column containing the TEXT data from DataMesh.)

Match Contact column list to Email Address

Answers

Help Article Resources

Categories