Event from Virtual Column not firing

[Greg_Huston]

Hi,

I'm having difficulty with an automation. I have a bot that has the following components

1. Event - monitor for data change that looks like this

AND([AllTasksComplete] = TRUE,[IsActive] = TRUE,[_THISROW_BEFORE].[AllTasksComplete] <> [_THISROW_AFTER].[AllTasksComplete])

This event triggers on an update, for table jobs, and the above is the condition. What I'm after is when people complete all my tasks in a job, the virtual column "AllTasksComplete" changes to TRUE. I know the virtual column works because I can see it in my job views. I've eliminated the before and after requirements as well to test (just on update with these criteria) and the test worked, but it won't actually do it automatically.

2. Execute the below process

2. Send a notification to me - job is complete

3. Send an email to me - job is complete.

However, I'm not getting the actual notifications automatically. I've tested the process, notifications and email tasks independently and they all work. However the bot itself isn't working. I never get these notifications without running a manual test.

The Bot Monitoring tab shows a bunch of successful bot runs during this time I've been fighting with this, but again, unless I'm manually doing this test, the bot doesn't actually engage in the automation, regardless of what the monitoring tab shows. I don't actually get the push notifications.

I'm on a paid start plan. The plan analyzer said this was what was allowed. I'm not sure if this is a problem and I can't find the documentation on it. The plan comparison chart doesn't show specific features anymore.

Can someone help me understand what is going on here?

[dbaum]

Automation events can be triggered by data changes saved to a table's data source, but not by changes in the result of a virtual column's App formula calculation when it happens to be recalculated.

[Greg_Huston]

This makes sense and is likely the cause of all my problems. Here's the code for my virtual column (AllTasksComplete)

NOT(CONTAINS(SELECT(JobTasks[TaskComplete], [JobID] = [_THISROW].[JobID]),"FALSE"))

This basically checks a job's tasks if any aren't complete, and sets TRUE/FALSE based on that. It's virtual.

Should I just change that to a regular column? Will that work then with the notifications?

[dbaum · Accepted solution]

One approach is indeed to convert the virtual column into a data source column. If you configure the automation event to be triggered by a relevant value in that column, it would still depend on the row being recalculated, which wouldn't happen automatically just because values change in another table. To force recalculation, you could create an action that triggers that, perhaps as the Form saved event where a job's tasks are updated.

A different or complementary approach would be to trigger the automation event when a task record's status changes. A design pattern could be along the lines of:

• Define the event's condition to something like:

AND({expression that checks whether the task's status changed to "Complete", probably leveraging [_THISROW_BEFORE]}, ISNOTBLANK(SELECT(Tasks[TaskID], AND([JobID] = [_THISROW].[JobID], NOT([Status] = "Complete"))))

• In that automation, include a step that does one of the following:
  • Run a data change action that is a Data: execute an action on a set of rows action that from the Tasks table executes a Data: set the value of some columns in this row action on the Jobs table to trigger the row recalculation described above.
  • Run your send a notification task, with the expressions written from the context of the Tasks table rather than the Jobs table.

[Greg_Huston]

Just wanted to say a big thank you for this. It's a super easy way to make this happen, and explains some of the behaviour I was unclear on.

[Greg_Huston]

Perhaps better said, if each job has multiple tasks, how would I create a notification that will be sent to me when *all* tasks are marked complete? I'm using virtual columns with an app formula (shared below) to see when all tasks are complete - but I can't launch notifications from that. how would I accompllish this?