Global Variables

[MultiTech]

I would love if there was a place, a new place inside the editor (Data > Variables) where I can create X number of global variables for my app.

Literally like a virtual column... for my app (not a table)

Things like:

• in(Context("View"), list("Sites_Dash", "Site_Dash_Control"))
• in(index(Current_User[Security Level], 1), list(
  "Admin",
  "User",
  "Team Leader"
  ))
• Split(Concatenate(Current_User_Selected_Location[Location_Remaining_Items]), " , ")

Every single one of these formulas can be executed from a "global" standpoint.

It would be nice if I could save the results of these formulas somewhere inside the app, which were then available for me to use inside the various show_if/required_if/etc. formula spaces.

• This way instead of having to copy that formula over-and-over, once for each element that needs that logic (potentially causing a problem later on when I update the formula, and forget some place I used it previously), I can just call the global variable.
• Plus I would imagine this would help with efficiency and reducing the amount of "processing" on the device.

UserSettings seems like it would be a use for this - but it's not.  We need a dedicated new mechanism built into the platform (and the apps on devices) to allow for this sort of functionality.

-------------------------------------------------

As always, thanks for considering! 

[SkrOYC]

Yes, I also like the idea of some kind of "UserSetting" account-wise instead of just on one device. I tried to build one and it's just the same thing as the UsersTable, we need a "native" one

[dbaum]

This indeed would be enormously helpful.

FYI, @MultiTech, regarding expressions like the following:

in(index(Current_User[Security Level], 1), list(
"Admin",
"User",
"Team Leader"
))

A mitigating technique I've developed is that I create a role permissions table with a row for each user role defined in my app and a column for each permission I need to define separately (e.g., adds only vs. all changes for a table's Are updates allowed? property; only own rows vs. all rows for a table's security filter; show limited columns vs. show all columns for restricted columns' Show if property). Then, throughout my app I use relatively generic expressions to look up the relevant permission for the current user's role (leveraging your helpful current user approach). So far, with regard to your feature idea, that's no different than what you're doing--it would still be helpful to define an app-level variable. However, what the technique does mitigate is rework stemming from design changes--I just change the data in the table (e.g., change "All Columns" from true to false for an existing role; add a row with a newly defined role) rather than have to change the expressions everywhere throughout the app.

Edit: Subsequent to writing this post, I found that @graham_howe already explained this approach in more detail. See his great implementation explained in Flexible user role management - Google Cloud Community . 

[Steve]

Why not do this with Current_User? Or maybe Current_App?

[JPAlpano]

Hi Steve,

How to implement the Current_App?

[Jonathon]

As @Steve alludes to, if your app has a table with a single row, or a table that security filters down to a single row, you can stick your global variables on that table and fetch them with ANY(table[column]). Two common scenarios for this are: an applications table and current_user (auth) table.

applications table

If you are working in a multi-app environment with an app launcher, you'll have an applications[] table. Outside of the app launcher, you can security filter this table to return the a single row for the active application. The security filter expression can be as simple as CONTEXT("AppName")=[AppName].

Some examples of global app variables could be:

1. An app version number for reporting
2. The appsheet app-id / app link
3. Static images / SVGs

current_user (auth) slice/table

The concept is the same as with the applications table example above, except you are querying a slice with a single row instead of a table. E.g. ANY(current_user[id]). This method is optimal for the vast majority of appsheet scenarios. @MultiTech provides a good breakdown on the current_user slice method in this thread. There are 2 drawbacks to be aware of that are relevant to this topic:

1. If you want user-specific globals, the virtual columns will calculate on every user row
2. You can't security filter a table against itself, which is relevant in cases where users should not have access to the full user list (e.g. multi-tenant applications). 

A usersRoles junction table can get around P2 by first grabbing the list of usersRoles against USEREMAIL(), and next filtering down the users table from the information in usersRoles. If you go this route, you will need to include an [email] column on your usersRoles jct - this should be in addition to a true UUID/fkey. You are also chained to filtering against a usersRoles table (or equivalent) for every multi-user app - an annoying requirement when not every app is complex enough to warrant roles.

Another option, and the one I use for my multi-tenant scenarios, is to make a view of the users table that contains (at least) the primary key [UUID] and [email] columns. Add this as a new table to the app, and security filter on [email]=USEREMAIL(). I call this my auth table which, aside from being a table instead of a slice, is functionally identical to the slice method. And then you can do cool things like security filter users down to a specific company with [company_id] = ANY(auth[company_id]). And you can store your user constants on the auth table and there is only 1 row to calculate.

So, if the above points 1 and 2 are relevant to you, then consider making your current_user slice an actual table (can calculate/update itself in SQL as a view, or in Sheets with a QUERY() function). If points 1 and 2 aren't relevant to you, then the slice method is probably a better choice.

[SkrOYC]

Just droping this here, which helped me to manage my UsersTable withouth converting list to values

Handy tip for pulling data from UsersTable/Current... - Google Cloud Community

[MultiTech]

---

@Jonathon wrote:

• If you want user-specific globals, the virtual columns will calculate on every user row

---

There's actually a handy trick to making this current-user specific:

• wrap your whole formula in an IFS() checking to see if the UserID = the current users.

Ifs([UserID] = Index(Current_User[UserID], 1),
  your_formula_here
)

I've been experimenting with some clever things with VCs and trying to reduce the calculation loads over time.  This is one of those tricks.

-----------------------------------------------------------------------------------------------------------

All and all though @Jonathon , not a bad method of doing things.

• Do you face any issues with lag between updates between the actual user table  and the current_user table you've made?
    - Because the current_user table is just "importing" the row from the user table right?  So any changes have to go through the user table first, then they'll populate into your current_user table....
  
  
• How do you handle those?

[Jonathon]

---

@MultiTech wrote:

• Do you face any issues with lag between updates between the actual user table  and the current_user table you've made?
    - Because the current_user table is just "importing" the row from the user table right?  So any changes have to go through the user table first, then they'll populate into your current_user table....
  
  
• How do you handle those?

---

In the simplest case, the auth table is readonly and no views are ever built or exposed for it. It simply exists as a filter. Edits to a users record could still occur through the users table.

For my workflow, users create their accounts in AWS Cognito, which sends a webhook to appsheet to initialize their matching user record. The process takes <5 seconds from the time they hit login till the time the app launcher shows their user-specific applications, and in over a year of using the system I haven't had a case where the user loaded into the app before their auth record was available. And in this case, the auth table is much more complex as its aggregating data from usersRoles, usersApps, and usersOrgs junction tables.

With the above setup, you can have a multi-tenant application, user-specific app permissions and roles, user-based row-level security against other users records, and user self-signup. And you get all of that functionality through a single auth table - you don't need to add all of the junction tables into every subapp you may have, you can just have a single user management app where that's all handled.

@dbaum this might be relevant to what you've been working on.

[dbaum]

Yes, thanks, @Jonathon, I was already following this thread, including your prior entry, with interest. Love the elegance of an auth table.

In my main app, I have multiple tenants and each tenant manages their own user list separately. I need to account for the scenario where multiple tenants include the same user unbeknownst to each other--including potentially assigning, say, different roles to the user within their respective tenant. Abstracting out usersRoles and usersOrgs, all segregated from usersAuth is great. One of my first questions to this helpful community was about this point, and I landed on a novice, partial version of that technique. I'm definitely looking forward to finishing my updated implementation that you helped me with re Cognito.

[Markus_Malessa]

I for one like the idea of being able to declare global variables and setting/updating values related to these variables as long as it can be handled in an efficient way. With that I am specifically referring to how user_settings are handled for example, which if a user has the ability to update or set a user_setting value it forces a sync (rather than a background sync), which in my opinion is very inefficient especially if you are running the app on a mobile device in an area where connectivity is less than desirable. That is why I created a quasi user settings table that is stored in a Google Sheet, however even under this scenario an update to a user setting causes a transaction and therefore a sync change, which in this case can be handled in the background based on the sync settings within the app. However, even in this scenario I believe that this is still not the most efficient way.

Enter 'global variables'. I would imagine the ability to set global variables that can be set/updated based on a selected row within a table might present the most efficient way because it would be a purely programmatic change that could still be based on common AppSheet formulas and/or even provide the option to set/update these variables through form entry. I suspect that maybe the internal AppSheet database might be able to present a somewhat hybrid solution to this conundrum, but even then I would imagine that the global variables would mostly need to be specific to the app runtime/specific user so ultimately it would likely result in having to constantly adding/updating/removing columns or rows.

I for one voted for this feature idea, because I don't see how in modern app development you can get by without global variables of some sort.

[Markus_Malessa]

I forgot to add that in most of my use cases global variables have a very high probability to replace many virtual columns, thereby also enhancing overall app performance.