Manager View

[Aristotelis_Pap]

Hello,

I have been trying to set up a security filter in my CRM app that combines two expressions, in order to achieve a manager view, where a “manager” user can view both his own and his team’s data. The security filter that I have in place right now is for limiting the users’ view to their own contacts and it works. This expression is the one below (that I found in the AppSheet documentation).

IN([CustomerId], SELECT(CustomersToReps[CustomerId], [SalesRepEmail] = USEREMAIL()))

Instead of using USEREMAIL, I have created a User ID (USERSERIAL), in another table (USERS) that corresponds to it, and the expression turns out like this.

IN([USERSERIAL],SELECT(USERS[USERSERIAL],[Email]=USEREMAIL()))

For the Manager view that I would like to achieve, I have created new columns in my USERS table, one true/false “Manager” column, and a “Team ID” column so that if the email address (USEREMAIL) is a Manager (true), the security filter would filter the data based on Team ID (instead of USERSERIAL). In the opposite case (Manager=false), the data would be filtered normally with the USERSERIAL, so that the user can view only his own data.

So the way I went about this is to nest the first expression into an IF expression (see below), where I can state the two conditions.

IF(IN(USEREMAIL()=USERS[Email],USERS[Manager]=Yes),IN([Team ID],SELECT(USERS[Team ID],[Email]=USEREMAIL())),IN([USERSERIAL],SELECT(USERS[USERSERIAL],[Email]=USEREMAIL())))

The error that I am getting after the check is the following:

Cannot compare Email with List in (USEREMAIL() = USERS[Email])

Could you please help me figure this one out? Am I using the right expressions? Is something like this possible? I would appreciate any sort of guidance towards the right direction.

Best wishes,
Aristotelis