Error referencing [_THISROW_BEFORE] for new row in automation branching condition

[dbaum]

I have two automations based on data change events set to trigger based on "all changes" because I need them to run when a row is added, updated, or deleted. In one automation, a reference to [_THISROW_BEFORE] causes an error in the case of a newly added row; the other automation processes the reference with no error.

Error

One branching condition in the process evaluates whether the value of the row's [Active] column was true before the change. For a new row, I expect the condition to evaluate to false and send the automation down its "No" path. However, when the trigger event is a newly added row, the automation fails altogether at this point. In the automation monitor, I see the message "Error encountered in step with name [Delete Active Tenant?]: IfElse expression evaluated to null". That suggests that it's not possible to reference the "before" value for a new row.

No error

Nonetheless, I have another automation with a similar condition that does reliably work even for new rows. In this case, the condition evaluates whether the value of the row's [Role] column is in a specified list. As expected, when the trigger event is a newly added row, the condition evaluates to false and sends the automation down its "No" path.

In both cases, the [_THISROW_BEFORE] value is presumably null when the event trigger is a new row. So, I don't understand why the condition's expression can be evaluated for the IN() boolean function but not for a boolean column's value.

Any guidance?

Is this expected behavior--perhaps based on some logic to the different behaviors that I'm missing?

Is there a recommended way to reference the "before" value for a boolean column in an automation branching condition? I have other ideas to try, but testing each is pretty involved and, so, wanted to confirm first with the expert community colleagues.

[Koichi_Tsuji]

I just wonder what is going to happen if you add another step to check the condition if or not the [_thisrow_before].[xxxx] is null or not, using Isblank([_thisrow_before].[xxx]) kinda of expression to branch the remainder of steps..... Just additional conditional branch.

[Steve · Accepted solution]

Probably a bug with automation--I'd guess Automation isn't expecting a standalone Yes/No reference. Try this:

AND(TRUE, [_THISROW_BEFORE].[Active])

[dbaum]

That's indeed seemingly the issue. I've got it working now. Thanks for the direction.

Details follow. If I should report the bug elsewhere, please advise.

Did a bunch of methodical testing. When using my [Active] Yes/No field in an automation branching condition:

• the following standalone expression results in an error for an added row

  • [_THISROW_BEFORE].[Active]

• the following standalone expression results in an error for a deleted row

  • [_THISROW_AFTER].[Active]

I revised my expressions as follows, and everything works fine--including for added and deleted rows:

[_THISROW_BEFORE].[Active] = true

[_THISROW_AFTER].[Active] = true

[Steve]

Escalated.

[kvngo94]

Hi dbaum, 

could you advise how you set up for detecting below kinds of rows

- a newly added row : not in the database before, newly added

- an existing row, just got update : already exists, just got updated

- a deleted row :  already exists, just got deleted