Re: Security Filter based on row user

nmistry99 · 05-16-2022 02:04 AM

Hi,

I'm currently trying to place a security filter on my app to limit view/capabilities for each user by the user-level.
I have used the following IFS statement:

IFS(User_Level="Super-Admin","VIEW_ONLY",
User_Level="Admin","ALL_CHANGES",
User_Level="User","UPDATE_ONLY")

however I keep receiving the following error message:

Table 'Login NJ' has an invalid security filter '=IFS(User_Level="Super-Admin","VIEW_ONLY", User_Level="Admin","ALL_CHANGES", User_Level="User","UPDATE_ONLY")'. Filter must return true or false.

Is there a way to troubleshoot this? I've also used several other expressions such as SWITCH.

The main aim of the security filter is that upon login, users (by email) with a super-admin will be able to view all data, admins will be able to make all changes and users will be able to make updates only.

I have also attached a screenshot of the user table from google sheets that the app is feeding off.

Any help would be greatly appreciated.

JuneCorpuz

Hello. Security filter is used for filtering out the records for that table. It seems that your formula is for "Are updates allowed?"

This is the correct formula for your "Are updates allowed?" settings

IFS(
	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Super-Admin",
	"READ_ONLY",

	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Admin",
	"ALL_CHANGES",

	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "User",
	"UPDATES_ONLY"
)

For your security filter settings, just copy the code above and just change the results for the IFS() Statement

One more thing, there are no VIEW_ONLY and UPDATE_ONLY values. When you open the expression assistant, you will see this message at the top of your screen.

nmistry99

Hey,

Thanks alot for getting back to me on this. I've inputed the formulae and adjusted "SheetName" to "Login NJ" which is the sheet name in my document.

However I'm still receiving the error that the "Filter must return true or false", which has beeen the case for all other formulas I've inputted. Is there a bug on the app or something to do with my sheets?

Thanks

JuneCorpuz

That formula is for "Are updates allowed?" and not for security filter. Security filter functions just like the slice. What are you trying to filter in that table?

nmistry99

Ah ok I missed that, thanks confirming. For the security filter I'm trying to do the following:

For users that are Super-Admin, they are permitted to view all data on the app once they log on.

For users that are Admin, they are permitted to view all data and edit on the app once they log on.

And for users that are User, they are permitted to view none of the data and only add data on the app once they log on.

I hope that makes sense? There are a fixed number of users who will be using the app, each email is assigned a user level. So essentially I'm trying to create a security filter that restricts the data viewed and/or ability to add data based on the user level.

JuneCorpuz

Apply this in your "Are updates allowed?" settings

IFS(
	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Super-Admin",
	"READ_ONLY",

	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Admin",
	"ALL_CHANGES",

	ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "User",
	"UPDATES_ONLY"
)

Apply this in your "Security Filter" settings

This formula will show all of the data for super-admin/admin user_levelbut will not show any data for other user_level

IF(
	OR(
		ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Super-Admin",
		ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Admin"
	)
	TRUE,
	FALSE
)

nmistry99

Thanks alot, will give this a go. Can 'SheetName' also be a slice that I have created for the User-Level sheet?

JuneCorpuz

Yes

nmistry99

Just tested out the code and works fine for the "Are updates allowed?" which works fine, however have the following error message for the code inputted in the "Security Filter" section:

Have tried adjusting but have had no luck, anyway to troubleshoot this?

Steve

Why did you put it in the security filter?

JuneCorpuz

My bad, I forgot to put thee comma after the OR() statement.

IF(
	OR(
		ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Super-Admin",
		ANY(SELECT(SheetName[User_Level],[Email] = USEREMAIL())) = "Admin"
	),
	TRUE,
	FALSE
)