Expressions: New option for faster and more consistent comparisons

Hey everyone! In the coming days, we will enable the option for expressions in apps to use new behavior for comparison operators (=, <>, <, <=, >, >=). This will be an improvement in sync performance and consistency of results.

The current behavior will be called the Legacy option and the new behavior will be called the Consistent option. The ability to change an app between Legacy and Consistent will be added under Data > Options > Expressions > Blank value comparison mode.

Performance Improvement

We improved the performance for comparison operators when they are evaluated on the AppSheet server, such as in virtual columns. Since virtual columns are recomputed on app sync, this may improve the speed of sync.

Consistent Comparisons

The Legacy comparisons behavior is known to have some issues with accuracy and consistency between expressions evaluated in the server and client (e.g., app formulas in virtual columns vs. ones in physical columns). These issues can cause unexpected behavior in apps, and can take a long time for app creators, support, and devs to track down. Here is an example of one of those issues. The Consistent option resolves this issues with the changes summarized in the table below. The examples use column notation to represent a blank value ("[Blank]"), but it represents any expression that evaluates to a blank value.

Expression example Server (Legacy) Client (Legacy) Server and Client (Consistent)
1 = [Blank] true false false
[Blank] = [Blank]
[Blank] <= [Blank]
[Blank] >= [Blank]
true true true
[Blank] = 0
[BlankDuration] = โ€œ000:00:00โ€
false true false
[BlankDateTime] = โ€œ12/31/1969 6:00:00 PMโ€
[BlankDate] = โ€œ12/31/1969โ€
[BlankTime] = โ€œ06:00:00 PMโ€

(The value on right side is the user TZ offset from UTC unix epoch)
false true false
[Blank] <> 0 true false true
[Blank] < 0
[Blank] > 0
false false false
[Blank] <= 0
[Blank] >= 0
false true false
[Blank] < 1
[Blank] <= 1
[Blank] > -1
[Blank] >= -1
false true true

FAQ

Why is the option opt-in?
Since it changes the behavior for how comparison operators handle blank values, the Consistent option is not strictly backwards-compatible with existing expressions. Weโ€™ve made both behaviors available to give app creators time to adapt to the new behavior. In several months, the app editor will begin explicitly encouraging use of the new behavior. Eventually, the old behavior will be removed entirely.

How are new apps or apps created by copying another app affected?
New apps will default to Consistent, but along with all apps, can be switched between Legacy and Consistent in the app editor. Apps created by copying another app will use the same comparison mode as the copied app.

What should I do if using the Consistent option makes my app work differently than I intend?
If your app depended on the original behavior such that switching to Consistent broke your app, switch back to Legacy to restore the original behavior. From there, you can make a copy of the original app as a test version, switch the test app to Consistent, then make the necessary changes to the test app so that you end up with a stable app using the Consistent option.

14 29 2,565
29 REPLIES 29

Will this affect this at all

?

Hi @Marc_Dillon โ€“ this does not affect that feature request

I applaud you and AppSheet in recognizing the issues in inconsistent behavior between the client and servers and the need to sync the behavior between the two.

I would like to point out that there still is a flaw that will result in problematic comparisons and while it may be easier with the โ€œConsistentโ€ implementation it can still be difficult to identify, especially in complex expressions.

In the image below, I am highlighting the contradictory behavior. Once the decision is made that [Blank] <> 0, it is contradictory to imply that [Blank] is less than 1 and that [Blank] > -1. This is because these are INTEGER values and the only logical value [Blank] can be is ZERO - if <1 is in fact TRUE.

This also begs the questionโ€ฆwhat is the result of โ€œ[Blank] < 1.0โ€??


I am not sure why AppSheet is re-inventing the wheel when it comes to the treatment of [Blank], Empty String, Space and Zero. There are well documented implementations. Maybe you are trying to simplify expressions by eliminating the need to test for [Blank] everywhere? The past has proven that trying to avoid that complexity doesnโ€™t work.

IMHO, in would be best to treat [Blank] as a text value that is different than Space. (You may be able to treat [Blank] and Empty String as the same.) This would mean that [Blank] is not a valid operand in comparisons against numbers - just like any other text value such as โ€œAโ€, โ€œBโ€, โ€œCโ€, etc - and would result in an error when used in a numeric comparisons - whether NUMBER type or DECIMAL type.

Thanks for pointing that out @WillowMobileSystems . The comparison against 1 section being at the bottom, I totally glossed over it on my first review.

Blank, or null, values are acceptable values in Number or Decimal columns though. What do you mean by โ€œresult in an errorโ€?

I think any blank value being compared as equal, less than, or greater than, any number/decimal, should return FALSE. And conversely, being compared as not equal to any number/decimal should return TRUE.

To clarify my position, I agree with the entire table in the OP, except for this portion:

3X_a_1_a1ca112524f28c55e7b75c4bba0eb41e355b175a.png

@natalie , can you comment on the reason why it was decided like this?

I stand corrected and agree with you, any numeric comparison with [Blank] should return FALSE.

Good point and I didnโ€™t think my proposed implementation through well enough for a no-code platform - I had a flashback to my days as a c-sharp developer and was thinking of runtime instances.

In our use case here, we donโ€™t know what the runtime value will be and [Blank] is a valid column value or returned value from a sub-expression. So we DO need to accept it as a valid value

HOWEVER, this WILL lead to confusion and error detection problems. We are human and there will be scenarios where we THINK the value simply cannot be [Blank] when in fact it actually is. Or we simply do not think of this as a use case. Weโ€™ll bang our heads on our keyboards for hours until โ€œQWERTYโ€ appears on our foreheads then weโ€™ll finally realize the issue is because some tested value actually is [Blank].


There are two ways to minimize the embarrassing โ€œQWERTYโ€-on-forehead syndrome:

  1. Begin relying on the Test feature for the expression to confirm expected results. This may require many test rows to be added to work well - especially in a new app.

  2. Explicitly test for [Blank] and not [Blank] - where it may occur. At the very least this will help remind you of a possible [Blank] value when reviewing the expression in the future. An example would be:

AND(ISNOTBLANK(<<value to test>>), <<value to test>> <= 100)

If you wanted to accept [Blank] as a valid value, you MUST test for it anyway:

OR(ISBLANK(<<value to test>>), <<value to test>> <= 100)

With the values as indicated by the OP, yes, you do.
But with the one change that I mentioned, Iโ€™m pretty sure that no, you donโ€™t.

How so?

Consider these 2 questions:

  1. โ€œIs blank less than 0?โ€
  2. โ€œIs blank less than 1?โ€

What would be your answers to those 2 questions?
I would say โ€œnoโ€ in both cases.
The โ€œconsistentโ€ results from the OP apparently would say:

  1. โ€œnoโ€
  2. โ€œyesโ€

I would call that inconsistent.

Now consider these 2 expressions:

AND( ISNOTBLANK( [val] ) , [val] < 0 )
AND( ISNOTBLANK( [val] ) , [val] < 1 )

For the first expression, with a blank value, and regardless of your answer for #2 above, both terms would be false, i.e., theyโ€™d agree. At which point, thereโ€™s no reason to include the ISNOTBLANK() term.

For the second expression, with a blank value, if you answered โ€œyesโ€ to #2, the terms do not agree, and so it is required to include the first term to keep the same functionality. But if you answered โ€œnoโ€ again, then it is the same as above, the terms agree and thus thereโ€™s no reason to use the ISNOTBLANK term.

Obviously this is just one use-case. My โ€œpretty sureโ€ was alluding to the fact that I canโ€™t possibly think of all use-cases. But all that I have thought of would benefit from my desired behavior, and not force the checking for blank values in every expression.


Interesting. I did not realize it was a requirement for inversions to have opposite values. I went to go check some results in my browserโ€™s javascript console just now, and got this:
3X_b_1_b19ebb5f5ab5fa248a90d3d3195eb0237427d67a.png

So I suppose what was originally written is consistent with other platforms too.

I still hold my belief as expressed above, but I guess it seems Iโ€™m arguing against the convention of a very major programming language as well. I donโ€™t know what to think anymore now that I just checked that, haha.

For sure.

The discussion is probably mute now but I think there was a mis-understanding. Agreeing with you I was using the assumption that your suggestion was being used where any test between a number and [Blank] returns FALSE. In this case, if we wanted to also allow [Blank] as a valid value we would need to test for ISBLANK() like in the OR() example I supplied above. Did I miss something?

Hi @WillowMobileSystems and @Marc_Dillon โ€“

The goal is to represent blank as a single unique value that is not equal to the default value (in the case of Number, this is zero), but acts like default value in non-equality comparisons (<, >). Once this is understood, I believe the new behavior in the table is consistent with itself. So, to answer the one of the questions asked, [Blank] < 1.0 will result in true. Blank and empty string will continue to be treated the same.

This behavior may not be perfect for everyone, but we thought it that it would be sensible enough in practical usage.

If blank values in comparisons were to always return false, then we would be introducing inconsistency elsewhere since Blank > 1 and Blank <= 1 would both be false despite being inversions that are supposed to have opposite values.

If blank values are unable to participate in comparisons with numbers and instead return neither Yes or No but a third โ€œunknownโ€ value like SQL databases, this would make the Yes/No type more complicated.

I guess there really is no perfect implementation or we wouldnโ€™t have different ways these outlier values are treated currently in other languages/platforms.

I still think there will be confusion but at least server and client will behave the same which is the most important enhancement with this change!!

Austin
Participant V

This should 100% be pinned on the forum so that it cannot be below the first page when you open it up as it has a profound affect on pretty much every app. Also should be an un-ignorable notification when it is added since it has such a big impact.

Steve
Participant V

My opinion:

  • A blank textual value should be equivalent to an empty string ("")

  • A blank numeric value should be equivalent to zero.

  • A blank Yes/No value should be equivalent to FALSE.

  • Blank List or EnumList: an empty list.

  • Blank Time: 00:00:00 (midnight)

  • Date: first date of the epoch

  • DateTime: midnight of the first day of the epoch

  • Duration: 000:00:00

Alternatively, a blank value in a value-to-value comparison should be a fatal error.

What would be your opinion of the output of this situation:

ANY( Slice[Number-column] )

โ€ฆwhere the Slice has no rows in it.

ANY( empty-list ) should return nothing, producing a blank value.

100% agree

100% agree

66% agree but the other 34% is not against this.

I think the key is just clear understanding and explanation of the outcomes for all situations that involve a possible blank value. Itโ€™s not necessarily a problem that I need to use OR(ISBLANK())'s. Itโ€™s the fact that itโ€™s not inherently obvious that I need to use them.

I have made the switch to โ€œconsistentโ€ and found that true-false display conditions such as the following

[column name]<>""

need to be changed to

isnotblank([column name])

No big deal but this didnโ€™t seem to be in the basic description about the difference between Legacy and Consistent.

Austin
Participant V

Also given how little of my time I can devote to the appsheet side of our systems, is there an end of life timeframe for the Legacy option? Just to put on our timeline.

Hi @natalie the โ€œLearn moreโ€ opens in the same tab. I think it should open in a new tab.
3X_e_0_e0e64ad3f4f3fe17072fe2bd5d5662f095e637c0.png

Thanks for the suggestion! It should be fixed in the first deployment of next week.

Hi @natalie

I noticed this new option was made avaiable under our account. To ensure this option is selected as โ€œConsistentโ€ for our entire apps, We wish to control through the team policy.

Is any โ€œpolicy objectโ€ available for this new option?4

Thank you.

@Takuya_Miyai
@kamila

Hi @natalie ,

The help indicates that the default will be Consistent, but that does not seem to be the case at the moment.
In both my Enterprise and Core environments, the default for mock apps created from Idea is Legacy.

Thanks,

When creating a new app, the mode defaults to Consistent. When copying an existing app, the mode set for the original app will be maintained.

@tsuji_koichi

Hi @natalie

The default is still Legacy if I created it from โ€œStart with an ideaโ€.
This behavior is different from the behavior when creating from a spreadsheet, and I think it should be unified soon.

Thanks

Hi @Takuya_Miyai , thanks for the report! We will track this issue internally to look into a fix.

Hi @natalie

Does it fix the fact that these two settings are different when created from idea?

I like the idea feature so much that I am seeing more and more apps with different settings at the metadata level .

@tsuji_koichi

Hi @natalie

If we still create an app from Start with an idea, these two settings will remain old.

Snag_1fba44c.png

โ€ƒ

Snag_1fbcef6.png

โ€ƒ
@zito 
@Steve 

@natalie

To add another findings and feedback to you, once we create new app from idea, the Apply show-if constraints universally settings is turned off by default. It would be better to make this option to be turned on to avoid the confusion.

@Takuya_Miyai

Thanks @tsuji_koichi

@natalie

This is it.

I see, thanks for the report @tsuji_koichi @Takuya_Miyai !