Collect List of Matching Emails from Selected Items in Dropdown
I have a column that is a dropdown of Names, multiple select enabled. I want to have a second column that another dropdown of the emails associated with those names.
I have this:
And would like to achieve this, in a separate sheet:
The multi-name selection comes from a form, so this needs to be done with some formula, manual editing of the solution sheet is not possible.
Best Answers
-
HI ALL - Please check the latest comment on this thread - this isn't the Best Answer, it's easier using Data Mesh on a single sheet as outlined in the last comment from me.. But if you only have Data Shuttle here's the solution:
Hi sure, it's slightly tricky but it works. The issue is that you cannot put together email addresses directly into a multi-contact column with a separator, it doesn't work. I wish it did! But we can leverage Data Shuttle to export/import a list of email addresses into a Contact column, which does work as it converts the data on import.
With everything setup, here's how it works:
- Someone picks a list of names on a form and submits it. Smartsheet creates a new row with the multi-select names in the Names column.
- The Email Addresses formula pulls in a list of email addresses from your lookup sheet based on the names selected and separates them with a semicolon.
- Data Shuttle runs hourly/daily (whatever you set) and exports the submission sheet to a CSV/XLSX file that gets attached to the submission sheet.
- Data Shuttle then triggers to import from that attachment. It imports the list of email addresses into the Emails column, converting those addresses to contacts along the way.
SETUP
1.On your form sheet, the one where people select a multi-selection of names, add a Text/Number (not a Contact) column to collect the emails called Email Addresses.
2. In the Email Addresses column, add the formula shown and then right click it and choose Convert to Column Formula
=JOIN(COLLECT({Email}, {Name}, HAS(Names@row, @cell)), ";")- The {Email} reference points to the Email column on your lookup sheet, use the Reference Another Sheet link when typing out the formula to set that up.
- The {Name} reference points to the Name column on your lookup sheet
3. You should now be able to select names in the form and have the email addresses populate with ; separator.
4. You will also need a unique identifier on each row of your "form sheet". Add an Autonumber column if you don't already have one.
5. Create a Data Shuttle Offload workflow. Source is your "form sheet". Destination is a CSV or XLSX (doesn't matter) attached to the same sheet. Export the Autonumber column and the Email Addresses column. Include the checkbox for headers. You'll want to schedule this to run hourly or daily depending on how often the form gets submitted. Note that there will be a delay inherent in this process, between form submission and Data Shuttle scheduled run. There's not a way to trigger Data Shuttle offloads based on conditions.
6. Create a Data Shuttle Upload workflow. Source is Attachment from the same sheet. Set to "Most recent". If you ran step 5 it should find an attachment on the sheet. Check the "This file has column headers" box. Target is the same sheet again. Set to Merge, with Update selected only. In the Mapping, Unique Identifier is your Autonumber column. Email Addresses should already show as mapped to the Email Addresses column in Smartsheet. You can remove that mapping by changing Source to "Not mapped". Add Email Addresses to map to the Emails column. Finally, select "Run on attachment". This will trigger the update each time the offload workflow runs.
-
Aaaaand I just had my fellow overachiever Sam Mueller let me know it's even easier with Data Mesh.
Instead of intermediary sheet, you only need a single Data Mesh workflow that uses the submissions sheet as both source and destination. Then simply map unique id as the Autonumber column, and map the Email Addresses column to the Emails column, set to Update Immediately, and done.
Answers
-
This isn't possible in the core app. There's very little functionality with multiple selection contact fields. You cannot "piece together" a multiple selection contact column value like this with any delimiters.
If you want to look up this data into a multiple-contact column, you can index/match the results into your sheet, but your lookup would need to have every possible combination of multiple contact choices to find. You cannot JOIN(COLLECT this together.
If you have addons, you can do some workarounds to accomplish this.
With Data Shuttle, if you build a list of email addresses separated by a semicolon or comma, then export that list to CSV/XLSX, then re-import that collection of emails separated by semicolon/comma, Data Shuttle will convert it to a multiple-selection contact column if you import into one.
The other option is to use Bridge and build a workflow that reads the sheet when new submissions are entered, reads the names, and does a little Javascript to loop through the names and put together the email contacts associated to those names from the lookup sheet.
-
@Brian_Richardson I see, thank you for the clarification.
I do have Data Shuttle. Would you mind elaborating a little bit on your proposed solution with that add-on? -
HI ALL - Please check the latest comment on this thread - this isn't the Best Answer, it's easier using Data Mesh on a single sheet as outlined in the last comment from me.. But if you only have Data Shuttle here's the solution:
Hi sure, it's slightly tricky but it works. The issue is that you cannot put together email addresses directly into a multi-contact column with a separator, it doesn't work. I wish it did! But we can leverage Data Shuttle to export/import a list of email addresses into a Contact column, which does work as it converts the data on import.
With everything setup, here's how it works:
- Someone picks a list of names on a form and submits it. Smartsheet creates a new row with the multi-select names in the Names column.
- The Email Addresses formula pulls in a list of email addresses from your lookup sheet based on the names selected and separates them with a semicolon.
- Data Shuttle runs hourly/daily (whatever you set) and exports the submission sheet to a CSV/XLSX file that gets attached to the submission sheet.
- Data Shuttle then triggers to import from that attachment. It imports the list of email addresses into the Emails column, converting those addresses to contacts along the way.
SETUP
1.On your form sheet, the one where people select a multi-selection of names, add a Text/Number (not a Contact) column to collect the emails called Email Addresses.
2. In the Email Addresses column, add the formula shown and then right click it and choose Convert to Column Formula
=JOIN(COLLECT({Email}, {Name}, HAS(Names@row, @cell)), ";")- The {Email} reference points to the Email column on your lookup sheet, use the Reference Another Sheet link when typing out the formula to set that up.
- The {Name} reference points to the Name column on your lookup sheet
3. You should now be able to select names in the form and have the email addresses populate with ; separator.
4. You will also need a unique identifier on each row of your "form sheet". Add an Autonumber column if you don't already have one.
5. Create a Data Shuttle Offload workflow. Source is your "form sheet". Destination is a CSV or XLSX (doesn't matter) attached to the same sheet. Export the Autonumber column and the Email Addresses column. Include the checkbox for headers. You'll want to schedule this to run hourly or daily depending on how often the form gets submitted. Note that there will be a delay inherent in this process, between form submission and Data Shuttle scheduled run. There's not a way to trigger Data Shuttle offloads based on conditions.
6. Create a Data Shuttle Upload workflow. Source is Attachment from the same sheet. Set to "Most recent". If you ran step 5 it should find an attachment on the sheet. Check the "This file has column headers" box. Target is the same sheet again. Set to Merge, with Update selected only. In the Mapping, Unique Identifier is your Autonumber column. Email Addresses should already show as mapped to the Email Addresses column in Smartsheet. You can remove that mapping by changing Source to "Not mapped". Add Email Addresses to map to the Emails column. Finally, select "Run on attachment". This will trigger the update each time the offload workflow runs.
-
@Brian_Richardson So helpful! Thank you!
-
Old answer - see the single-sheet Data Mesh solution below instead.
Actually - I just was testing and found that Data Mesh will do this too, and it does it in a triggered rather than scheduled manner. If you have Data Mesh then I would skip Data Shuttle and do this instead:
- Still setup an autonumber and a Email Addresses column in your submissions sheet, using the formula that I provided
- Create a third intermediary sheet for Data Mesh to use. Call it maybe "Email Data Mesh". Doesn't matter the name. On that sheet create a text/number column called Auto (or the same name as your existing autonumber column). It's important that this is not an actual autonumber column but instead is a plain ol' text column. Create a second text/number column called Email Addresses. Set it aside somewhere where people won't mess with it.
- Setup two Data Mesh workflows:
- Workflow 1: Source is the submission sheet, destination is the new Email Data Mesh sheet. Unique identifier is Autonumber column on both sheets. Map Email Addresses from source to Email Addresses to destination. Set the workflow to Copy and Add and Update Immediately.
- Workflow 2: Source is the Email Data Mesh sheet, destination is the submission sheet. Unique identifier is the Autonumber column on both sheets. Map Email Addresses from source to Emails column in destination (the actual contact column). Set the workflow to Copy (not copy and add, not cell link) and Update Immediately.
Now when someone submits a new form, the combined list of email addresses will sync over to the Email Data Mesh sheet on a new row, then immediately sync back to the submission sheet into the Contacts column as actual contacts!
-
Aaaaand I just had my fellow overachiever Sam Mueller let me know it's even easier with Data Mesh.
Instead of intermediary sheet, you only need a single Data Mesh workflow that uses the submissions sheet as both source and destination. Then simply map unique id as the Autonumber column, and map the Email Addresses column to the Emails column, set to Update Immediately, and done.
-
-
@Brian_Richardson You should put these instructions together into a post of its own and see about getting it listed as a "best practice". Sure it would be great if we could do this in the core app, but at least for now having these as options is awesome!
-
@Paul Newcome great idea! Posted:
Help Article Resources
Categories
Check out the Formula Handbook template!
