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.
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.