Learn how to automatically configure emails from a multiple-choice dropdown list based on conversations in the Smartsheet forums.This will help you build a workflow that sends notification emails to faculty when a resident submits a case on their behalf.
first
Once a resident submits a case on behalf of a faculty member, the faculty member must be notified via email.There are sheets and forms for residents to fill out, and faculty names are listed in a multi-select dropdown menu in one of the columns.Residents can select one or more names.
If one teacher is selected, you can obtain the email address from a separate sheet and send a notification email.It is unclear how to notify if multiple faculty members are selected, or if "Clinical Preceptor or Fellow" or "Unlisted" is selected.
Answer summary
- Each item in a multiple choice dropdown list is separated by a newline character (CHAR(10)).
- Text functions such as FIND, LEFT, MID, RIGHT, and LEN can be used to extract each item (including "Clinical Preceptor or Fellow" and "Not Listed").
- If you have a table of names and email addresses on a separate sheet, you can get the email address corresponding to the extracted name.
Translation of correspondence
Oshaku ✭✭✭✭
12/06/23 12/07/23 Edit
Hello,
Once a resident has submitted a case on their behalf, the faculty member must be notified via email.
There are sheets and forms for residents to fill out.
Faculty names are listed in a multiple-select drop-down menu in either column, so residents must select one or more names.
I can see how to autofill an email from another sheet if only one name is selected, but I don't know how to send a notification if multiple names are selected or if "Clinical Instructor or Fellow" I don't know what to do if it is selected.or “Unlisted” is selected – there is a separate column where you can enter a name, but not an email, if it is not on the list. (This setup is necessary because clinic instructors change quickly)
- Faculty name 1
- Faculty name 2
- Faculty name 3
- Faculty name 4
- ------
- clinical instructor or fellow
- Not listed
Thank you!
answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello @OshaK
I believe the column with multiple names selected is a drop-down list that allows you to set multiple values in the cell.
In this case, each name or entry is separated by CHAR(10), so you can use TEXT functions such as FIND, LEFT, MID, RIGHT, and LEN to retrieve each name, such as clinical instructor, fellow, non-list, etc. Masu. (You can get the number of items in multiple drop-down lists with COUNTM.)
Then, if you have a table of names and email addresses, you can retrieve the email address that corresponds to the name.
Oshaku ✭✭✭✭
@jmyzk_cloudsmart_jp thank you. By "table" do you mean another sheet?Can you give me an example of the formula? Sorry, I'm not following.thank you!
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
@OshaK
Yes, I'm talking about a sheet with columns for name and email.
I modified the solution I created earlier to add a part that converts multiple dropdown lists to a single value and a part that retrieves emails from another sheet part. (The previous solution was to extract a single contact from multiple contacts, so this solution supports up to 1 lists.)
Example of calculation formula
For example, multiple drop-down lists have the format "item1+CHAR(10)+item2+CHAR(10)+item3", so use the FIND function to find the CHAR(10) position and retrieve each To do. Item using MID function. [c1] is CHAR(10)'s first position and [C2] is his second position.So the item looks like this:
MID([multiple names]@row, [c1]@row + 1, [c2]@row – [c1]@row)
Some of the actual formulas are:
Multiple names: multiple drop-down lists
m# =COUNTM([multiple names]@row)
c1 =IF([m#]@row > 1, FIND(CHAR(10), [multiple names]@row, [c0]@row + 1) )
n1 =IF([m#]@row = 1, [multiple names]@row, IF([c1]@row > [c0]@row, MID ([multiple names]@row, [c0]@row + 1, [c1]@row – [c0]@row), IF([c1]@row < [c0] @row, MID([names]@row, [c0]@row + 1, LEN([names]@row) – [c0]@row + 1)))
n2 =IF([c2]@row > [c1]@row, MID([multiple names]@row, [c1]@row + 1, [c2]@row – [c1]@row), IF([ c2]@row < [c1]@row, MID([names]@row, [c1]@row + 1, LEN([names]@row) – [c1]@row + 1)))
Published demo sheet
https://app.smartsheet.com/b/publish?EQBCT=f7cea94dcb094bffa0b42c189b8648c1
Detailed column information including formulas
https://app.smartsheet.com/b/publish?EQBCT=a5772f5e6edc477ea0ed827697407d7b
To request a copy of the solution
It's much easier to get a copy of the solution and explore it yourself.If you would like a copy, please request it using this form. (Select "Multi-value drop-down list to single value")
https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54
Oshaku ✭✭✭✭
@jmyzk_cloudsmart_jp Hey, thanks for your help.It will take some time to parse, but thank you for your cooperation.thank you.
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Happy to help! 😁
flag quotesinsightfulvote in favor1 great
Summary
- Extracts names separated by newline characters (CHAR(10)) from the option drop-down list.
- Get the email address corresponding to the extracted name from a table on a separate sheet.
- Send a notification email to the extracted email address.
By following the steps above, you will be able to properly send notification emails even if multiple faculty members are selected, or if "Clinical Preceptor or Fellow" or "Unlisted" is selected.
note
- This article is a translation of the conversation on the Smartsheet forum into Japanese and summarized for the WordPress blog.
- Actual sheet links and detailed mathematical formulas in the forum are not translated into Japanese and are posted in their original form.
- If you would like to reproduce detailed formulas or sheets, pleaseOriginal linkPlease refer to.
Summary of points
- To get multiple values from a multi-choice dropdown list, you need to extract each value separated by a newline character (CHAR(10)).
- To get the email addresses corresponding to the extracted values, you can use a table on a separate sheet.
You can use this method to automatically populate emails from multiple-choice drop-down lists and simplify your workflow.