Sanity Check

[Austin]

Quick Sanity check for me please, these 2 statements are functionally the same right? I mean I drew myself a truth table for each which I haven’t drawn since like 4 yrs ago in sophomore yr of college and they are the same. Yet the first didn’t give the same results as the second in my valid if.
They were each inside of the same select statement*

AND([Department]=[_THISROW].[Department],IF(CONTAINS(USEREMAIL(),“store”),[L1]<>“it support-corporate”,true))

IF(CONTAINS(USEREMAIL(),“store”), AND([Department]=[_THISROW].[Department],[L1]<>“it support-corporate”,true),[Department]=[_THISROW].[Department])

[Marc_Dillon]

It seems like they are the same, but only after a brief look. Here’s an alternative:

AND(
  [Department]=[_THISROW].[Department] ,
  OR(
    CONTAINS(USEREMAIL(),“store”) ,
    [L1]<>“it support-corporate”
    )
  )

[Austin]

That formula is different than mine? I want to only exclude “it support-corporate” if USEREMAIL contains store.

[Steve]

Please state the intended logic in plain English.

[Marc_Dillon]

Ah yes, slightly misread.
Does adding in NOT() make it correct?

AND(
  [Department]=[_THISROW].[Department] ,
  OR(
    NOT( CONTAINS(USEREMAIL(),“store”) ) ,
    [L1]<>“it support-corporate”
    )
  )

[Steve]

Note well the following:

• [Department]=[_THISROW].[Department] is TRUE if [Department] is a blank value or the two values are the same.

• [L1]<>“it support-corporate” is TRUE if [L1] is a blank value or if the two values differ.

[Austin]

So we always want to check if [department]=[Thisrow].[Department]
and if the USEREMAIL contains “store” we want to exclude [L1]=“it sup…”

The second formula I posted works just fine, I just couldn’t for the life of me figure out why the first one doesn’t. My expectation now is maybe something to do with blank being true in Appsheet is messing with my thinking of how the logic is working and affected the methods I used to test it.
This is the whole formula for the valid if with my second formula:

[Steve]

Austin_Lambeth:

So we always want to check if [department]=[Thisrow].[Department]
and if the USEREMAIL contains “store” we want to exclude [L1]=“it sup…”

AND(
  [Department]=[_THISROW].[Department],
  OR(
    NOT(CONTAINS(USEREMAIL(),“store”)),
    [L1]<>“it support-corporate”
  )
)

[Marc_Dillon]

We wrote the same expression, must be right!

As for your entire valid_if expression. I think you could actually take advantage of how the equality works if the first term is blank (as Steve just said), to shorten your expression:

SORT( SELECT(
  TicketReasons[L1] ,
  AND(
    [Department]=[_THISROW].[Department] ,
    OR(
      NOT( CONTAINS(USEREMAIL(),“store”) ) ,
      [L1]<>“it support-corporate”
      )
    )
))

Since that first term of the AND() will be true if [Department] is blank.

[LeventK]

@Marc_Dillon
What @Steve wants to emphasize is this:

SORT(
	SELECT(
		TicketReasons[L1] ,
		AND(
			[_THISROW].[Department]=[Department],
			OR(
				NOT(CONTAINS(USEREMAIL(),“store”)),
				NOT(“it support-corporate” = [L1])
			)
		)
	)
)

[Marc_Dillon]

Yah I got that. If you look at Austin’s full valid_if expression, he has a preceding IF( ISBLANK( [Department] ),... ). One corresponding expression possibility, to incorporate that into an expression like mine, without the subjectively unnecessary IF(), would have to include:

OR(
ISNOTBLANK( [Department] ) ,
[_THISROW].[Department = [Department]
)

Or we can take advantage of the odd equality resolution mentioned by Steve where

/blank value/ = “anything”

…always results to TRUE, by re-writing the above OR(), as simply

[Department] = [_THISROW].[Department]

[Austin]

Guys, the second formula I had in the original post works just fine, I was only asking why the first formula wasn’t working since they looked logically the same to me .

[Marc_Dillon]

Yah well… I’m trying out to be a professional thread de-railer.