Solved: Old legacy branch condition changed behaviour

OptimiX_XcrY · 12-21-2022 10:18 AM

Hello,

I have an expresion used as a branch condition for an action in an older app of mine. This branch condition worked with no issues so far, until this week at some point , when instead of False it acted like a True .

And(isnotblank([stadiu]);
[stadiu]="finalizare";
Count(select(comenzi[x4];and([x4]=[_thisrow].[id task];or([status]="nepreluat";[status]="pregatita"))))>0)

The basic logic is that if I "close" a client that still has open orders it does an action.

The action starded to apply in the last few days even when there were no open orders.

As a test , so far , I used the same expresion in a virtual column, and it returns False (N).

I also used the count() from the expresion in another VC and it returns 0.

But the action still trigers, and in Monitor section I see it acts like True (Y).

I think it might have something to do with Legacy vs Consistent. It was in Legacy until today. I tested with consistent and it seems fine now, but without me understanding it.

In help sections , I saw that [blank]>0 returns false in all instances of legacy or consistenct....in case my count() somehow returs [blank] , so it should have worked fine with either setting.

Please help me understand this case , so I might have better chances of figuring out where in my other apps I could have issues I don't know about yet.

Thank you

OptimiX_XcrY

Support confirmed that my ticket was successfully solved.

I just tested on my apps , and I confirm too that it works as it should be, both in legacy and in consistent mode.

Thank you all

View solution in original post

WillowMobileSys

I don't think the expression itself is the problem. You are likely running into data situations you haven't considered.

Please provide us with some data examples that you believe are behaving incorrectly with your expression so we can help you through that understanding.

Appy

I have the same issue.

A branch condition I have been using for the past year stopped working properly about a week and a half ago. Nothing had changed in the data structure nor the Bot making the call.

The True branch is now being ignored and the False branch is always followed.

I have been trying to debug this for over 2 solid days. Nothing has an impact.

To be clear, the table in the condition branch is the table the bot initially references.

Here is the branch code which I have simplified as much as possible but still doesnt work:

===========

(COUNT(
FILTER(
"Next of Kin",
AND(
([CR#] = [_THISROW].[CR#]),
(TRUE = [Signed]),
(TRUE = [Active]),
(TRUE = [Appsheet Nok])
)
)
) = 0)

===========

I have error checking in place via sms txts that are generated in the process. Everything looks as it should, its just the conditional branch that is not executing properly.

I have to wonder if some backend change took place at Appsheet because there are 2 of us with the same issue.

Penny

OptimiX_XcrY

Does anything change for you too if you change that Legacy/Consistent setting?

Appy

No.

I changed the app to Consistent yesterday but I get the same result.

Appy

There is definitely something screwy with appsheet.

I rewrote my event as 2 events in order to remove the conditional branching.

One event triggers if false, the other triggers if true. They work properly.

But there is something screwy with appsheet true/false. I have another trigger that sends a webhook when a row is added. The webhook does not fire and the audit log shows that the event returned false so didn't fire.

When I change back to legacy the event triggers and the webhook is fired.

This is the event trigger:

AND(
(TRUE = [Active]),
(TRUE = [Appsheet Nok]),
([_THISROW_AFTER].[Total NOKs] > [_THISROW_BEFORE].[Total NOKs])
)

Total NOKs is never blank. there is nothing here that could be affected by comparison behaviour.

I also noticed that a table column (Yes/No) that I set the display values to "Yes" and "No" actually display as "N" and "Y".

WillowMobileSys

@Appy wrote:

I have to wonder if some backend change took place at Appsheet because there are 2 of us with the same issue.

I agree that there are now 2 of you with apparently the same issue that its much more likely that some bug was introduced. Others are probably being impacted and not yet aware of it. I would encourage BOTH of you to submit an item to AppSheet support.

The "Legacy vs Consistent" setting only deals with how a "blank" result is handled in comparison operations. There was a difference in the past between the device side and server side when executing identical expressions. The difference was fixed but there was backward compatibility problems with older apps - hence the reason for the setting - so app creators can control WHEN to switch to "Consistent" - presumably after fixing their code to match.

Today, you should ALWAYS use "Consistent". If you are working on older apps, you should switch to "Consistent" and fix any related problems. The fact that this setting is listed under "Legacy" is a good indication that this setting is not intended to be supported indefinitely.

Now....

Whether this setting impacts each of your particular use cases...I can't be sure sure. One thing you could do to verify, is REPLACE your current COUNT() expression with an IF statement similar to the below that forces a zero when the returned list is empty:

IF(ISBLANK(<<expression that returns list>>),
     0,
     <<original COUNT() expression>>
)

This kind of expression, for each of your use cases, renders the Legacy setting ineffective so you can verify of the setting actually could be making a difference. If you see no difference in the issue you are having then this Legacy setting is not the culprit - something else is going on and should be raised to support.

If things begin to work, but don't without the IF() and Legacy setting ON, then maybe the AppSheet Legacy implementation was removed creating a bug. Support should be contacted.

OptimiX_XcrY

Thank you, I will try that and come back.

Can a count() return a [blank] value?

WillowMobileSys

That is what the expression is testing. I actually don't recall if in the "legacy" implementation treated COUNT("blank") different than COUNT(LIST("blank")) - i.e. no items vs a LIST of "blank" items. But that seems to be the area of commonality.

Appy

I have opened a ticket. They have sent the issue to a specialist. I think it goes beyond count() issue. TRUE/FALSE returns seem to be affected.

I'll let you know

OptimiX_XcrY

I have oppened a ticket as well, but when I did a test again , it worked fine for me. I guess your ticket solved my issue as well.

The ticket operator did not gave me explanations, but recommended to use consistent for now on. I will, but with no easy way to check if my older apps will be affected. Thanks for your ticket

OptimiX_XcrY

Actually it works in legacy now, but does not behave correctly in consistent anymore.

And I can't find the chat from my ticket

OptimiX_XcrY

Before I opened this thread I wrote the count() value in physical column, while testing the issue. The written value in cell was always 0 , not blank.

Anyway, unfortunately right now the legacy behaviour is ok, but the consistent behavior is not anymore, it does not apply the action when the branch condition is true.

I am waiting for a solution from support.

OptimiX_XcrY

I rewrote my expresion as

And(isnotblank([stadiu]);
[stadiu]="finalizare";
or(
Count(select(comenzi[x4];and([x4]=[_thisrow].[id task];or([status]="nepreluat";[status]="pregatita"))))>0;

isblank(Count(select(comenzi[x4];and([x4]=[_thisrow].[id task];or([status]="nepreluat";[status]="pregatita")))))
)
)

It did not solve the issue 😞

OptimiX_XcrY

My testing results :

1. Legacy and true logical expression , branch is true (OK)

2.Legacy and false logical expression , branch is true (not OK)

3.Consistent and true logical expression , branch is false (not OK)

4.Consistent and false logical expression , branch is false (OK)

So Legacy always returns True , Consistent always returns False

Appy

I haven't had a response from support yet. I was told they would contact me by email once the specialist had his results/solution/Qs.

I have the same issue with Constant. I had to revert to legacy. There was a whacky issue w TRUE/FALSE (nothing to do w Count()) in some other processes.

I have a workaround for the count() issue in my failing Bot but I havent implemented it on live app because I want to see if the issue is fixed. (Meantime I prefer to make manual fix after the fact so I can keep an eye on it.)

I believe it may be fixed because I had a user trigger the bot last night and it performed correctly.

So, maybe Count() is fixed in Legacy (I'll confirm that when it is triggered again in another live app I have). But maybe Consistent, and its overall issues are not yet fixed.

Steve

Attn @Aleksi @devingu

takuya_miyai

Dear Google Support team.

I am experiencing a similar phenomenon in my environment.
I was directed to this thread by AppSheet support, but to my surprise, no Google member has responded to this thread in any way.
What the heck is going on?

Customer app data keeps getting corrupted. If there really is a problem with the logic, please make an announcement about the problem as soon as possible.

@zito
@Lauren_vdv
@Roderick

@Steve
@Aleksi

Aleksi

Hi all. Just saw this thread. The last time we have deployed, is 12/21/2022, but nothing after that. Let me check this from our devs in the US if we have touched that part of the code.

Appy

FYI.

The Count() issue first appeared on Dec 16th.

OptimiX_XcrY

I second that.. sometime in the first half of december, based on wrong data added by branch condition actions

Aleksi

It seems that our devs are aware of this and they are investigating the root cause.

dbaum

FWIW, I'm observing what may be the same issue. Here's some chronology and comparison for reference in case the additional nuance is helpful.

I implemented a new automation on 12/11/2022 that runs daily. It uses the same SELECT expression in multiple places: all but one of those uses are in the Email Body property of a Send an email task (e.g., wrapped within COUNT or as part of START); the remaining use is in a Branch on a condition step (wrapped within ISBLANK). The Send an email task has been reached multiple times (including today) and the expression has reliably returned accurate results in the email content. Before today, when the Branch on a condition step was reached, it accurately returned false (most recently, on 12/26/2022). Today is the first time that the Branch on a condition step was reached and should have returned true, but it inaccurately returned false.

Branch on a condition expression

Edit 1/1/2023: Should have noted for reference originally that my app's "Blank value comparison mode" property is set to "Consistent".

Appy

FYI.

I have not heard back from specialists yet. For the record, my Count() scenario correctly ran on one app last week and incorrectly ran on another 2 apps last week. That particular code is exactly the same on all 3 apps. And they are all set to legacy

WillowMobileSys

@Appy wrote:

my Count() scenario correctly ran on one app last week and incorrectly ran on another 2 apps last week

I have been an app developer for over 26+ years now. Putting aside the extremely unlikely (but possible) case that the AppSheet code runs differently in your exact same use cases, I find it very difficult to believe that the exact same expression used in the same way would run correctly in one use case but not run correctly in 2 others. There must be some other variables at play causing different results. Maybe something you don't realize is impacting the results?

For what's worth, I have had nearly a dozen different cases over the years where I was convinced, without proof, that there was an AppSheet bug. After almost sending a bug report in most of those cases, I realized I was being lazy and had not done enough analysis and decided to do the due diligence. In every one of those cases, I found I did something wrong or I didn't completely understand how the system was operating.

For these difficult to understand use cases, yo have to break them down into pieces. The Test feature in AppSheet does not always provide a good picture of what is going on. I typically do this by creating several TEMPORARY Virtual columns to house parts of my expression so I can investigate each part separately and zero in on the part that doesn't make sense.

For example, using this expression...

COUNT( FILTER( "Next of Kin", AND(
                                   ([CR#] = [_THISROW].[CR#]),
                                   (TRUE = [Signed]),
                                   (TRUE = [Active]),
                                   (TRUE = [Appsheet Nok])))
) = 0

I would break it into these smaller expressions and assign each to a Virtual Column...

FILTER( "Next of Kin", ([CR#] = [_THISROW].[CR#]))

FILTER( "Next of Kin", AND([CR#] = [_THISROW].[CR#], TRUE = [Signed]))

FILTER( "Next of Kin", AND([CR#] = [_THISROW].[CR#], 
                           TRUE = [Signed],
                           TRUE = [Active]))

FILTER( "Next of Kin", AND([CR#] = [_THISROW].[CR#], 
                           TRUE = [Signed],
                           TRUE = [Active],
                           TRUE = [Appsheet Nok]))

I would look at the "Next of Kin" data and then decide what I would expect as row results for each of these and confirm by running the app that those are the results I get.

If any results don't match, I then inspect that expression to understand why my expected results were not received. Once all looks good, I would add COUNT to each one and again confirm results are as expected.

If all still looks good then I would run in automation. If results DON'T match...not done yet. I would add TEMPORARY table columns and create an action to assign the same set of test expressions above. Then add the action into the automation flow and run the Bot again. What results are recorded in the columns? Do they match what was done in the Virtual Columns? If not you need to investigate why.

This can be tedious work...welcome to the world of advanced app development!!

I hope this helps!

Appy

I appreciate the input. But I spent days looking into this before I went to the community to see if there were similar issues. It seems there are. Also, Appsheet support acknowledged there was an issue. I was told that it might take some time to resolve because it was the Holiday week and also they were trying to find the root cause.

So you understand, I rewrote my bot so it does not use the branch/count(). I broke it down into 2 bots- one that's triggered by the FALSE state and the other that's triggered by the TRUE state. This approach works fine. So, it is not the data or data structure.

Oh, and the app in which in ran correctly last week it failed the time before that. When it ran correctly that was when I hoped it had been fixed. But it is still failing in the other 2 apps. It hasn't yet been triggered again in the app that had a success. But when it does I'll know better if this is an inconsistent bug.

Also, I did all my error checking prior to going to the community. I added sms triggers through each step in the process so I would receive the values passed in txt. All the values were as to be expected. It was the fail at the branch that was the issue (not recognizing true result and following false branch).

bcairns

Thanks for everyone's patience. I'm an engineer on AppSheet who is looking into this.

Unfortunately despite all of the examples everyone has helpfully posted, we can't reproduce this issue in either our production or test environments.

As part of our normal holiday release freeze, we did not push a new release of AppSheet between 2022-12-22 and 2023-01-02. This means that if expression behavior changed between those dates, it is unlikely that it was caused by changes made to AppSheet.

We have made some bug fixes regarding [_THISROW] evaluation in bots which were rolled out slowly, concluding around 2022-12-15. However, in my testing, [_THISROW] appears to be working correctly.

It would help me immensely if anyone who is experiencing this issue could provide a test app that demonstrates the consistency issue. Alternatively, if you are OK with AppSheet engineering examining your live app, please file a ticket and indicate that it's OK for support/engineering to take a look. You will need to enable AppSheet support to access your app:
https://support.google.com/appsheet/answer/13003359?hl=en&ref_topic=10101310

Appy

Hi,

The issue began on December 16th. Prior to that, the same scenario/condition was triggered by a customer on Dec 13th and it worked without issue.

Also, I had opened a ticket on Dec 26th which referenced this thread and also gave another example of an inconsistency between Legacy and Constant. It did not have anything to do with Branching or Count() but it does show where there is an issue with Appsheet not recognizing TRUE state and treating it as FALSE - which I believe is actually the real issue with the Count() and Branching we have reported in this thread. (FYI. The true/false issue example I gave in the ticket does not have anything to do w a blank var or 0. It is just reading cols in a table that are either true or false).

The chat refernce is

Chat transcript for case: 7-6543000033186

I have not yet had a response to that open ticket.

bcairns

I'm still investigating whether there were any behavior changes that affected your app, but I wanted to note that legacy and consistent modes were not intended to produce the exact same results in all cases. We introduced the legacy/consistent option because we knew that some apps would be affected by the changes in consistent mode.

Specifically, rules around [Blank] comparison have changed considerably in Consistent mode.

In legacy mode:

The AppSheet server evaluates expressions the same way it did before we added the "consistent" mode
The AppSheet runtime (on the web/mobile device) is not consistent with how the server evaluates expressions. This can lead to unpredictable behavior.

In consistent mode:

The AppSheet server evaluates expressions in a way that is consistent with the runtime's (web/mobile device) behavior
Some expression evaluation behavior has changed compared with the "legacy" mode

Our support article provides a table of differences:

https://support.google.com/appsheet/answer/11510515?hl=en

Appy

To be honest I am a little confused. There is nothing in the legacy/Consistent support page that references anything other than [blank] comparisons.

Are there any other scenarios not documented?

bcairns

The "legacy" mode actually compares values by converting them to strings, so there are a significant number of weird quirks that unfortunately don't follow consistent patterns.

Essentially, the legacy algorithm is:

Convert both the left and right-hand values to strings
Compare those strings, applying a number of exceptions to attempt to avoid the inevitable problems which come from comparing strings

For example, consider the expression "10 < 2". In the legacy system, we convert 10 and 2 to strings "10" and "2". This would result in "true" if we compared the strings directly, so there's code to work around this. Unfortunately, this code isn't particularly comprehensive, so there are cases that get missed. Things get particularly complex when lists, blank values, and mixed types are considered. The behavior you see in these complex cases in "legacy" mode is hard to predict, because it depends on the string representations of the values, which isn't well-specified.

This inconsistency in "legacy" mode is one reason why we recommend using only the "consistent" mode, and why we made it the default for new apps.

Appy

Thank you.

Thank you. That helps.

OptimiX_XcrY

This is my case, where there shouldn't be any difference :

I don't know if I'll be able in the next few days to replicate the issue in another app , and the affected one being already in use by my collegues , but I'll try.

bcairns

Thanks for that information.

Based on our investigation so far, it does not appear that the result of blank comparisons has actually changed in either legacy or consistent mode.

In testing against our live environment, [Blank] < 0 and [Blank] > 0 both evaluate to False in both legacy and consistent modes, which is consistent with the expected and documented behavior.

The common thread seems to be either virtual columns or SELECT statements; we continue to investigate whether something may have changed there.

OptimiX_XcrY

I managed to make a test app from phone (by cloning the live one and deleting non relevant stuff) , but connected to live tables, with security filters to only display one designated row , free for testing.

I already have an open ticket to support and the approval to acces it checked.

How can I give you acces and show the issue?

Edit : https://www.appsheet.com/Template/AppDef?appName=Testapp-1042873&appId=Testapp-1042873&linkFrom=Copy...

Edit : Case ID 1-0549000033613

bcairns

Thanks to everyone for providing examples.

AppSheet engineering was able to identify a bug that affects some expressions using [_THISROW] when used in a branch step in an automation. It appears that the bug was introduced by a fix for a different bug that also affected _THISROW in expressions. Unfortunately, this means that we cannot simply revert the change.

We are working on a root cause and will provide an update when we have a fix ready.

Thanks for everyone's patience.

OptimiX_XcrY

Thank you !

In the meantime , it is enough to avoid using [_this] in branch condition formulas , to avoid the bug?

OptimiX_XcrY

Hi,

This is not solved yet. Is there an estimated timeline?

Should the bug be posted to announcements too in the weekly post? (For other creators)

takuya_miyai

@OptimiX_XcrY

I agree with you.

@bcairns
Could you first report this issue that is occurring to the announcements category?

Many users are having trouble with this issue, but are concerned because the cause is not known.
This is an issue that may stop the use of AppSheet.

The business impact is also significant.

takuya_miyai

@bcairns

I have contacted support with these two questions and after 4 days I still have not received an answer.

Q1: Is this [_Thisrow] issue only a branch condition?
Q2: Does the same problem occur in [_Thisrow_before] and [_Thisrow_after]?

Are we in a situation where appsheet support team cannot even answer these simple questions?