Automation Workflow Q: Daily Email w/ Multiple Criteria

FGarvey

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!

Sam_Harwart

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?