IF(AND(..)issues

[SABIO_Soporte]

Hi there!
I´m having trouble with an expression that was working perfect.

Expression: IF(AND([Preg21]=“S”,[Preg22]=“S”],[Preg23]=“S”,[Preg24]=“S”,[Preg25]=“S”),“Mes completo”," ")
The condition stops working.

[Steve]

In what way has it stopped working? Is there an error?

Be aware, too, of some non-intuitive behavior of the is-equal-to operator: if the value on the left side is blank, the exression is TRUE regardless of the value on the right side. For instance, if [Preg21] is blank, [Preg21]="S" is TRUE.

[MultiTech]

Steve:

For instance, if [Preg21] is blank, [Preg21]="S" is TRUE.

This has never made sense to me; in my mind the value of “S” is different than “” or even null! If you don’t include an isnotblank() in there it doesn’t work that way, and like Steve said: a blank [Preg23] will result in true just as if it had ACTUALLY met the criteria.

No sense to me.

[Dengue_Evaluati]

Steve:

For instance, if [Preg21] is blank, [Preg21]="S" is TRUE.

Wait, is this true? I use these all the time and they seem to be behaving as I intuited…

[MultiTech]

Indeed.

if you had this scenario:

[Preg23] = blank

that would result in true in your formula

[Steve]

It’s true. Also not intuitive, the opposite isn’t true: "S"=[Preg21] is FALSE if [Preg21] is blank.

[MultiTech]

Steve:

"S"=[Preg21] is FALSE if [Preg21] is blank.

THIS is nice to know. Now I"m going on a formula hunting mission in all my active apps to switch things around.

Thanks @Steve!!!

[Steve]

Confirm first. It’s been a long time since I figured it all out. I may be misrembering.

[MultiTech]

• “New Virtual Column 1” is a simple Y/N
• “New Virtual Column 2” you can’t see - because it contains a formula where if New VC 1 is false, then New VC2 is blank (this gives us our blank field to play with)
• “Column then criteria” shows exactly that, with a formula like this: [Column] = “Value” - looking for a value in New VC 2.
• “Criteria then column” shows it the other way around: “Value” = [Column]

Indeed, if you put an absolute value (or at least something that will never be blank) in the first position, then you can skip having to include an ISNOTBLANK() clause.

[Dengue_Evaluati]

I have around 40 actions and reports that use this specifically, and now I learn it does the opposite of what it does…

I have conditions where I use “if([answer]=“yes”…” where were [answer] blank, I’m expecting ‘false’ to be returned…

I need a walk.

[MultiTech]

Dengue_Evaluation:

I need a walk.

[Steve]

You are not alone. If there was one thing I could fix, this would be it.

[SABIO_Soporte]

Hey @Steve @Dengue_Evaluation @MultiTech_Visions
The ] was creating the error, also in Appsheet editor no error where shown.
I sopused it should show the error in the editor…?

Regarding of all what is in this chain of comments, I agree in what Steve said, but me be it will depend on the result that we are trying to fetch with the formula?
What I looking for is that If all the conditions are “S” is true, that´s it. If one doesn´t mutch “S” is False, and also if any of the are blanck, is also False.
I´m I correct?

[Steve]

SABIO_Soporte:

The ] was creating the error, also in Appsheet editor no error where shown.
I sopused it should show the error in the editor…?

The expression syntax is somewhat loose. In this case, I suspect the parser saw "S"] as if it were "S]". There was no open bracket, so it’s not expecting a close bracket, so treats it as arbitrary literal text.

Note that quotes are not strictly required around literal text values except to differentiate literals from reserved words and syntactic elements. But since there’s no good reference of such special elements, and it’s really too much to expect the average app creator to be aware of such things, it’s safest to quote all literal text as a matter of practice.

SABIO_Soporte:

What I looking for is that If all the conditions are “S” is true, that´s it. If one doesn´t mutch “S” is False, and also if any of the are blanck, is also False.

Try:

IF(
  ("SSSSS" = ([Preg21] & [Preg22] & [Preg23] & [Preg24] & [Preg25])),
  "Mes completo",
  " "
)

[MultiTech]

Steve:

IF(
  ("SSSSS" = ([Preg21] & [Preg22] & [Preg23] & [Preg24] & [Preg25])),
  "Mes completo",
  " "
)

This is really clever.

[SABIO_Soporte]

This is really clever!
If I had orher vales for each column, should work right ?
Eg: If(“SFT12…”=[a]&[b]&…)),”xx”,””zz”)

[MultiTech]

The idea behind @Steve’s formula is to essentially concatenate together all the column values and compare that to a pattern that should match.

So as long as you know the pattern of values you want, then yeah it will work with anything.

[SABIO_Soporte]

[MultiTech]

Steve:

if the value on the left side

What happens if you switch things around??? In an equality operation like this it shouldn’t matter which is first (in regards to math)… so if you switch it to:

“S” = [Preg23]

would that get around it @Steve

[MultiTech]

[Ignacio_Engelb1]

Estas seguro que no será que en:
IF(AND([Preg21]=“S”,[Preg22]=“S”],[Preg23]=“S”,[Preg24]=“S”,[Preg25]=“S”),“Mes completo”," ")
En [Preg22]=“S”], hay un corchete de más,
In the above mentioned condition theres an extra “]” in the formula