the filter expression cannot reference the table being filtered.

[jaichith]

Hi,

 I can understand about this issue. whereas I need some work around as a remedy. Kindly suggest.

This error has been occurred after deploying expression given in this post

I have an Idea. 

Creating a separate table for the controlling of  security filter for the  USER  table. But again the separate table has be controlled for security purposes . this will lead to the creation of chain of tables which should be avoided. 

I need solution  for it . Please help 

Thanks 

[AleksiAlkio]

As the error message explains, you can't use the formula reading values from itself. So.. now the question is, what is goal? What are trying to do?

[jaichith]

User Permission Table has the instructions for security filter and for Are updates allowed? Are adds, deletes and updates allowed?.   This is for dynamic user control.  

as said earlier , I have an Idea , Creating a separate table for the controlling of  security filter for the  USER and USER PERMISSION  table. But again the separate table has be controlled and limited for security purposes . this will lead to the creation of chain of tables which should be avoided. this Idea may not be appropriate and becomes clumsy. Is there any other remedy for it ?  @AleksiAlkio  

[AleksiAlkio]

Difficult to say exactly as you have not explained all the details. But.. if you have the role in Users table, you could filter it with the SF and then base of that table, filter the User Permission table based on that role. Could that work?

[jaichith]

I have used Lookup () function in SF. Which goes into 2 tables 

IF(LOOKUP(LOOKUP(USEREMAIL (), USERS", "EMAIL" "USER ROLE), "USER ROLE PERMISSIONS, USER ROLE", "USERS) = "NO ACCESS", FALSE, TRUE)

USERS table has the user role. 

User Role Permission Table has the values "NO ACCESS " , "ALL CHANGES" etc and those values are pulled by using  SF  expression (Either to give access or not to give access )  for the all tables in the app which includes both user table, User Role Permission table too.

I am not going to filter any rows in table instead I am going block access to the table for an user by setting it to FALSE (Check Lookup expression and idea given by @Marc_Dillon in this post) .

Without bringing more changes to existing table structure in USER and USER Permission table I want to set SF expression or something else either give access or not for particular user. 

Note : I wish control all user through app UI and not through traditional table  expression like "admin, manager" = true and "sales person, clerk " = false etc because this app will be  like mini ERP so all controls should be in App UI . 

I hope now you have understood what I want. 

Things What I have done as far as now to improve some extent . Please tell me is it a good remedy or vulnerable

1. Incase If I need to set  no access for  the  two tables then The allowed values is "READ_ONLY"

2. View of the two tables( USER and USER Permission ) has been disabled (FALSE) through UX of particular table view (Showif Constraints )  with the help of  the Lookup expression was given by @Aurelien  in this post 

Thank you

[AleksiAlkio]

There are two different things here to think about.. hiding a view and sensitive data. When the data is sensitive, hiding a view is not a correct way to do it. It just hides the button, not the view itself and for the person who has access to use the app, can have access to see the data. Table permissions and security filters are secured.. views, actions or Usersettings are not. That is something that you need to think first, if you haven't done that already.

As you have the "Users" table already with the email and role, I would first filter (Security filter) it with the email as USEREMAIL()=[Email]. Then you could filter (Security filter) the "User Role & Permissions" table with IN([User Role],Users[Role]). When this is done, you can read what ever permission for other tables with a simple formula like ANY(User Role & Permissions[Dashboard])

[jaichith]

I have small concern in the 2nd Paragraph. 

If I am an ADMIN , if there is SF useremail() = [EMAIl] in USER table , in that case I would be allowed to get a (probably a single row ) data that matches my email id. As being an admin I may be in need to see other users and their roles and I will add or delete an user in the table. 

How to overcome this ? 

[AleksiAlkio]

If you would use a real database, you could create a view with only email and role columns. Then you could bring that view as a table into your app. Because you are using GSheets, you could do this for example with an additinal read_only sheet that reads the rows with the QUERY().. and only those two columns again.

Then use that table to identify the app user's role with the [Email]=USEREMAIL() and use the original "Users" table for other purposes you need in the app.

[AleksiAlkio]

And for the "User Role & Permissions" table you probably need to have an additional "Super Admins" table if you don't want to add emails directly who have access to modify permissions.

[jaichith]

Then where can I put controls for the SUPER ADMIN table ? 

Bro @AleksiAlkio did you read the last part of my recent reply. ? Is it vulnerable ? 

[AleksiAlkio]

As I explained earlier, hiding views, actions etc. is never a secured way to do it.

[SkrOYC]

I would strongly suggest you to implement another method rather than using Select() expressions there (and related ones).

Current User (Slice) | How to conform your app a... - Google Cloud Community
Handy tip for pulling data from UsersTable/Current... - Google Cloud Community

[Suvrutt_Gurjar]

Please do consider simplifying your set up. For example pulling the user permissions in the Users table only and using current user system so that the security filter does not involve any SELECT()  and become a cumbersome,  expensive security filter.

Based on your current set up the security filter coluld be something like 

AND([Email]= USEREMAIL(),

IF (ANY(SELECT(User Role Permissions [Users], [User Role]=[_THISROW].[User Role]))="No Access", FALSE, TRUE)

)

Edit: Not tested. Replying from mobile in transit.

[jaichith]

After Testing:

The above expressions filters the rows that matches the [Email]= USEREMAIL(). I could only rows that match my email id and  not able to  see the other(s) rows even if I am a admin..

If I remove the  part  [Email]= USEREMAIL() in the expression. it leads exposure all rows to all user ( includes admin, clerk, sales person etc).