Best practice to notify/ask if someone's name is selected from a drop down?

SSParks

Greetings-

I'm trying to figure out if the way I am doing it is the most streamlined way. I have a column called assigned staff where someone can select a single individual or multiple staff members. Once selected they are emailed with info from the grid asking if they are free during form submitted time (ex meeting time). They receive a email where they can select Free or Not Free.

If a person's name is selected from column "Assigned Staff" then another column "Assigned Mike" (designated just for employee Mike) has a formula =IF(HAS([Assigned Staff]@row, "Mike"), "Mike") that fills that row in with Mike's Name. The column next to it is called "Mike Status" which populates the answer from the email Mike received with either Free or Not Free.

Basically what I am trying to accomplish is if Mike's name is selected from the drop-down Mike gets an email with the meeting time and Mike is able to respond free or not free and that information comes back to the grid to notify of his availability. I have a grid I am working on that would require two or three separate notifications of the staff which means I would have to have 4 to 6 columns designated just for Mike. Is there an easier way to gather this information from someone? Does this have to be setup for each individual staff member or is there a way to have a formula that covers everyone?

Georgie

Hi @SSParks,

I see that you’ve posted a similar question here and have had some good suggestions but are still looking for help. 

As per @Adam Murphy's suggestion on the other thread, I would also suggest changing the dropdown column to a contact list column that allows for multiple contacts. 

I tested using condition paths so that you could use one workflow, but this won’t work, since the workflow will follow the first path that has the criteria met, and doesn’t go back up to complete any additional paths (check out this article for more information). Therefore, you’ll need to have a workflow for each staff member, but you could have one update request workflow per staff member and one column per staff member, and then one column with a formula that summarises the availability for each row.

You can take the following steps to achieve this:

1. Add all staff members in My Smartsheet Contacts (if they’re not already set up as contacts in your Smartsheet account). 
2. Change the multi-select dropdown list column to a multi-select Contact List column and populate with the relevant contacts.
3. Create an availability dropdown column for every staff member, so you’ll have “Mike availability”, “Justin availability”, and so on, with the options as “Mike is free” and “Mike is not free”, changing the names for each column - this will help us with our summary availability column.
4. Create an update request for each member, as follows:
   1. Trigger: When rows are added or changed, when Assigned Staff changes
   2. Condition: Where Assigned Staff has any of [Staff Member 1]
   3. Action: Request an update. Send to specific people - [Staff Member 1]
      1. Customize the message to send specific fields and ensure you include the availability column for Staff Member 1, along with all other fields they need to see (eg Task name, Meeting time)
5. Duplicate the workflow and change the staff member in the condition and update request, and the availability field being sent. Repeat this for every staff member.
6. Create one more column to summarise everyone’s availability for each row, and use a formula similar to below and convert it to a column formula (you’ll need to ensure both ranges in the formula go from your first availability column to your last availability column). This will show you the availability of all assigned staff members for every row, with one staff member per line - if you'd prefer to have them all on the same line with a comma separating them, swap CHAR(10) for ", ".
   1. =JOIN(COLLECT(INDEX([Georgie Availability]@row:[Dorothee availability]@row, 1), [Georgie Availability]@row:[Dorothee availability]@row, @cell <> ""), CHAR(10)).
7. If desired, hide all other availability columns so that you’ll just see the summary availability column for each row, as in the example below:

Check out the following resources for more information:

• Automate processes with workflows
• Customize your alerts and requests
• JOIN Function
• COLLECT Function
• INDEX Function

Hope that helps!

Georgie