Populating Contact List Dropdown
I am trying to populate dropdown choices for a contact list column via cut/paste or datashuttle. I would like the choices to have the real name of the contact and e-mail address. When I manually add it, if the contact is in my contacts, it adds it the way it should. Cutting and pasting an e-mail address (that is not in my contacts) will add the e-mail address and when I later try to edit it, I am unable to edit the name. I tried the format "FirstName Lastname <user@domain.com>" but it puts the whole thing in the Name field and e-mail address is blank. Trying to do this via datashuttle produces the same results. I see a previous suggestion of importing to "My Contacts" and adding them one at a time. That is not practical since the list is large. Any help or pointers you can provide is much appreciated.
As an aside, I am new to Smartsheet and all I can say is it is frustrating that basic functionality (dynamic dropdown, folder level permissions are just a couple of examples) is missing for apparently years and has not been addressed. Suggestion of using the API or other workarounds (for years) is not really addressing the problem. Had to vent..
Thanks!
Answers
-
Hi @saratvemuri
You can use the following method to populate dropdown choices for a contact list column.
A Contact cell can be created by
- pasting the format "FirstName Lastname <user@domain.com>" into a Text/Number column,
- saving it,
- and then changing it to Contact List.
Contact has the format of "name < email>," for example, "Jone Doe <jone.doe@cloudsmart.llc>."
Example formula: =[Full Name]@row + " <" + Email@row + ">"
If you have an Employee Directory, you can use the function to create a "name < email>" format based on the directory and then create a master of the company's contacts by changing the column attributes from Text to Contact, as described above.
It is also a good idea to group the contact master by Department, Job Title, etc., in the report for ease of use.
For example, to create a Contact List for a project, follow these steps:
- Copy the formats you want to create from the Contact Master or its classified report. Note that the limit for preferred contacts to display is 50. So, you can only create Contact List with up to 50 Contact Candidates.
- Paste it as Text into the desired column.
- Next, change the column attribute to Contact List. This procedure will create contacts and also a contact list.
Since the contact list has a limit of 50, if you need more than 50 candidates, create a dropdown list of names (or emails) and use INDEX MATCH to get the contact cell from the contact master.
Example formula: =INDEX({Contact}, MATCH([Name List]@row, {Name}, 0), 1)
{Contact}: Master Contact Column Range
{Name}: Master Name Column Range
The following dashboard demonstrates the name to contact by index match.
You may get, using Data Shuttle, the Employee Directory from the company's HR database, etc.; if a premium application is unavailable, it can be maintained by importing from CSV or Excel.
When there is a personnel change, etc., and the master needs to be updated, if you do the above process once, which is to first bring the Employee Directory data up to date, create the name < email> format with the function, convert the Contact column to Text and paste the formats, and change the column attributes back to the Contact List, You can update the contact master.
-
Thanks for the response. However, currently, converting Text to Contact List loses the "FirstName Lastname" part (which the important one I am trying to preserve) and only keeps the e-mail address. Perhaps this is a new bug? It is not very useful to have to have a contact list with sometimes cryptic e-mail addresses.
-
Hi @saratvemuri
I missed the "I would like the choices to have the real name of the contact and e-mail address. " part.
Register Users in User Management
I use email addresses for my contact list to avoid the same name issue.
However, you want your use "FirstName Lastname" as the display value of the contact list. In that case, you can register it in User Management so that when you convert "FirstName Lastname <user@domain.com>" from Text to Contact List, it will be displayed as " FirstName Lastname" when converted from Text to Contact List.
*User Management" is managed by the system administrator.
System administrators manage users at the Administration Center.
Select User Management from the left navigation menu and select the Add User button. Enter the user's last name and email in the right panel window. The system administrator can also do a bulk import using a CSV file.
-
How many contacts are we talking? If you are able to create a reference table that has names in one column and emails in another column, you can use a regular dropdown for the names and then use a formula with cross sheet references or Data Mesh to pull the email address in.
-
We ended entering contacts manually one by one (after they are imported to my contacts). That is the only thing that works. Another Smartsheet Fail!
-
@saratvemuri There are other options depending on scale.
-
@saratvemuri I do not have a solution for you, but I do have encouragement. I am not new to Smartsheet but I am not a Power User. There have been many occasions that my frustration with the application hits a peak... much in the way that yours has. Especially when I am trying to do something that seems straightforward. I can only say 'hang in there'. Three of the best things about Smartsheet is 1) this forum - that has dozens and dozens of gracious and expert users who willingly offer solutions and suggestions as you have seen. 2) your account management team - burn up their phone and email... they will advocate for you with the development and support team and ensure that your voice is heard 3) even though Smartsheet seemingly misses the point on some basic functionality, the things that you can accomplish are pretty amazing.
I am not a Smartsheet employee or paid to say any of this. I am just a little nobody trying to make the best use of my company's money. I hope that this helps and wish you the best.
Jacqui
-
@jmyzk_cloudsmart_jp I am trying to replicate the Index function that you described above. I think I get it but have a couple of questions. First is the 'contact master a separate sheet? a report? or columns in the same sheet that I am using? Next if it is a separate sheet. where in the formula am I referencing the name of of the contact master sheet. For context, I have a list of 100 managers that I need to have in a contact list column in order to fire an email in automated workflow. Following your description below, I created a dropdown list of the managers' names in my primary sheet. If I understand your instructions above, I created another sheet that contains, the managers' names and their email addresses which I, if I understand correctly should be referenced in an index function on my primary sheet. Am I missing something?
Thanks as always
-
The Contact Master is a separate sheet.
The formulas that reference the contact list on the Contact Master sheet are on a sheet that references the Master sheet.
This is the column formula for the contact list.
- =INDEX({Contact}, MATCH([Name List]@row, {Name}, 0), 1)
Below is the image of {Contact} reference.
FYI, the allowed number of preferred contacts in a contact list column is 50.
https://community.smartsheet.com/discussion/67913/why-does-the-contact-list-field-have-a-limit
-
@jmyzk_cloudsmart_jp That particular limit is referring to the column PROPERTIES where you can list contacts similar to dropdown selections.
The only limitations to how many contacts can actually be listed in the cells in the column are the standard sheet limitations (cells / rows).
-
@jmyzk_cloudsmart_jp your solution for the contact list that has more than 50 names worked marvelously. I am not sure how I would have ever come up with that. Thanks again. I do have a quick follow-up. If I have an automated workflow that fires from contact list column, will the workflow read the contents of the column (email address? Or is it reading the formula? I can't seem to get the workflow to work as it does for other contact columns. Just wondering.
Jacqui
-
@Jacqueline Ennis The workflow can read an email address that is output by a formula. There are a number of variables that can affect whether or not it will send including automation settings, potentially sharing permissions, and whether or not the output string/email address is a valid email address.
-
Thanks. Since writing my question to you, I found out that Smartsheet is having some type of intermittent issue with sending notifications from the workflows. LOL I thought that I lost my mind since the workflow worked last week. Thanks again for the instructions on how to set up the reference sheet. That is going to have many more uses for me. Have a good day.
-
-
@Paul Newcome or @jmyzk_cloudsmart_jp - in the solution recommended above (which by the way works marvelously!), if I allow multiple sections from the 'contact' column will I get multiple emails from the formula column?
