Security Filter OR Expression not working

AppSheet AppSheet Q&A
, ,
1

Hi all

I’m trying to set up a security filter that shows all the rows to our employees, but only shows the rows to employees of other companies, if it has their name in the row.

I’m using the following expression:

OR(LOOKUP(USEREMAIL(),User_DB,eMail,Company)=“Selmoni Infranet”,LOOKUP(USEREMAIL(),User_DB,eMail,Company)=[Company])

It works for all the accounts in the User_DB that have “Selmoni Infranet” in the “Firma” column. But the second “OR” expression doesn’t give me any results, altough I do have matching company names in the Filtered table, and in the User_DB table.

Shouldn’t this expression work?

Thanks in advance.

Adrian

2

try

LOOKUP(USEREMAIL(),User_DB,eMail,Company)=[Company] =>

IN(LOOKUP(USEREMAIL(),User_DB,eMail,Company), tablename here[Company])

3

I tried the following (had to change some names to german):

LOOKUP(USEREMAIL(),User_DB,eMail,Firma)=[Auftraggeber] => IN(LOOKUP(USEREMAIL(),User_DB,eMail,Firma), Objekte[Auftraggeber])

I get the following error:

Expression ‘LOOKUP(USEREMAIL(),User_DB,eMail,Firma)=[Auftraggeber] => IN(LOOKUP(USEREMAIL(),User_DB,eMail,Firma), Objekte[Auftraggeber])’ was unable to be parsed: The given key was not present in the dictionary..

4

Maybe I did not get how your table(s) are structured correctly.

The table you are applying the filter to and the table that contains the allowed company, are they the same?

5

Yes they are. The table i apply the security filter to is called “Objekte”.

I have a column in this “Objekte” table that specifies the “Company”.

Then I have the “User_DB” which contains an E-Mail and the Company the user belongs to. So each user should only see their “Objekte” entries from their Company, except from our employees. They should see every entry.

6

Then try this (Forget IN…)

OR(

LOOKUP(USEREMAIL(),User_DB,eMail,Company)=“Selmoni Infranet”,

LOOKUP(USEREMAIL(),User_DB,eMail,Company)=[_THISROW].[Company]

)

7

My suggestion:

IN(
  LOOKUP(
    USEREMAIL(),
    "User_DB",
    "eMail",
    "Company"
  ),
  LIST(
    "Selmoni Infranet",
    [Company]
  )
)

Also, always quote the second, third, and fourth arguments of LOOKUP().

1 Like
8

@TeeSee1 & @Steve Thank you both for your solutions. I tried both solutions, and none of them didn’t work. I then went into the Enum List in my “User_DB” in AppSheet and realized that I changed some [Company] Names in the Google Sheet, but not in the Enum List. After correcting the Enum List in AppSheet, the formulas started to work. Now, both of your formulas work.

Thanks a lot for your help.

Adrian

1 Like