Evaluate for true vs. false/blank or for false vs. true/blank

[dbaum]

I had occasion to explicitly test the results of AppSheet expressions referencing Yes/No columns in order to understand how blank values are handled. So, I'm documenting here for reference.

I tested in an app with the Blank value comparison mode property set to "Consistent". My test results  could be a helpful addition to the documentation of expression behavior when a blank value is referenced, Configure data processing - AppSheet Help. 

I found one result counterintuitive: NOT([Blank]) evaluates to FALSE. Of course, that also affects my NOT(NOT([Blank])) test. Those results are marked in red in the table. The upshot is that if you need to evaluate whether a Yes/No column is not true (i.e., either false or blank) or not false (i.e., either true or blank), it's necessary to explicitly specify the equivalence: either NOT([BooleanCol] = true) or NOT([BooleanCol] = false).

Expression	Value of [BooleanCol]: TRUE	Value of [BooleanCol]: FALSE	Value of [BooleanCol]: (blank)
[BooleanCol]	TRUE	FALSE	(blank)
[BooleanCol] = true	TRUE	FALSE	FALSE
NOT([BooleanCol])	FALSE	TRUE	FALSE
[BooleanCol] = false	FALSE	TRUE	FALSE
NOT([BooleanCol] = true)	FALSE	TRUE	TRUE
NOT(NOT([BooleanCol]))	TRUE	FALSE	TRUE
NOT([BooleanCol] = false)	TRUE	FALSE	TRUE
ISBLANK([BooleanCol])	FALSE	FALSE	TRUE
ISNOTBLANK([BooleanCol])	TRUE	TRUE	FALSE

[Joseph_Seddik]

Thank you David, this is useful.

In my humble point of view,  the "counterintuitive" results do give the expected behavior for me. 

The expression NOT([Boolean]) translates to this: "does the column have a FALSE value?". The answer is should be NO, since the column does not have a FALSE value, but it's rather empty. 

The same concept can also be explained using this expression:

IF([Boolean], YES, NO)

This will also evaluate to NO; since the column does not have a TRUE value. Thus, both [Boolean] and NOT([Boolean]) should give us a FALSE. 

[dbaum]

Thanks for adding that explanation. Evidently, that's indeed the way to mentally parse NOT([Boolean]).

(In my mind, I was considering that expression to instead translate to "is the following false?: the column has a TRUE value" or "does the column not have a TRUE value?", which is true in the case of an empty column.)

[Joseph_Seddik]

To make a long story short 🙂 This depends on the standard and probably most standards have it that way. 

One of the widely-used standards for example is ECMA International, a European consortium for Standardization of Information Systems, with members like Google, Apple, Microsoft and IBM. Languages like JavaScript and C# (the frontend and backend languages of AppSheet) use ECMA standard. 

Here's ECMA's standard for Boolean conversion: https://262.ecma-international.org/5.1/#sec-9.2 

[dbaum]

Thanks again. Also helpful. I'm certainly all for standards, and glad to understand better this one. Now I have a mental schema for the various scenarios your excerpt covers in case I encounter those too.

[Steve]

I'm told this problem goes away if you go to Data >> Options and toggle Blank value comparison mode. Please confirm.

[dbaum]

As noted in the original post, "I tested in an app with the Blank value comparison mode property set to "Consistent"." See following screenshot of this setting.

Which of the following (or something else) do you mean by "toggle Blank value comparison mode."?

1. The behavior reflected in my test results using "Consistent" mode is not what's intended for "Consistent" mode. Toggling the setting to "Legacy" and then immediately back to "Consistent" will change the behavior to function as designed.
2. "Legacy" mode treats these expressions one way and "Consistent" treats them another way. My test results using "Consistent" mode reflect AppSheet functioning as designed. App creators need to choose which behavior they want (at least until the "Legacy" option is deprecated as noted in the AppSheet help article linked from the original post, which states that Eventually the Legacy mode will be removed.)

[Steve]

Toggle: switch away from the current value, save, switch back, save.

[dbaum]

Switched the app's Blank value comparison mode property to "Legacy", saved the app in the editor, synced the app in its own browser tab, and edited each row to force recalculation of the app formulas. Test results:

Switched the app's Blank value comparison mode property back to "Consistent", saved the app in the editor, synced the app in its own browser tab, and edited each row to force recalculation of the app formulas. Test results:

(Note: In both screenshots, the final column is "ISNOTBLANK(BoolCol)". I couldn't figure out how to widen that rightmost column.)

Here's a copy of the same data from my data source in case that's helpful.

ID	Boolean Value	Label	BoolCol alone	BoolCol eq true	NOT(BoolCol)	BoolCol eq false	NOT (BoolCol eq true)	NOT(NOT(BoolCol))	NOT(BoolCol eq false)	ISBLANK(BoolCol)	ISNOTBLANK(BoolCol)
1	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE
2	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	TRUE
3		(blank)		FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE

Test results are identical between "Legacy" and "Consistent" modes.

In both modes, result of NOT([Boolean Column]) is now TRUE, as opposed to the FALSE result from my original testing. It seems that in using AppSheet, we need to mentally consider the "NOT" function to mean "is not explicitly true".

I started this exercise in order to know what to expect from AppSheet in various situations. Whatever the behavior, I did assume it would be consistent. The one concerning part of all this is that AppSheet seemingly functions differently in "Consistent" mode depending on whether the app has previously been set to "Legacy" mode.

[Joseph_Seddik]

Let's just hope AppSheet's expression interpreter follows the standard 🙂

[Steve]

Escalated.

[Joseph_Seddik]

@Steve What is the problem you see here please? 

[Steve]

NOT([Yes/No]) where [Yes/No] is blank should be TRUE.

NOT(NOT([Yes/No])) where [Yes/No] is blank should be FALSE.

[Joseph_Seddik]

---

@Steve wrote:

NOT([Yes/No]) where [Yes/No] is blank should be TRUE.

NOT(NOT([Yes/No])) where [Yes/No] is blank should be FALSE.

---

@Steve Thanks Steve for your reply.

One question: "Should" based on what rule please? Is this a non-standard AppSheet spec defined somewhere? Unless I'm missing it, this is nowhere in the documentation. Thanks again.

[Steve]

In every other language I've used, a null/blank/empty value is FALSE.

[Joseph_Seddik]

---

@Steve wrote:

In every other language I've used, a null/blank/empty value is FALSE.

---

Exactly! null/blank/empty value should be FALSE.

And this is already the current behavior. So there's no problem.. right?

[SkrOYC]

The OP was about how NOT([Y/N]) was returning False, which should be true

[SkrOYC]

IMHO, If it's null, is converted to false, then the NOT() gives the opposite, true.
And NOT(NOT()) basically returns the same as a null/blank [YN], or the opposite of the prior true, a false.

[Joseph_Seddik]

---

@SkrOYC wrote:

IMHO, If it's null, is converted to false, then the NOT() gives the opposite, true.
And NOT(NOT()) basically returns the same as a null/blank [YN], or the opposite of the prior true, a false.

---

The thing is, it can be really anything we'd like it to be, maybe the more important point is rather to have it clearly documented. 

Now, in the HO of mine 🙂

Null is not converted to FALSE unless it gets through a boolean operator. It's null and remains null. 

• [blankColumn] = null
• NOT([blankColumn]) = false,
        a boolean operator operating on null should give FALSE.
• NOT(NOT([blankColumn])) = true 

This is the current behavior.  

[Joseph_Seddik]

In any case,  just to clarify, I'm not advocating this or that; this is a fringe case anyway and no app creator IMHO (or a developer in general) should blindly base his algorithms/behavior on null boolean values or allow it to happen in the first place. 

Was just trying to understand. Thank you all 🌷

[Joseph_Seddik]

Playing briefly with Node.js:

Welcome to Node.js v15.12.0.
Type ".help" for more information.
> 

undefined:

> let ss
undefined
> 
> Boolean(ss) 
false
> 
> ss ? 'yes' : 'no'
'no'
> 
> ss == true
false
> 
> ss == false 
false
> 

null:

> ss = null
null
> 
> Boolean(ss) 
false
> 
> ss ? 'yes' : 'no'
'no'
> 
> ss == true
false
> 
> ss == false 
false
> 

[dbaum]

Came across some helpful, clear JavaScript documentation--and a great term, "falsy"--related to this overall point.

FYI: Falsy - MDN Web Docs Glossary: Definitions of Web-related terms | MDN. 

[Joseph_Seddik]

Interesting, thanks David!

[dbaum]

For anyone who lands here and wants to suggest changes to current behavior, see this older feature request from @Marc_Dillon, which I just came across for the first time: NOT( [YesNo?] ), to return TRUE, when [YesNo] is b... - Google Cloud Community .