Security Filters for different level of app users

[Ange_Law]

Hi 

I am new to Appsheet and not from a developing background.

I am stuck with security filters for my app. 

I have 3 levels of access

• Super - Full access to the app and all its data
• BSO - Access to edit their data under their useremail as well as view other's data who are in the same department.
• User - only accesses their data, under their useremail but can edit and add.

I tried to follow and training video online which unfortunately did not work. Here is a snap of my user table and data table.

This is the expression I used:

IFS(
ANY(Users[Role])='Super',
TRUE,
ANY(Users[Role])='BSO',
OR(
[Email address]=USEREMAIL(),
AND(
[Email address].[Department]=ANY(Users[Department]),
[Email address].[Role]='User'
)
),
TRUE,
[Email address]=USEREMAIL()
)

Any help would be appreciated. I'm pulling my hair out trying to get it to work.

[romulo_torres]

Hello!

Have you tried using a combination with the function USERROLE() in table definition and security filters ?

https://support.google.com/appsheet/answer/10108128?hl=en

[AleksiAlkio]

I don't see any reason your formula won't work if the Users table is filtered with the useremail and it has only one row after the security filter. Though you can write the formula a little bit easier, but that's not the reason.
OR(
ANY(Users[Role])='Super',
[Email address]=USEREMAIL(),
AND(
[Email address].[Department]=ANY(Users[Department]),
[Email address].[Role]='User'
)
)

[Ange_Law]

Thank you for replying, I have had another go but I have now have the error 

Column Email address in expression '[Email address].[Department]' does not contain a reference.

[Markus_Malessa]

Might need to try it this way:

IFS(
ANY(Users[Role])='Super',
TRUE,
ANY(Users[Role])='BSO',
OR(
[Email address]=USEREMAIL(),
AND(
[Department]=ANY(Users[Department]),
ANY(Users[Role])='User'
)
),
TRUE,
[Email address]=USEREMAIL()
)

[AleksiAlkio]

It means your [Email] column's type is not a Ref or Enum (or EnumList) base of Ref. You can only use deref formula if the column type is one of those.

[Markus_Malessa]

The way I read your question makes me think that you are actually trying to address two separate settings within Appsheet table settings. One is what data do you see, the other is what data can you change. So my question is what are you actually having problems with? I'm going to reach here a little, and say that your "Security Filter" probably works but a user with BSO level can probably still edit data rows even if their email is not in the [Email address] column?

If that is the case then I don't know if Appsheet has a solution yet, as the CRUD 'security' settings are not able to be used at a row level column value according to the documentation. Unless this has changed and I don't know what I'm talking about. If you could clarify what your issue is I think we might be able to help you better understand. See the screenshot below to helpfully point out the differences: