Optional condition that is checked before firing the Action

[Dion]

I was trying to use the following condition to fire an action in the automation (to send an email):

AND([_THISROW_BEFORE].[Percentage Completed] < 1,
[_THISROW_AFTER].[Percentage Completed] = 1)

But it doesn’t work. [Percent Completed] is a virtual column. Does it have anything to do with the problem I’m facing by any chance?

Another info here, the following condition, which is quite similar to the first one, works just fine in another bot:

AND([_THISROW_BEFORE].[Status] = “Open”,
[_THISROW_AFTER].[Status] = “In Progress”)

[Status] is not a virtual column.
Feel free to speak if you need further info about the problem I’m facing. Thanks.

[Marc_Dillon]

Dion:

[Percent Completed] is a virtual column. Does it have anything to do with the problem I’m facing by any chance?

Yes, that’s your exact problem. Bots trigger on data changes, virtual columns are not “data”.

[Dion]

Ahh. Thanks for pointing that out

[Dion]

I’m back. I managed to pass the value from the virtual column [Percentage] into the non-virtual column [Percent Completed]. And I intended to use this non-virtual column as a condition to trigger a Bot to send an email. But it doesn’t work, and I don’t understand why. Can anyone see this question help?
This is the condition with non-virtual column:
AND([_THISROW_BEFORE].[Percent Completed] < 1,
[_THISROW_AFTER].[Percent Completed] = 1)

[Aurelien]

Hi @Dion

Virtual columns are calculated “on the fly”, real columns are calculated “on editing”.
I suspect this is the new issue you are facing, if percentage is calculated from another table.

My suggestion:
You may want to set an action that will update this value at any necessary time.

Typically, this could be a combination of:

• action1 that will be performed on a “Behavior” section of a form
• action1 “Execute an action on a set of rows”, that will trigger the action2 on another table
• action2 “set the value of some columns in this row” that will update the value

For example:

[Dion]

This is indeed the way that I used to update the value in the real column at any necessary time. But I am still unable to use the value of real column to trigger the bot.

[Aurelien]

OK.
What if you use the bot to set a value on a column “check” ?
So that, the previous condition

AND(
  [_THISROW_BEFORE].[Percent Completed] < 1,
  [_THISROW_AFTER].[Percent Completed] = 1
)

will become:

AND(
  NOT([_THISROW].[check_YesNo]) ,
  [_THISROW].[Percent Completed] = 1
)

(assuming initial value for [Check_YesNo] is set to false, of course)
And, the next step of your bot will be to switch that column [Check_YesNo] to true.

What made me think to this suggestion:

help.appsheet.com

Before and After Values

You can see the value of a column both Before and After it was updated.

and I don’t see any mention of the use of [_THISROW_BEFORE] and[THISROW_AFTER] in this post, even if, in the case of an update, it should be possible to use it:

help.appsheet.com

Event: The Essentials

[Dion]

@Aurelien

OK.
What if you use the bot to set a value on a column “check” ?
So that, the previous condition

AND(
  [_THISROW_BEFORE].[Percent Completed] < 1,
  [_THISROW_AFTER].[Percent Completed] = 1
)

will become:

AND(
  NOT([_THISROW].[check_YesNo]) ,
  [_THISROW].[Percent Completed] = 1
)

I just tried your suggestion. The Bot still isn’t triggered
So I looked into the Test for the Bot:

The Event Condition for the specific row that I’ve been testing on indicated “True”. The other rows were “False”. Does it mean the Bot was supposed to be triggered for this row?

[Aurelien]

Dion:

Does it mean the Bot was supposed to be triggered for this row?

Yes.
Can you have a look on the “Monitor” button/app ?

[Aurelien]

@Dion

Is this a “schedule” bot ?
If so, that won’t do anything if the app is not deployed.

[Dion]

It is not a schedule bot. It is set to trigger based on Data Changes

[Aurelien]

At this stage, I would suggest to contact Support, I don’t get why this is not firing the Process.

But I’m probably missing something, let’s wait for @Marc_Dillon or someone else to wake up and have a look

[Dion]

Alright, thank you so much for your help, though. I really appreciate it!

[Dion]

I’ve checked that as well. It showed that the Bot wasn’t triggered (number of times didn’t increase since last time).
The default “False” value wasn’t set to “True” too.

[Marc_Dillon]

Dion:

I managed to pass the value from the virtual column [Percentage] into the non-virtual column [Percent Completed].

How did you do this? Give screenshots and details.

Have you checked the Audit Log? It provides much better information than the Automation Monitor.

[Dion]

How did you do this? Give screenshots and details.

First, I began with creating an action, “Update Percentage” which will set a real column’s value to the virtual column’s value (as shown right below this sentence).

After that, I created a Bot with the “Data Change” Event type “Adds only” to the “Completed” table. This table has many-to-many relationship and shows the record of the training each user has done (refer to the picture below).

In this Bot, I created a “Run a data action” process with “Run action on rows”.

In Referenced Action, I used back the “Update Percentage” action that I’d created at the beginning.

This is how I did it.

[Marc_Dillon]

Just to clarify, you’ve got:

1. Bot on Adds to the Completed Table, which:
2. Runs an data change Action on a record in the Employees Table.
3. A 2nd Bot meant to trigger on changes in Employees Table.

And it is #3 that is not triggering?

That would be because by default, data changes from Bots, do not trigger other Bots. But you’re in luck, since they’ve recently given us the option to allow this, and it’s as easy as flipping this switch on your first Bot:

---

Dion:

Marc_Dillon:

Have you checked the Audit Log? It provides much better information than the Automation Monitor.

Oh, I haven’t checked this out yet. How should I do it?

From here:

[Dion]

Marc_Dillon:

Just to clarify, you’ve got:

1. Bot on Adds to the Completed Table, which:
2. Runs an data change Action on a record in the Employees Table.
3. A 2nd Bot meant to trigger on changes in Employees Table.

And it is #3 that is not triggering?

The Bot that I mentioned in my previous comment works well. It is used to pass virtual column value to a real column. And there’s actually a 2nd Bot that will be triggered by the optional condition based on the value of that real column, in order to send an email. But the 2nd Bot won’t work, I don’t know why.

[Marc_Dillon]

Dion:

But the 2nd Bot won’t work, I don’t know why.

Ummm…did you forget to read the rest of my reply?

[Dion]

Oh I did read it. Sorry I didn’t mention that cause I kinda couldn’t get it at first. So, I went to try it out. I think it worked I will do a few more testings and get back to you. Thanks a lot @Marc_Dillon

[Dion]

Marc_Dillon:

Have you checked the Audit Log? It provides much better information than the Automation Monitor.

Oh, I haven’t checked this out yet. How should I do it?

[Aurelien]

@Dion
I also see this, that may be what you are looking for, very simply ?

[Dion]

Problem solved. Thanks @Marc_Dillon for pointing out this

Marc_Dillon:

That would be because by default, data changes from Bots, do not trigger other Bots. But you’re in luck, since they’ve recently given us the option to allow this, and it’s as easy as flipping this switch on your first Bot:

And thank you too @Aurelien I like your idea of this:

Aurelien:

AND(
  NOT([_THISROW].[check_YesNo]) ,
  [_THISROW].[Percent Completed] = 1
)

And I’m actually using it right now.