Automation Workflow Q: Daily Email w/ Multiple Criteria
![FGarvey](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hello -
I need some help setting up an automated workflow to trigger a daily email based on multiple criteria. Details on the use case are below:
We have a sheet set up to assign daily tasks at an upcoming event. The majority of the tasks are assigned to individuals, which are indicated via the Staff Lead and Additional Staff fields in the screenshot below (both are set up as Contact List fields). However, there are tasks that will be assigned to everyone on staff, which are currently indicated via the "Full Staff" contact option selected in the same Contact List fields.
I would like an email to go out each morning to anyone assigned a task that day with the details on their assigned tasks. I've been able to get this to work for the tasks that are assigned to an individual contact (screenshot of the workflow below), but I also need the tasks assigned to the "Full Staff" contact to go out to everyone shared to the sheet. If a Contact is already getting an email with other tasks individually assigned to them, then the tasks assigned to "Full Staff" should be included in that email. If a Contact is not already getting an email because they don't have any tasks assigned to them individually, then they should just have the tasks assigned to "Full Staff" included in their email.
I'm sure I could get this to work by setting up a second workflow to only look at the "Full Staff" tasks, but I don't want two emails going out people - there will be plenty of days where a single person will have tasks assigned to them as an Individual Contact AND tasks assigned to the "Full Staff".
Note: I'm hoping to avoid having to assign the "Full Staff" tasks to each individual contact that makes up the full staff, as there is room for error with that approach and will make the sheet a little unwieldy. If that's the only way to make this work, then I can go down that path, but would prefer to make that a last resort.
Any ideas?
Thank you in advance!
Answers
-
Hi @FGarvey ,
There are a couple of items we need to solve for:
1 - how do we make sure we notify the right person or people
2 - how do we ensure the full staff list is easy to manage
Here's one way we could do this:Create Helper Sheet for the Full Staff List
1. Create a very simple sheet to hold the contact info of each person on the team in a single cell. Anytime you need to update the Full Staff roster, you'd come update this cell. It'll flow through to the main sheet.On the main sheet:
1. Create a couple of columns:
a. Full Staff List:=INDEX(COLLECT({TheStaffColumnFromTheHelperSheet}, {TheStaffColumnFromTheHelperSheet}, <>""), 1)
This pulls in the first cell from the Staff column on our helper sheet, which has the staff list we need. Make this a column formula. If you are new to cross-sheet references, let me know if I should provide more detail here.b. Notify:
=IF([Full Staff]@row = 1, [Full Staff List]@row, [Staff Lead]@row)
This checks to see if the task should be notifying the individual in Staff Lead or the Full Staff List. Make this a column formula.
2. Update your workflow to trigger on the due date and notify the contact(s) in the Notify column.
3. Hide the Full Staff List column so it doesn't make the sheet cluttered. I highlighted it in gray.
Thoughts?Thanks,
Sam
—
Want to chat about a Smartsheet problem you're facing?
Grab time on my calendar here:
https://calendly.com/sam-samharwart/30min -
Hi @Sam_Harwart -
Thanks for the response and apologies for my slightly delayed follow up. I just want to clarify a few things:
- The Helper sheet would only have two columns, each with a single cell, correct? The Primary Column (which needs to remain a text field and the Staff Column, which would be a Contact field
- For the Notify column in the Main sheet, can the formula be modified to check both the Staff Lead column and the Additional Staff column? Using myself as an example, there are known scenarios where within a single day I could be the Lead for several on-site activities, playing a support role for another activity, and we would also have an activity assigned to the Full Staff. In this scenario, each of those activities should be included in my email notification. I tried appending it myself, but got an "Incorrect Argument Set" error
Thanks again for your help - I'm feeling a lot closer to a solution!
Frannie -
Hi again @Sam_Harwart -
Just wanted to confirm that I ran a test overnight using your suggested solution and it did work - so thank you! My only outstanding question/concern is #2 from my response above:
2) For the Notify column in the Main sheet, can the formula be modified to check both the Staff Lead column and the Additional Staff column? Using myself as an example, there are known scenarios where within a single day I could be the Lead for several on-site activities, playing a support role for another activity, and we would also have an activity assigned to the Full Staff. In this scenario, each of those activities should be included in my email notification. I tried appending it myself, but got an "Incorrect Argument Set" error
I'll do some digging around to try to figure out how to fix that formula to also include the "Additional Staff" column, but if you have any tips, I would appreciate them.
Thanks again!
Frannie -
Awesome!
Is the Staff Lead someone who is already included in the full staff list? Because that might take care of it on its own. Or you could have the Staff Lead notified every time and add in the Full Staff if the box is checked.Thanks,
Sam
—
Want to chat about a Smartsheet problem you're facing?
Grab time on my calendar here:
https://calendly.com/sam-samharwart/30min
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives