JOIN(DISTINCT(COLLECT())) without losing Contact List column functionality

Kyle Chipman

Howdy - is there a formulaic way to gather all contact list column values alongside DISTINCT(COLLECT())? JOIN eliminates the contact list returns, converting them to strings, and trying something more convoluted via the Name + "<" + Email + ">" advice at the bottom of this post

https://community.smartsheet.com/discussion/67503/formula-to-combine-name-and-email-to-create-a-contact-row

requires a change of column format to initiate. Would like to run Current User reporting and build automations off a multi-value contact cell. I can provide context if helpful, just wasn't sure if I was overlooking something straightforward that's a simple answer first.

Thanks kindly!

Paul Newcome

Unfortunately there is no functionality to populate multiple entries into a contact list column while still maintaining the contact type functionality.

We can populate the data, but it will be as a text string.

Mark Cronk

Hi @Kyle Chapman ,

I don't think there's an easy way to do what you want. Contact list entries are challenging to manipulate. I suspect you'll end up using helper columns and possibly a helper sheet to compile what you're looking for. There are lots of smart people in the Community. Maybe someone has an idea for you.

Mark

Kyle Chipman

Bummer. I tried something along the lines of a helper sheet, running our MASTER - Employee table with auto-row ID's to split name and email, then converting in and around the formula pasted at the top. Again, it required that I change column format types away-from-then-back to Contact List to register the values as contacts, which will not work for my intended goal.

Appreciate the response!

Mark Cronk

Hi @Kyle Chapman ,

Place this formula in a Contact List column and see if it gives you what you're looking for:

=JOIN(COLLECT({insert contact list range}, CHAR(10))

It won't remove duplicates but might get you on the right path.

Mark

Paul Newcome

Unfortunately there is no functionality to populate multiple entries into a contact list column while still maintaining the contact type functionality.

We can populate the data, but it will be as a text string.

Kyle Chipman

Thanks Mark and Paul - appreciate the responses.

Paul Newcome

@Kyle Chipman Happy to help. 👍️

@Mark Cronk CHAR(10) is the delimiter for a multi-select DROPDOWN column. The delimiter in a multi-select CONTACT type column is actually ", " (comma space). If we are ok with converting to a text string, using the DISTINCT function should weed out duplicates. Another option for that (since it will be output in a text string anyway) would be to just JOIN/COLLECT and then throw in a SBSTITUTE to replace the ", " with CHAR(10) and drop it in a multi-select dropdown type column. Dropping a string into a multi-select dropdown column with the CHAR(10) delimiter treats each piece as an individual selection within the cell and the multi-select dropdown will automatically weed out the duplicates.

Joachim Mund

Hi @Paul Newcome

I tried to create a multi select contact list with =email1 + ", " + email2 for sending a workflow mail to both mails, but it will not work.

Any idea how to create such a multi contact list with severel mails for sending mails to all of them?

Thanks, Joachim

Paul Newcome

@Joachim Mund It is currently not possible to use a formula to enter multiple usable email addresses into a single contact cell.

I believe someone was able to accomplish this recently using the new Change Cell Value automation, and of course there is always manual selection, but we still can't use formulas to accomplish this.

Joachim Mund

Thank you @Paul Newcome