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.
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.
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]
|[Blank] = 0
[BlankDuration] = “000:00:00”
|[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)
|[Blank] <> 0||true||false||true|
|[Blank] < 0
[Blank] > 0
|[Blank] <= 0
[Blank] >= 0
|[Blank] < 1
[Blank] <= 1
[Blank] > -1
[Blank] >= -1
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.