How can I set a contact list column to display only the email and not the contact's name?

Jim Rood

I often have to use lookup formulas between sheets where one sheet is a system export of information loaded into Smartsheet. The system obviously generates an email displaying as text. In a prior support call I was told to convert the column to a contact list and that has served me well.

I've created a Message Tool for Employee Appreciation Day and the web form allows text entry of an email that may not be in the contact list. To validate, I created a helper column to look up this value against the list of 15,000 possible choices. It works great unless the sheet recognizes the email and converts it to only display the name.

Thought I would attempt a Dynamic View to add instead. While it will most likely find a match in the DV interface, I can't be 100% certain. I was hoping to find a way to control the display using a function or setting to guarantee a successful automated send.

Genevieve P.

Hi @Jim Rood

Yes, you're right, the two different ways of entering values, via a Form or via Dynamic View, do evaluate contact list columns in a different manner.

Contact lists in Dynamic View will automatically recognize contacts that are associated with your organization's Plan and are in the same account as you. Forms are published links relating to the sheet that will only show contacts if they are listed in the Column Properties as values.

From your initial post, it sounds like you want the contact to show up as an actual Smartsheet Contact, with the first and last name, so you're using the Helper Column with the lookup to create this if it doesn't happen automatically through the form submission, is that correct? Is the issue that when the contact comes in how it should (with the name as a Contact Value) in the first column, your formula is unable to find a Match?

If so, we could add an IFERROR statement to your helper column to return a text indicating that there is no match or that the lookup didn't need to happen. Then you could have two different conditions in your workflow to say that if the second column has this text (instead of a contact), send to the contact in the First column. Otherwise, send to the contact in the Second column.

Alternatively, you could set up both your form field and your Dynamic View field to be a regular Text/Number type of column and only use the reference column as the one with a Contact Type of value.

Let me know if I've still misunderstood... it would be helpful to see screen captures of the sheet and the two ways of inputting data in this instance, but please block out any sensitive information.

Thanks!

Genevieve

Genevieve P.

Hi @Jim Rood

I'm not sure I quite understand the issue; it would be helpful to see some screen captures of your form, sheet, formula, and lookup sheet if possible (but please block out sensitive data such as email addresses).

In the form, instead of allowing users to type in email addresses that aren't in the current contact list, could you create a second text field to input this email? This would ensure it always appears as text for your lookup formula. Would this work for you?

Cheers,

Genevieve

Jim Rood

The issue is ultimately that entering information into a sheet via a Form provides a different experience than does entering information via a Dynamic View. Each handles a contact list differently. The Form is the equivalent of a text box like you mentioned Genevieve. In the Dynamic View the contact list performs more like entering in the sheet itself with auto populate as you type being active which provides a level of validation. Despite completely typing a full email address in the Dynamic View, when it hits the sheet it may display as the contacts First Last name rather than the email address.

For example:

Entering the following email address: john.smith@myagency.com

may return: John Smith in the contact list column within the sheet.

The inability to dictate how the contact should display in the column creates the need for two distinct processes within a sheet to accommodate the variance in data entry from the Form vs the Dynamic View. It should not be necessary to sacrifice the data validation the Dynamic View provides in order to perform a secondary look up to ensure a match within a given data set of contacts.

I hope this better explains the predicament. Thanks for taking the time to understand this issue.

Genevieve P.

Hi @Jim Rood

Yes, you're right, the two different ways of entering values, via a Form or via Dynamic View, do evaluate contact list columns in a different manner.

Contact lists in Dynamic View will automatically recognize contacts that are associated with your organization's Plan and are in the same account as you. Forms are published links relating to the sheet that will only show contacts if they are listed in the Column Properties as values.

From your initial post, it sounds like you want the contact to show up as an actual Smartsheet Contact, with the first and last name, so you're using the Helper Column with the lookup to create this if it doesn't happen automatically through the form submission, is that correct? Is the issue that when the contact comes in how it should (with the name as a Contact Value) in the first column, your formula is unable to find a Match?

If so, we could add an IFERROR statement to your helper column to return a text indicating that there is no match or that the lookup didn't need to happen. Then you could have two different conditions in your workflow to say that if the second column has this text (instead of a contact), send to the contact in the First column. Otherwise, send to the contact in the Second column.

Alternatively, you could set up both your form field and your Dynamic View field to be a regular Text/Number type of column and only use the reference column as the one with a Contact Type of value.

Let me know if I've still misunderstood... it would be helpful to see screen captures of the sheet and the two ways of inputting data in this instance, but please block out any sensitive information.

Thanks!

Genevieve

Jim Rood

You nailed it Genevieve! Not only did you understand the pain point but you provided a viable solution. Thanks for your patience and perseverance in getting me to this point. I can't wait to go implement this.👍️

Genevieve P.

No problem! Let me know if you run into any issues or questions about this. 🙂