Solved: Event from Virtual Column not firing - Page 2

Greg_Huston · 07-01-2022 02:36 PM

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

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.

View solution in original post