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])
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โ
)
)
That formula is different than mine? I want to only exclude โit support-corporateโ if USEREMAIL contains store.
Please state the intended logic in plain English.
Ah yes, slightly misread.
Does adding in NOT() make it correct?
AND(
[Department]=[_THISROW].[Department] ,
OR(
NOT( CONTAINS(USEREMAIL(),โstoreโ) ) ,
[L1]<>โit support-corporateโ
)
)
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.
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:
AND(
[Department]=[_THISROW].[Department],
OR(
NOT(CONTAINS(USEREMAIL(),โstoreโ)),
[L1]<>โit support-corporateโ
)
)
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.
@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])
)
)
)
)
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]
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 .
Yah wellโฆ Iโm trying out to be a professional thread de-railer.
User | Count |
---|---|
40 | |
36 | |
33 | |
23 | |
17 |