Populating Contact List Dropdown

Paul Newcome

@Jacqueline Ennis It will populate a text string that contains the list of email addresses, but it will not be usable for automations or "Current User" filters.

Office of the Medical Director

@Paul Newcome or @jmyzk_cloudsmart_jp

I currently am doing something similar with multiple names selected in the drop-down, and I want to email the report to those listed in the multiple drop-down.

Since we have over 500+ contacts, I create a contacts list sheet and used the formula to pull the email address into to send the email; however, if I have more than 1 name in the list, I receive an error msg.

The Primary Apparatus Crew is where the crew names are selected from a drop down. The Primary Contact List has the following formula:

=INDEX(COLLECT({Field Crew Contacts - Email}, {Field Crew Contacts - Agency}, @cell = Agency@row, {Field Crew Contacts - Name}, @cell = [Primary Apparatus Crew]@row), 1)

As you can see, the formula is looking at the primary apparatus crew name and agency (not shown), references the contacts list sheet, and pulls the email address into the Primary Contact List. My issue is that I can get this to work with 1 contact, but is there a way to get it to work with multiple contacts in the primary apparatus crew list?

I have been working on this for a while and haven't found any workarounds. Any assistance would be greatly appreciated!!

Thanks

Adrienne

Paul Newcome

@Office of the Medical Director There is currently no efficient way to use a formula to output multiple usable contacts into a single cell.

Office of the Medical Director

@Paul Newcome Thank you for the insight. Do you know of any other way for me to handle this? Is this something that Data Shuttle or any other add-on that could do this?

Thanks,

Adrienne

Paul Newcome

@Office of the Medical Director MAYBE the API. I haven't tested the API for this functionality though, so I am not sure if it will work or not.

jmyzk_cloudsmart_jp

@Office of the Medical Director @Paul Newcome

Smartsheet sorts textualized dropdown lists alphabetically. Hence, fetching the first item by text function yields the alphabetical first, not the top item seen in dropdowns.

https://app.smartsheet.com/b/publish?EQBCT=13aa3e678c9847ef94baaf3a22b734db

Example Formula: =LEFT(Crew@row, FIND(CHAR(10), Crew@row) - 1) (Change CHAR(10) to ", " if Contact List}

To get the displayed first item in dropdowns, use the Smartsheet API, which reflects the display order, like "Merino, Johan, Hanson, Joshua."

https://app.smartsheet.com/b/publish?EQBCT=78fa3577085a460f8a7b25ccc39e20e1

Check the demo dashboard. The 'Crew' column is editable. Modify the selection and click "Link to Update the Sheet" to observe API updates to "First List by API."

Note: Smartsheet displays dropdown lists based on dropdown column list values, not chosen order. If order matters, designate a primary contact column instead of relying on list positioning.

Example Code:

https://gist.github.com/jmyzk/894e2cfac205c5a5be5d89bbba06c8f1

Paul Newcome

@jmyzk_cloudsmart_jp Is this able to output multiple usable contacts in a single cell based on the selections made in a multi-select dropdown?

A formula can be used to grab the first one. Its populating all of the as usable contacts for automations that we are discussing potential API use for.

jmyzk_cloudsmart_jp

@Paul Newcome

As a trial, we have created a service to update your Contact List from your Email List.

Please try it out by filling out the form below.

BTW, I am a kidney transplant patient with underlying Covit19 risk, so unfortunately I will not be able to participate in this year's Engage.

https://app.smartsheet.com/b/form/01aaaa0f179547b3ad230f4421fa293d

https://www.youtube.com/watch?v=IkX2EqTFwlY