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

[Aurelien]

HI @Aristotelis_Papadopo

The error is that USERS[Email] produces a list of values, here it will be a list of emails.
Before going further in trying to answer your question, I tried to indent your formula in order to better understand it, but I think something misses here:

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

Moreover, expressions are not built correctly.
For example (just one example):

 IN(USEREMAIL()=USERS[Email],
      USERS[Manager]=Yes
   )

Cannot exists this way, because:
USEREMAIL()=USERS[Email] ==> is incorrect, a value cannot be compared to a list of email
USERS[Manager]=Yes ==> the same, you cannot compare a list of manager to a value.

However, you can use (just an example):
IN(USEREMAIL(),USERS[Email])

Please see:

help.appsheet.com

IN()

Is item in list?

Let us know if that may help and if you still have your issue !
Cheers

[Aristotelis_Pap]

Hello @Aurelien ,

Thank you for your response. The indentation does make it more clear, I think that my problem revolves solely on the condition. I understand that this is not the correct use of the IN expression. Is there another expression that would fit this double IF condition better? Maybe SELECT? I have also tried to not use any expression on the condition, but that doesn’t work either.

[Aurelien · Accepted solution]

I think you can mix IN and AND ?
like:

IF(
   AND(
      IN(value1, list1),
      IN(value2, list2)
   ),
expressionTrue,
expressionFalse
)

[Aristotelis_Pap]

Hi @Aurelien @Steve ,

Thank you very much for the help and suggestions. I tried adding the AND expression and it works!! Thanks again for your time. Wish you all the best.

Aristotelis

[Steve]

This?

OR(
  IN(
    [CustomerId],
    SELECT(
      CustomersToReps[CustomerId],
      ([SalesRepEmail] = USEREMAIL())
    )
  ),
  IN(
    USEREMAIL(),
    SELECT
      Users[Email],
      ([Manager] = "Yes")
    )
  )
)