Data Mesh and Contacts

Dan Wilson

I've been having a bit of a struggle.

I have a Contacts column. If I enter an employee's email address, it auto-completes to the contact "card". However, if I use the contact column as the unique identifier between sheets for a data mesh, and the other sheet has email addresses entered via form (that do not automatically convert to a contact "card" since it was not entered manually into the sheet), then the mesh fails.

Ok. Fine. I can prevent my email from auto-completing to the contact "card" by entering the full email address, selecting all, cutting, and then pasting into the field. It's weird and inconvenient, but it works.

However, I also have a Data Shuttle that imports new employees into this sheet, and for some reason when the Data Shuttle imports or updates a row, the email address auto-completes into a contact "card", forcing me to regularly go in, look for email addresses that have switched from a straight address to a "card" and manually correct them so that my meshes with other sheets will work.

And to make all this even weirder, if I over over the straight address, it still shows as being part of the "card", so Smartsheet clearly equates the two in general with Shuttle, but not with Mesh.

Any help?

Jeff Reisman

@Dan Wilson

It's a bit of a workaround, but whenever I need to use an email address to match between sheets, or as a lookup value, etc., but I also need it for workflow and notifications, approvals, etc. - I use two columns on the same sheet. I always start with a Text/Number column "Email" where I stash the email address, and then I have a contact list column that I populate using a formula like =Email@row.

By keeping one column as text, the email address remains exactly as entered, so that it can be treated as text by formulas and apps like DataMesh. By using it to populate the Contact column I can still send notifications, etc.

A Contact List column is always going to try to resolve the values in it to contact cards, so you cannot rely on it to match email addresses in other sheets, or even other contact list columns. Even using a function like ISTEXT to determine if a contact list field is populated on a row doesn't work (I just had to update a formula I was using that function in to use <> "" instead!)

Dan Wilson

Thanks Jeff. I've tried that, but the text field keeps resolving as the name of the person rather than as the email address. It's a real pain!