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

@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.

4 Likes

You have no idea… :frowning:

4 Likes

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.

2 Likes

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

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.

2 Likes

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?

1 Like
1 Like

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

2 Likes

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.

4 Likes

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.

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

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!

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.

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

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?

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.

1 Like

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")
2 Likes

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

1 Like

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

3 Likes

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.

1 Like

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.

2 Likes

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.