AND inside a SELECT not working

AppSheet AppSheet Q&A
,
1

Has anyone encountered an issue where in AND() doesn’t seem to work inside a SELECT() function.

Here is a snippet of the condition I am using

IFS(
LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = ‘Admin’,
SELECT(tblemployees[employeeid], TRUE) - SELECT(tbltmtlmapping[teammemberid], TRUE),

LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = ‘HR Admin’,
SELECT(tblemployees[employeeid],
AND(
[businessunitid] = LOOKUP(USEREMAIL(), tblemployees, email_address, businessunitid),
[departmentid] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid)
)) - SELECT(tbltmtlmapping[teammemberid], TRUE),

TRUE,
SELECT(tblemployees[employeeid],
AND(
[businessunitid] = LOOKUP(USEREMAIL(), tblemployees, email_address, businessunitid),
[departmentid] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid)
)) - SELECT(tbltmtlmapping[teammemberid],
AND(
[Team Member Business Unit] = LOOKUP(USEREMAIL(), tblemployees, email_address,businessunitid),
[Team Member Department] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid),TRUE)
)
)

it doesn’t provide any item on the dropdown list, wherein it should be. I tried it on different fields, doesn’t seem to work on my end.

Rhage_0-1702282124730.png
Rhage_0-1702282124730.png716×803 76.8 KB

2

This response is not exactly about the issue you have mentioned in the post. But you could much simplify your expression and get rid of several lookups in the expression and thereby simply it.

Please consider using the pioneering Current User system tip Current User (Slice) | How to conform your app a… - Google Cloud Community given by @MultiTech

Basically please create a slice called say “Current_User” on the table “tblemployees” with a slice filter expression such as

[email_address]= USEREMAIL()

Then your following lookup expression

LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = ‘Admin’

can be reduced to a much simpler

ANY(Current_user[user_type])= “Admin”

Other LOOKUPS() also can be similarly reduced.

2 Likes
3

Thanks for the tips @Suvrutt_Gurjar I’ll definitely give that a try

4

You are welcome.