Creating an automation workflow with multiple conditions

sanktjohan

I'm trying to create an automation workflow to send out routine email alerts to notify the team if a task associated with a specific Batch is due soon or past due, and I can't seem to address this in one or two alert Actions due to needing multiple AND/OR conditions for the various date fields.

Here is my scenario:

Our Smartsheet tracks Tasks A, B, C, and D deadlines for each Batch using a 'Target Date' column and then we fill out the 'Actual Date' column once the task is complete. One issue is that the 'Target Date' for each task may not always be populated for the entire sheet, and when I used additional AND/OR conditions to reflect this, my workflow got very messy. I ended up making different Actions for each Task in the same workflow (i.e. IF Task A 'Target Date' is in the past AND 'Actual Date' is blank → Alert Task Owner, new condition path: IF Task B 'Target Date' is in the past AND 'Actual Date' is blank → Alert Task Owner, and then I made a mirroring automation workflow for 'Due Soon' notifications). Unfortunately, this is leading to a separate email going out for each individual task. Is it possible to have one automation workflow that includes all tasks and will send out one or two different alerts depending on if a task is Due Soon or Past Due?

Workflow Requirements:

• For Tasks A, B, C, and D
  • IF 'Target Date' is in the next 7 days AND 'Actual Date' is blank → Email 'Due Soon' alert to Task Owner
  • IF 'Target Date' is in the past AND 'Actual Date' is blank → Email 'Past Due' alert to Task Owner
  • One email alert that displays all of the tasks that are Due Soon, and one email alert that displays all of the tasks that are Past Due. I would also be interested if one email notification can cover both scenarios.

This is my current workflow:

Thanks for the help!

kowal

Hi Johan,

So you tested this setup and no notifications are received?

The setup looks legit so I wanted to make sure you tested it and no emails are received.

Also did you change the permission settings to unrestricted (just in case)?

sanktjohan

https://community.smartsheet.com/discussion/comment/456263#Comment_456263

This workflow does send out notifications. However, if 3 of the 4 tasks are overdue, it will send 3 separate emails (1 for each task). I would like 1 email notification sent that includes any/all relevant tasks. I couldn't figure out how to group the conditions in a way to do this.

kowal

I think the problem might be the dynamic message you are sending with each notification.

I can see that sometimes you send 5 fields and mostly 4 fields. If you want to receive everything as one email each message needs to be the same no dynamic data inside like {{}} etc. have you tried that?

maybe make a copy of this workflow - remove subject and body message. set up to send all the fields… and see how many emails you will get.

Toufong Vang

Hi, @sanktjohan , there are 4 tasks so you have 24 possibilities that a combination of any or all tasks are incomplete (4 factorial, 4!). To avoid 24 separate conditions (1 for each combination), it's easier to account for each task separately, and then combine the message about the status of the tasks, JOIN(COLLECT()).

In this approach, use 5 helper columns:

Task1_Msg
IF( AND([Task1 Target Date]@row<Today(), [Task1 Performed]@row=""), "Task 1 needs to be completed.", "")

Task2_Msg
IF( AND([Task2 Completion Date]@row<Today(), [Task2 Actual Completion Date]@row=""), "Task 2 needs to be completed.", "")

Task3_Msg
IF( AND([Task3 Completion Date]@row<Today(), [Task3 Actual Completion Date]@row=""), "Task 3 needs to be completed.", "")

Task4_Msg
IF( AND([Task4 Completion Date]@row<Today(), [Task4 Actual Completion Date]@row=""), "Task 4 needs to be completed.", "")

Email_Msg
JOIN(COLLECT([Task1_Msg]@row:[Task4_Msg]@row, [Task1_Msg]@row:[Task4_Msg]@row, <>""), CHAR(10))

CHAR(10) is a new-line character. This will ensure that the messages are on separate lines.

Configure your workflow automation to send the alert and the contents of {{Email_Msg}} when Email_Msg is not blank.

Hope this helped!

sanktjohan

https://community.smartsheet.com/discussion/comment/456321#Comment_456321

This worked perfectly - thank you!!