Preferred Contact List dropdown best practice?

J. Craig Williams
J. Craig Williams ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

Is there an easy way to populate the preferred dropdown choices in a Contact List?

If I had 50 (the limit), mentioned here

https://help.smartsheet.com/articles/504619-column-types#contactlist

do I need to select them one by one?

No import?

I tried commas and semi-colons in a list, but that didn't work.

I tried populating the column and then switching back and forth between Contact List type and Text/Number type for the column. That didn't work either (it does for the Dropdown List type).

I know I have done this before, but either the lists were smaller, I had to do it on only one or a few sheets (not a large set of sheets, a large number of contacts, and each sheet wanting this feature added), or I have forgotten the easy way. Or maybe I am doing something wrong.

If there is no easy way, I'll write an enhancement request.

Craig

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/01/18

    Well... This was disappointing. I found a workaround that is very unsatisfying, but it works. Post all of the emails into a text number column, make it a dropdown, then make it a contact column. You can't associate the names with them if you do it this way though. Or it it works better for you just post emails into a dropdown then convert it to a contact column

  • Hi @J. Craig Williams ,

    Did you end up submitting an enhancement request?

    I ended up with a solution as a byproduct of trying to automate updating dropdowns list and contact lists in multiple sheets every time a new staff member joins or new project starts. It does involve a PowerShell script to invoke the Smartsheet API, so not the best answer for the less technical user, but a boon for someone like myself who has lots of different sheets with dropdowns that need to be kept in sync. I set up the master list for the dropdown as entries in a column in a dedicated sheet (or you could use any existing column of type text or contact list).

    The script needs an API key for your account. It includes an array with details for each dropdown to update, namely the IDs for the source sheet and column, optionally a filter to apply, and then the sheet and column that needs the dropdown updated. If the source column is of type contact list, it will make the destination column a contact list with an entry for each of the contacts in the column, otherwise it will make it a single select dropdown (I do not use multi-select, but if required it would be an easy mod to make). I should really sort and remove duplicates from the source column in code before setting up the contact list, but Smartsheet seems to do this anyway, so I did not get around to implementing this (sorting an array of custom objects in PowerShell is something I would need to Google, my initial attempts failed🙁).

    The ID of a Smartsheet is easy to obtain from the gui, just look at the properties of the sheet. Not so easy to find the IDs for columns and filters, so I wrote a second script that given a sheet ID will list the IDs of all the sheet's columns and filters.

    Here are the scripts, I had to add a txt extension to get them to upload.

    Once the UpdateSmartsheetLookups.ps1 script has been configured, it can be re-run whenever required, or as a scheduled task (or perhaps triggered by a workflow through Zapier or PowerAutomate).

    If anyone gets to use the scripts, please let me know how you go 😊

    Note that I have only just finished testing the script, it has not been running in production for a long time, and does not handle errors elegantly, but it does do the job and will not modify or delete any data, just the column type and dropdown values.

  • Wow, thanks to L@123 you just saved me a bunch of time, this worked! Making them text fields and then changing the type did the trick. Now if they only did something like this for Group Management... Truly thanks!