Table of Users to have access with Show if including access to specific rows that contain specific text

[appteam6]

I am currently using this formula to solve the Show if from a table on useremail
IN(USEREMAIL(),SELECT(UserTable[Email],IN([accesslevel], {1, 2})))

What I need as a next step is IN(USEREMAIL(),SELECT(UserTable[Email],IN([accesslevel], {1, 2} Useremail can only view data based on Col3 = specific text in usertable on that email))

If I am doing this for a UX view of a table how can I make it so a useremail can only see only certain rows that contain specific text in those rows. For example useremail can only see rows that column name contains “John” and this is specified inside of the Usertable.

[Marc_Dillon]

Do you know about slices?

help.appsheet.com

Slices: The Essentials

Display only certain rows or columns of your table with slices.

[appteam6]

Not quiet what I am looking for. I would need to create a slice for each user access instead of allow certain users to only see specific rows based on a filter.

[Koichi_Tsuji]

I m not fully following up this post and topic

But you may have user table already? And you have ref connection to your other table?

Then place the user table view as well as target table view onto dahboard with interact mode ON.

User table (deck or table view whatsoever you made) will act like a filter. Once the app user select one person, then the rest of the view will be filtered.

[perissf]

To my understanding, @appteam6 is looking for a way to completely hide data that are not relevant to each user.

help.appsheet.com

Security Filters: The Essentials

You can use Security Filters to control which records a user can see and update.

[Koichi_Tsuji]

He claims Slice does not satisfy his demands, then naturally Security filter is not filing his needs.

[appteam6]

That is correct.

I want to make it so I can create a user email table where each user has access levels to specific UX tables and within that access they are only granted visibility of certain rows inside the UX table.

For example
Test@email.com has access level 2 and inside a specific table they only have access to view the rows where column 5 has their account number. On my access level table I would put in column 1 the user email, column 2 the access level and column three the rows “filter” = Account number”.

This way on any table that has multiple accounts in the table the user email can only see the rows that relate to their account number and only if that specific user has access to those UX tables. I am using the original formula above to gain each useremail access to the UX tables but then after going through the access I need to only allow them to see specific data in that table.

[Marc_Dillon]

Yep, sounds like you need either slices or security filters.

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[appteam6]

Ok so if I have 200 users I need to create 200 slices and every time I add a user I need to create a slice?

[Marc_Dillon]

I would sure hope not. Did you read the linked thread?

[appteam6]

Yes I read it. It basically does exactly what I posted in my first part of this thread. It does not filter down viewable rows to the user. I need to create a slice within a showif formula. =useremail can view UX table but only specific rows of user table that contain their account number.

current formula is IN(USEREMAIL(),SELECT(UserTable[Email],IN([accesslevel], {Admin, GC, Account})))
Then need to add =account can only view rows with lookup of useremail->account number associated with that account useremail. That way when a specific useremail logs in they cannot see every account in the table only the account they are associated with.

We have 200+ accounts and one table. I do not want all 200 users to be able to see every account. I can create slices for each user but how to I create a slice in the showif?

[perissf]

You need to define a Security Filter in your table, where you can select the rows you want to give access to the logged in user.
To give you an idea:

OR(
    IN(USEREMAIL(),SELECT(UserTable[Email],IN([accesslevel], {Admin, GC, Account}))), 
    <<Put here your second condition with LOOKUP formula>>)

[appteam6]

Yeah that does not work either. I would be happy to share the contents and the app if someone wants to help write the formula.

[MultiTech]

appteam6:

It basically does exactly what I posted in my first part of this thread.

appteam6:

I am currently using this formula to solve the Show if from a table on useremail
IN(USEREMAIL(),SELECT(UserTable[Email],IN([accesslevel], {1, 2})))

Not quite.

Your implementation is… heavy handed. That SELECT() formula you’ve got there… that’s what I call “brute force” and you should never really use brute force.

That’s where the slice comes in, it’s an efficient way to accomplish what you’re wanting.

If you read to the end of the post linked above, I talk about how you can use the slice and pull data from it

• this would be for use in security filters, show if formulas, etc.

Current_User (Slice) - How to conform your app around WHO is using the app

• To restrict something for Admin role only
  INDEX(Current_User[User_Role], 1) = "Admin"
• To restrict something for Admin & Team Leader roles only
  IN(INDEX(Current_User[User_Role], 1), list("Admin", "Team Leader"))
• To see if a Client ID is inside the assigned client’s list for the user
  IN([ClientID], SPLIT(Current_User[User_Assigned_Clients], " , "))

---

@appteam6 Here’s a screenshot of a typical security filter for an app I’d build

• First: we check to see what role the Current_User (Slice) holds:
  • if the role is “hospital”
    • check to see if [Hospital_History] contains the [hospital_link] for the current user
  • otherwise:
    • check and see if the [facilites_link] is inside the list of the [admin_facilities] for the current user.

current_user[role] = the assigned role of the user
current_user[hospital_link] = the user’s assigned hospital (if they have the “hospital” role)
current_user[facilites_link] = the assigned facilities to the user (stored as an enumlist inside the user record, only for non-hospital role users)

You might have guessed that this is a security filter for a patient database

If I didn’t use a slice to pull out the current user’s record, I’d have 3 separate LOOKUP()s going in there, and that’s only on 1 table (with 8k records)…

• that’s not including the 5 patient data tables that each contain well over 50k records each (last time I checked… 6 months ago. ha!)

Trying to do a hard select like that is… inadvisable.

[nocewstelcoserv]

Not to be rude for all the users who propose their solution. I totally agree with your methods but this is also the question I have been searching today, I get what the thread poster wants.  What he wants is to restrict the users viewing of UX based on the users role table. For example: 

This table can be used for show if expression on the UX View without any table reference, just the USEREMAIL(). 

@appteam6  I know its been a year but I would love to know what approach you applied for this implementation. Thanks!

[nocewstelcoserv]

I tested the method IN(USEREMAIL(),Table Name[Column Name]) and it WORKS! so Roles/Access Level can be separated via Table it is a waste of table but who cares? it does the trick! LOL!

[StephenMattison]

It does seem like you would benefit from setting up a "Current User Slice" with a Security filter of USEREMAIL()+[Email].  I am using this with great results to pull in user [Access] and User data that you are needing without using complicated & slow SELECT formulas.  I learned some great stuff in this YouTube video:  "Show a User only their data in AppSheet (Custom User Permissions)"   https://youtu.be/2Z2qUTtxNFA