Current User (Slice) | How to conform your app around WHO is using the app

[MultiTech]

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 following:

1. A user table
2. A column (in the User table) that holds the Email the user will use to access the app
3. User sign in required
4. A one-to-one match of records in the User table for each actual user of your app

The User Table

Details

Create a table for your app that will hold information specific to the user. This table must contain at least the following:

1. A column to hold the Email of the person accessing the app
2. An ID column, to uniquely identify that record out of the User table.

My rules for Table Keys

• Always a text column
• Always hidden
• Always UNIQUEID()

Your User table can contain any other information about the user you may need; for example:

• User_Role - to denote if someone’s an Admin, User, etc.
• User_Name - to hold their actual name (to display it in the app)
• User_Assigned_Accounts - a list of the accounts assigned to the user
• User_Hourly_Rate - the hourly rate of the user
• User_Icon - you get the idea…

---

User Sign-in Required

Why?

In order to restrict when certain things should be allowed or not, the app has to have some way of differentiating one user from another. While there is another way to accomplish this, requiring someone to sign in is the simplest of them all.

By requiring people to sign into the app before they can use it, you’re ensuring the when you use the function USEREMAIL() - there will be an email in response, and from this we can base permissions and such.

---

One-to-one match of User Records

Explanation

What I mean by this is that for each user accessing your app, you need to have a corresponding record in the User table for that user.

• For example: if Mary is accessing the app with the email Mary@email.com, then you’ll need to have a record in the User table for Mary with that email in the [User_Login_Email] column (or whatever you name that column).

If you do not have a record for the user, but you’re going to allow users to access the app anyways, then you need to conform your app around that eventuality - handling what happens when someone logs into the app but doesn’t have a User record.

Many of the techniques I’ll describe below (on how to use this system) depends on there only being a single record inside the User table for each user.

• If you have the possibility of users having multiple records in the same table (with the same login in email), then you’ll have to build in a system for the users to “flag” - or somehow otherwise denote - which record they want to use.

---

—| The Setup |—

1. Create a new slice of your User table

2. Inside the condition formula space, add something like this:
   USEREMAIL() = [User_Login_Email]

3. (Optional) Remove the “Add” permissions from this slice (keep edit and delete, if you wish for users to able able to have those permissions for their User record)

---

How do I use this?

Now that you’ve got everything setup and put in place, you’re ready to actually pull information from the Current_User slice and use that in conditions throughout your app.

• To pull a value
  INDEX(Current_User[User_Role], 1)
  ANY(Current_User[User_Name]) - though I suggest INDEX() in preference to ANY()

• To pull a list
  SPLIT(CONCATENATE(Current_User[Related Timesheets]), " , ")
  SPLIT(CONCATENATE(Current_User[User_Assigned_Clients]), " , ")

• 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(CONCATENATE(Current_User[User_Assigned_Clients]), " , "))

---

There are an endless amount of example formulas to give, but the one’s I’ve highlighted above provide instructions on how to handle both values and lists in some common scenarios.

Other Uses

This technique of isolating out a record in a slice can be very helpful when you want to conform the UX around a specific context or situation the user might be in.

• For example: you might create a slice to pull out newly created Timesheets, that lack a “close” time, by the user; this way you could easily create a “Time-clock” type of dashboard, or view, based on the presence of a record inside that Timesheet-isolating slice.

[rmsmeltz]

Thanks again. I can’t get this to work to save my life. The current error I’m receiving is "
Table ‘Companies’ has an invalid update mode expression ‘=IF(INDEX(Current_User[User_Company_Link], 1) = [CompanyID], “ALL_CHANGES”, “READ_ONLY”)’. Unable to find column ‘USER_COMPANY_LINK’

The User’s table is a single ref column to the companies table. I have not tried implementing any of these formulas on the work orders table for the companies, since I cannot get it to work on the companies table yet.

I have also tried changing the companies column to EnumList of refs and using your recommended formula.

I appreciate any help. I have been at this specific problem all week and I feel as though something this simple should not take this long.

[MultiTech]

Hey @rmsmeltz

The problem you’re having is that you’ve just copied the sample formula I provided; you’ll need to change the column names so they match whatever you’ve got in your system.

---

So in the formula, [User_Company_Link] should be replaced with the column name that IS the ref column to the Companies table.

[rmsmeltz]

I tried it that way at first, then I changed the names of my columns and types to match what you showed me in the example so it was easier. It still gave me that error message.

[rmsmeltz]

@MultiTech_Visions I’m watching the extended version of your video on Patreon. I don’t think I have my users and companies tables set up properly. Can you provide any more in-depth guidance on how to make sure they’re set up correctly?

[MultiTech]

Hmmm… at this point it might be best to move this into it’s own discussion, instead of at the bottom of this Tip & Trick post.

[Marc_Dillon]

You can’t specify the Update Mode per record like this. Here you are setting it for the entire Table. If you need to specify it per record beyond this, then do so by filtering down in a Slice, and setting the Update Mode of the Slice.

The expression assistant is not infallible. Sometime the reported error is not actually the real problem. This may be one of the cases. To double check this exact error, just try to set some temp VC expression in some Table to

Current_User[whatever-column-name]

[MultiTech]

You know @Marc_Dillon you’re about not being able to do things on a per-record bases here; I can’t believe I missed that. lol I have no idea what I was thinking.

---

Honestly, I’ve actually taken to controlling edits and things on the Action side of things, I rarely use the actual table-updates permissions anymore.

• You can control EDITS through the edit action
• You can control DELETES through the delete action
• You can control ADDS through the add actions

And with actions, you can control them on a per-record bases.

So I just put my “access” permissions formulas inside the individual actions for each table nowadays.

---

Now to figure out a way to clean up this post! haha

[pravse]

Matt, but please remove the “backdoor” because that is inviting a security hack. Nobody should allow user impersonation that way in their apps. USEREMAIL() is something the platform controls and not hackable. Having a lookup table is not just a backdoor but really a frontdoor also :]

[Ami]

Speeking of hackers, could you do a webinar about how secure appsheet is in terms of cyber security? And ways for us as creators to know what to do to make it safer

[Ferret]

Does "backdoor' refer to creating a slice to return the current user and then using something like ANY(CurrentUser[ID]) to filter or limit the app instead of just relying on USEREMAIL()? If so, how do you suggest handling our data when a user changes their email?

[Ami]

Hi

How do you actualy use it with views/slices? Do you add this condition to any slice with AND()?

[MultiTech]

Keeley_Hooper:

share their email address and password with someone allowing log ins

Yes you can do this, just keep in mind you wont be able to tell who did what - unless you build in some way for each user to differentiate themselves from the other.

[Muhammad_Salooj]

Hi, I’m trying to use this - How do I use this, but with the role column or similar column being an Enumlist, and it should check if a particular value of a role type is in that Column for a user?

I’ve tried tweaking this formula

To restrict something for Admin & Team Leader roles only
IN(INDEX(Current_User[User_Role], 1), list(“Admin”, “Team Leader”))

For example, I’ve tried:

IN(INDEX(Current_User_Slice[user_permissions], 1), list(“all_permissions”, “show_cost_column_table_current_stocksheet”, “edit_cost_column_table_current_stocksheet” ))

I’ve also tried:

IN(INDEX(Current_User_Slice[user_permissions], any), list(“show_cost_column_table_current_stocksheet”, “all_permissions”))

[MultiTech]

@Muhammad_Saloojee When comparing two lists, you have to get a little more clever.

IsNotBlank(Intersect(
  Split(Current_User[User_Roles], " , "),
  List("Option1", "Option2")
))

This will produce true when there are matching elements in each list.

• If no elements match, then the intersect will be blank (because there’s no ‘intersecting’ list items)
• You also have to use Split() to prepare the value pulled from Current_User[User_Roles]
  • Current_User[User_Roles] is a list of an EnumList; so in order for the system to use it, you have to “convert” it back into a ‘true’ list

[Muhammad_Salooj]

Thank you. I’m trying to get it to work… It’s not quite working though… Playing with the spacing between the quotation marks… It’s either hiding them from users who it’s not supposed to hide, or showing them to all users all together when i change the quotation marks… I have the expression written as this:

IsNotBlank(Intersect(

Split(current_user_slice[user_permissions]," , "),

List(“show_cost_column_table_current_stocksheet” , “all_permissions”)

))

As you can deduce, I have: a user table, current user slice, a column in the user table called " USER_PERMISSIONS" instead of user role. In this way i wish to create an easy way for managers to add or remove permissions like how they would with check boxes in other apps.

The user table has a column that i want to allow various different permissions for… For example:

all_permissions , show_cost_column_table_current_stocksheet ,cost_show_current_stocksheet , cost_edit_current_stocksheet , cost_delete_current_stocksheet , cost_edit_current_stocksheet , show_user_table

[MultiTech]

hmmm… @Muhammad_Saloojee Looks like it should work

Perhaps pulling out the value from the Current_User_Slice would work:

IsNotBlank(Intersect(
  Split(INDEX(current_user_slice[user_permissions], 1), " , "),
  List(“show_cost_column_table_current_stocksheet” , “all_permissions”)
))

Aside from that, you might change the SPLIT() delimitator: try just using a comma (no spaces)

[MultiTech]

If you’re pulling a list-of-a-list, and you’re having problems working with the value, a very common troubleshooting step is the following:

• Wrap the initial list pull in concatenate()

SPLIT(CONCATENATE(Current_User[List]), " , ")

---

This first pulls the list-of-a-list, then converts that into a string (via concatenate); then split can process that string to give you your actual list.

[Danielsntgd]

How do I just restrict table data to a specific user once I have a Current_User slice? (What security filter do I use?)

[JPAlpano]

In the table that you want to restrict, you should add a column that includes the email address of the person you want to restrict it to. Make this column hidden.

Then in the security filter of that table, use something like =[THE_EMAIL_COL]= INDEX(CurrentUser[Email]).

Or better yet, bake it toniser settings as described in my next post below.

[JPAlpano]

I have saved many hours of implementing currentUser setup by following what @SkrOYC suggested in the tips and tricks section HERE 

It is combining a currentUser slice with the User Settings.  However, instead of using Initial Values in the User Settings, I use the App Formula instead.

The idea is this, instead of using ANY(currentUser[Column]) or INDEX(currentUser[Column]) to refer to some columns in the current user, do the ff instead.

Create Columns in User Settings for each of Current User slice fields you will use throughout the app. 

For example, if my currentUser slice will have [Emp ID], [Role], and [Email], create those columns in User Settings as well.

Next, assign app formula for each of the User Settings columns as follows:

[Emp ID] uses INDEX(currentUser[Emp ID], 1)

[Role] uses INDEX(currentUser[Role], 1)

[Email] uses INDEX(currentUser[Email], 1)

Now, whenever you need to use it, simply use [_THISUSER].[EMP ID] which is equivalent to using INDEX(currentUser[Emp ID], 1)

if you use the QREW tools extension from AppSheetTraining. com, the autofill will greatly save your time typing vs uaing the ANY() or INDEX() approach. 

[rgrantz123]

Anyone have this or the advanced version setup in a sample app that I can take a look at?  I'm new to AppSheet and having a bit of trouble pulling this off.  I really want to set my current and future apps up so that only users who signup can have access to them.

[Tommy62]

Fürs erste reicht es  dieses einzurichten:

1. Erstellen Sie einen neuen Slice Ihrer User-Tabelle

2. Fügen Sie innerhalb des Bedingungsformelraums Folgendes hinzu:
   USEREMAIL() = [User_Login_Email]

[alexandercox]

I found using this method seems to make my Sync time out. Why might this be happening?

[Gonzague_Issen1]

Dear Matt, 

Security filters are recommended as a way to scale an app, provided that the expression used to filter the rows doesn't destroy performance. I read on that forum that functions like SELECT() or IN() should be avoided. So how about your recommended approach with INDEX()? If I filter one of my tables like that:

OR([Author]=USEREMAIL(), INDEX(Current_User[User_Role], 1) = "Admin")

to give the administrator full visibility on the records, would that affect performance on a large table ?

Thanks