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.

[MultiTech]

I’ve seen at least a dozen posts in the community in the past week about how to do something like this, and surprisingly there hasn’t been a devoted post about this yet - despite this probly being THE most common system people put in place in their apps.

So here it is - for easy reference from now on.

Quick Poll

Do you have a Current_User (Slice) - or the equivalent?

• Yes
• No
• No (but I will now!)

0 voters

[dbaum]

Yes, already had a Current User slice. Thanks for the tip to use INDEX for this single-row slice--I had been using LOOKUP.

[MultiTech]

Advance Version - Emulate User

A phrase you might hear in conjunction with “hackers” or “programming,” is the idea of a “back door.”

en.wikipedia.org

Backdoor (computing)

A backdoor is a typically covert method of bypassing normal authentication or encryption in a computer, product, embedded device (e.g. a home router), or its embodiment (e.g. part of a cryptosystem, algorithm, chipset, or even a "homunculus computer" —a tiny computer-within-a-computer such as that found in Intel's AMT technology). Backdoors are most often used for securing remote access to a computer, or obtaining access to plaintext in cryptographic systems. From there it may be used to ga A bac...

Many times, users will email me complaining about the app behaving in a strange way - yet when I try and test it out it works just fine for me.

To help me troubleshoot problems like this, I’ve now taken to building in a back door that allows me to hijack the app and manually tell it who is using the app.

---

—| The Setup |—

1. Create a new UserSetting

   • Name: Current_Selected_User
   • Type: Ref to the User table
   • Initial Value: LOOKUP(USEREMAIL(), Users, User_Login_Email, UserID)
   • Show If: LOOKUP(USEREMAIL(), Users, User_Login_Email, User_Role) = “Admin”
   • Description: “Select the user you wish to emulate using the app”

2. Change the condition formula for the Current_User slice

   • [UserID] = UserSettings(Current_Selected_User)

---

What this does is allow someone that’s got the “Admin” role to see (and be able to edit) this UserSetting.

The UserSetting defaults itself to the ID of the user record that matches the email the person is using to login; this value is then used by the Current_User slice to pull out their corresponding record.

• Since the UserSetting defaults to the ID that matches the user’s USEREMAIL(), and the Current_User slice defaults to what the UserSetting says, end users see no change in functionality.

But admins have the ability to modify this field; and instead of it defaulting to THEIR [UserID], they can manually select the ID they wish to use - giving them the ability to “emulate” using the app as another user.

---

This advanced version of the Current_User slice technique has really helped me with troubleshooting problems specific to users.

App on my friends!

[Rafael_ANEIC-PY]

Amazing info as always Matt, thank you for sharing !

The backdoor you mentioned works similarly to the “preview as” function built into the app editor or does it provide better information when you test on it?

[MultiTech]

Rafael_ANEIC-PY:

Does the backdoor you mentioned work similarly to the “preview as” function built into the app editor; or does it provide better information when you test on it?

It’s similar in function to the “preview app as” in the editor - but this is baked into the app.

The app LITERALLY believes it’s being accessed by that user - in regards to anything that you’ve built out using the Current_User slice.

USEREMAIL() will still return whatever email the user is ACTUALLY using - or whatever you’ve entered in the “preview app as” space.

[elco]

This relies on the fact that current user in the Users table because of LOOKUP(USEREMAIL(), Users, User_Login_Email, UserID).

So have you figured out a way to force user to create the entry when they first log in because if you show them a form they can just ‘Cancel’ it and row won’t be created.

Or have you figured out a way to restrict access to everything until they do create their row in Users table?

[Steve]

FAQ: How do I do something automatically the first time someone uses my app? Tips & Tricks 💡

How do I setup/register a new app user automatically? How can I do something automatically the first time a user uses the app? This example assumes your app requires sign-in, and that you have a table named Users, each row of which describes a single user of your app. Such a table is a common way to store information about your app users and as an alternative to User Settings to store their individual app settings. The Users table is assumed to have a column named Email to store the email addr…

[elco]

Thanks @Steve I saw that and it still has the same issue that is users can press ‘Cancel’ and row is not created.

[Steve]

There’s no sure-fire way to prevent the user from canceling out of a form.

In order to force a User table entry for a new user, the user must do something with the app that will trigger an action. The action can then add the current user to the Users table. The problem is that the main menu entries and the navigation buttons along the bottom of the screen can’t trigger actions, so you have to present a view to the user that forces the user to either save a form (which can trigger an action) or press an action button. Action buttons can’t be placed on forms; all other view types require at least one pre-existing row in a table. Whether you use a form or a view atop some pre-existing row, you still have to prevent the user from navigating away from the view, which means removing all main menu and navigation bar entries. There’s no simple way to do any of that.

[elco]

Thanks for that explanation. It helps a lot. Then would how does this work? When is the initial value of the User Setting resolved? We need it to be resolved after the User exists in the table.

And can we request a setting that tells if you can reject the open form? Simple enough to implement. This would be useful in many other places.

[Steve]

elco:

When is the initial value of the User Setting resolved?

User settings is handled like any other table: the user settings row doesn’t exist until the user goes into it and saves it.

elco:

We need it to be resolved after the User exists in the table.

If you’re going to have a Users table, best to use it rather than use user settings at all.

elco:

And can we request a setting that tells if you can reject the open form?

Event Action: Form Cancelled Feature Requests

Just like we have Form Saved Event action, is it possible to have Form Cancelled Event action as well? Thanks for considering.

[Keeley_Hooper]

Hi,

Thank you so much for this. Will this still allow someone to share their email address and password with someone allowing log ins on one account from multiple users?

Thanks so much.
Keelet

[Marc_Dillon]

Thanks for taking the time to write this out!

[Grant_Stead]

Why do you prefer index?

[MultiTech]

It’s been my experience that ANY() can sometimes remove the “meta data” of a column; I’m talking about the deep layered data that AppSheet keeps internally that we can’t see. Stuff like:

• Referenced table Key column
• Referenced key column type

But also stuff we can actually set:

• EnumList base type
• Enum base type ref table

In the past when I would use ANY(), sometimes this information would be lost and I would loose the reference nature of the data or the base type of what we’re working with.

INDEX(), however, has never had this problem - in fact it seems to retain the type of the data you pull in no matter what.

[GreenFlux]

MultiTech_Visions:

In the past when I would use ANY(), sometimes this information would be lost and I would loose the reference nature of the data or the base type of what we’re working with.

INDEX(), however, has never had this problem - in fact it seems to retain the type of the data you pull in no matter what.

Now that’s a good tip! I saw that limitation with ANY() before, but didn’t realize there was a way around it with INDEX()! Great find, @MultiTech_Visions!

[GreenFlux]

Wait, it wasn’t ANY()-- I saw this same limitation with ORDERBY().

Release Notes: 7/17/20 Feature Release Notes

Thank you for this! I’m just now getting a chance to experiment with it. We can now dereference an ORDERBY list of Refs to get other values from that row and sort by any column. Awesome! However, I’ve noticed you can not dereference the ORDERBY() expression directly. The ordered Ref-list has to be stored to a Virtual Column first, then that column can be dereferenced to a new list. ORDERBY(<<LIST_EXPRESSION_OF_REFS>>)[Column] does not work, but [OrderedRefs_VC]= ORDERBY(<<LIST_EXPRESSION_O…

[Grant_Stead]

MultiTech_Visions:

INDEX(), however, has never had this problem

Interesting - You should drop this as a tip in of itself.

[Lucinda_Mason]

Wow! Now I need to check where I use ANY() - which is a ton and see if I should switch to Index.

[Fabian_Weller]

Wouldn’t it be better to report this as a bug, instead of changing every ANY() expression to INDEX() ?

[MultiTech]

The real problem is that it WAS fixed at one point, then broke; then was fixed again, then broke.

I can’t build around something like that for a paying client; there’s few things on the platform that see-saw like this - this just happened to be one of them.

[Koichi_Tsuji]

Mate, i fed up with all the bugs, unexpected behavior of Appsheet. I m so tired… to struggle with it.
My clients claiming and jump over to me (Off course) … again. I fed up.

I go sleep and forget all now…

Something is going wrong with Appsheet, as it did not happen before Google era. I simply hope everyging goes back before the time before Google aquires. Honestly, as things obviously gets worse and worse day by day.

[MultiTech]

tsuji_koichi:

as it did not happen before Google era

That’s not true; it’s always been like this. Part of the territory of a SaaS that deploys multiple updates a day.

tsuji_koichi:

I’m so tired… to struggle with it.

I hear you; that’s why I typically steer clear of any of the “newer” features, they will have tons of bugs that need to be rolled out. It’s maybe about a year before things stabilize to where I feel okay using it in “production” apps.

And when it comes to things like this ANY() situation we’re talking about: once I identify something that’s oscillating like that, I’ll find a solid work around that I can rely on. Such as the INDEX() thing.

[Koichi_Tsuji]

I respect you and your opinion, but it is my own opinon.

I simply so tired now.

[lamontfr]

@MultiTech_Visions @tsuji_koichi

I agree that I sometimes find inconsistencies in the behavior of Expressions. When this happens, I have to spend hours to narrow down the problem and then look for an alternative method to get the result I need. This is very frustrating.

I think that the AppSheet Expression interpreter code needs refactoring.

[Steve]

lamontfr:

I think that the AppSheet Expression interpreter code needs refactoring.

You have no idea…

[HBT]

Yes, you are right about that. I also [ ]forget these signs. The expression interpreter does not see this error. But first I do the complex expressions I want to do in Excel. Then I make shorter alternatives to the expressions I wrote in Excel. I convert the expressions I get at the end into Appsheet expressions. In this way, I prepare my expressions faster.

[Jonathan_S]

@praveen See above. I tend to agree. I know this is part of the growing process but the level of attention that has been needed over the past months on apps that have worked forever that are broken or broken features is not good.

Do you see any improvement on this? Does google not have internal testing? or the level of testing that appsheet had in the past?

[Fabian_Weller]

MultiTech_Visions:

• Create a new slice of your User table
• Inside the condition formula space, add something like this:
  USEREMAIL() = [User_Login_Email]

Hi Matt, why don’t you use Security Filter for that?

[MultiTech]

Because then the Users table is no longer the Users table - it’s more a single user table.

If I put a security filter on the Users table, then I won’t have any of the other Users data in the system - admin users would have problems with that.

[dbaum]

FWIW, I'm trying to do both. I apply a security filter on my Users table that depends on the role--just the user's row(s) if they're only a standard user but all users for the tenant if they're an admin (or me!).

IF(OR(USEREMAIL() = CONTEXT(OwnerEmail), [Role] = "Admin"), OR([Tenant ID] = USERSETTINGS("Tenant ID"), [Email] = USEREMAIL()), [Email] = USEREMAIL())

[elco]

Discovering this is like discovering the power of pointers in C!

Honestly this should be part of any new app created, because using this method requires you don’t use USEREMAIL(), USERROLE(), USERNAME() instead use UserSettings(Current_Selected_User).[Email] ...[Role]. No one will design with this in mind and will have to replace all usages.

[elco]

Steve:

If you’re going to have a Users table, best to use it rather than use user settings at all.

But we need to use UserSettings(Current User) everywhere in the app instead of USEREMAIL()/USERROLE() value to do this Tip/Trick!

elco:

Honestly this should be part of any new app created, because using this method requires you don’t use USEREMAIL(), USERROLE(), USERNAME() instead use UserSettings(Current_Selected_User).[Email] ...[Role] . No one will design with this in mind and will have to replace all usages.

Event Action: Form Cancelled Feature Requests

Just like we have Form Saved Event action, is it possible to have Form Cancelled Event action as well? Thanks for considering.

I don’t think that’s the same feature. That simply wants ‘Cancelled’ Action. I want a ‘Show If’ for ‘Cancel’ button when you’re in form view so you cannot go back. You have to fill it and finish saving.

[Steve]

elco:

But we need to use UserSettings(Current User) everywhere in the app instead of USEREMAIL()/USERROLE() value to do this Tip/Trick!

Ah. That’s a question for @MultiTech_Visions, then.

[Tincuskan]

Very good strategy. Unfortunately I am very new to Appsheet and I would like to ask you if you have an example “Mini-App” that could show this implementation for reference. It’s possible?

[rmsmeltz]

So I am trying this method because it seems better than the way I had it set up. The difficulty I’m having is I want any user logged in to be able to view and edit their rows based off of the associated company.

Example: David & John both work for ABC, LLC. They should both be able to view and edit records for ABC, LLC.

I have followed all the steps above and can’t seem to get the result I’m looking for. Any guidance is appreciated.

[MultiTech]

rmsmeltz:

Example: David & John both work for ABC, LLC. They should both be able to view and edit records for ABC, LLC.

It seems to me that if you used a security filter to only show that companies data in the app, then you wouldn’t have to restrict add/edit/delete permissions.

• Because then, the data shown in the app is only for that specific company.

---

But if you wanted to control who can do what, you’ll either need to:

• use a “Role” based system (ie. “Admin” roles can edit things, but “Users” can’t); or
• control things on the individual actions.

---

If using a role-based system

You can use an Update-Permissions formula on the Companies table (inside the “Are updates allowed?” space) like the following:

switch(index(Current_User[User_Role], 1),
	"Admin", "ALL_CHANGES",
"READ_ONLY")

---

If you wanted to control things through Actions

Each individual operation that can be executed on a table (add/edit/delete) is controlled through an action for that table; and you can base whether any action should be seen or not based on data from individual records.

• So for the “Edit” action on the Companies table, you could do something like the following:
  INDEX(Current_User[User_Company_Link], 1) = [CompanyID]

• You might also throw something in there about only allowing “Admin” types as well:

AND(
  INDEX(Current_User[User_Role], 1) = "Admin", 
  INDEX(Current_User[User_Company_Link], 1) = [CompanyID]
)

---

NOTE: I originally posted an incorrect statement, which caused confusion and the comments after this one are based on that erroneous info. The previous details can be found here.

Then inside the “Are updates allowed?” space for the Companies table…

…use a formula such as this:

For a single ref column

IF(INDEX(Current_User[User_Company_Link], 1) = [CompanyID], 
  "ALL_CHANGES", 
"READ_ONLY")

For an EnumList of refs

IF(IN([CompanyID], SPLIT(CONCATENATE(Current_User[User_Companies]), " , ")),
  "ALL_CHANGES", 
"READ_ONLY")

---

I might also include something about only allowing Admins, if you use a Role-base permissions system:

IF(AND(
  INDEX(Current_User[User_Role], 1) = "Admin", 
  INDEX(Current_User[User_Company_Link], 1) = [CompanyID]
),
  "ALL_CHANGES", 
"READ_ONLY")

[rmsmeltz]

Thanks! I will try implementing this and see if I can make it work.

[MultiTech]

You might also find some helpful tips from this post as well:

How to use Security Filters to allow multiple companies to use the same app Tips & Tricks 💡

Let’s say you’ve got an app, and you want to allow multiple companies to access and use the same app. The primary benefit here is that, as a developer, you only have one app to support. If you were to create a copy of said app, one for each additional company that wanted to use it, then you’d have to support all those apps. Any changes made to functionality (column structure, views, actions, automation, etc.) will need to be duplicated on each additional app. This then translates to ho…