Cannot get Automations to work the way I want

lalyse

I'm trying to set up an automation for a task tracking sheet. I would like to send a weekly email notifying team members of their follow-up items for the week, and if they do not have any tasks, then they would receive an email stating as such. I have tried using the "otherwise" function, creating a second condition path, adding a condition below the first condition, etc. No matter what, I can't seem to get it to work how I intend. Either both emails go out, or neither go out. I have been able to get it to work where only the "no follow-up items" email goes out, but it still sends both when the person has something assigned to them. At this point, I can't even remember what I have and haven't tried. Does anyone have an idea for how to make this work? Appreciate any help you can offer.

jmyzk_cloudsmart_jp

Hi @lalyse

To make the automation condition easy, I added the following helper columns;

[This Week] =
  WEEKNUMBER(Start@row ) = WEEKNUMBER(TODAY())

[All This Week Task Complete] =
AND(
 [This Week]@row , 
 COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row , [This Week]:[This Week], true) = 
 COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row , [This Week]:[This Week], true, Status:Status, "Complete")
)

https://app.smartsheet.com/b/publish?EQBCT=8ae126bc216b4ac4969f0cad25585aa9

The check-box column [This week] indicates whether the row is for this week.

The check-box column [All This Week Task Complete] checks if the items are this week, and all the tasks are complete, by comparing the number of such tasks and the number of completed tasks. If the counts are the same, all the tasks are complete.

Using those helper columns, I created a time-triggered workflow automation for this week's task. The automation branches based on whether all assigned tasks are complete. If so, it sends notifications; otherwise, it sends an update request for the incomplete tasks.

As shown in the 7th row, jmiyazak's task is all complete, so a notification is sent.

On the other side, the demo receives an update request for the 9th row's task, as it is in progress.

A bit of a digression...

In this example, jmiyzaki has only one task in the week, but if he has multiple completed tasks, he may get multiple notifications.

In the updated sheet, I've added a helper column to track multiple completed tasks.

[First Complete Task]
=MIN(COLLECT([Row ID]:[Row ID], [All This Week Task Complete]:[All This Week Task Complete], true)) 
=[Row ID]@row

With this formula, only the earliest task's row will be sent.

https://app.smartsheet.com/b/publish?EQBCT=a1388dad3ace42518b023cb0e87393ce

I updated the workflow for the "All This Week Task Complete: true" version as follows.

The notification, however, makes sense for the original one that sends multiple rows.

As the image below shows, the original version lists all the tasks that are complete and makes sense.

On the other hand, sending only the first completed items does not provide the whole picture.

So, I checked the help article and found the following.

When you manually select a date, the workflow triggers all the rows that meet the conditions you set simultaneously. For example, you can use an alert to remind you to take action on all rows that don't have an Assignee by a specific date.

https://help.smartsheet.com/articles/2479236-trigger-blocks-when-your-workflow-is-executed

Although the explanation is not entirely clear, my test indicates that the automation will send a message with multiple rows.

My misunderstanding was that the automation would send multiple messages, but it would send a single message with multiple rows.

So, we do not have to use the [First Complete Task]. As I found, Smartsheet time-triggered automation takes care of this.