Handy tip for pulling data from UsersTable/CurrentUsersSlice

[SkrOYC]

I've been doing this to the point I consider it to be reliable.

So many of you have express the need for global variables. This is kind of address buy the usage of a UsersTable where there is exactly one row per user.

This opens the door to many scenarios where you can have advance control over different roles per user, different views each one could see and also a dashboard that shows data from slices that listens to filters related to certain user using a detail view with quick edits.

Now, this have been described many times on this forum and it's something that I apply to all of my apps.

The current scenario:

Let's say you use a slice to just pull the row that belongs to the current user, with a filter similar to [EmailColumnOnUserTable]=USEREMAIL()

Perfect, now you have a slice with just one row, but everytime you want to read or use any of the column values you would need to do something like:
INDEX(CurrentUser[ValueColumn], 1) or ANY(CurrentUser[ValueColumn])
SPLIT(CONCATENATE(CurrentUser[ListColumn]), " , ")

This is because your Slice returns a list, even if it's just one row in that list.

The need:

It works fine if the values are going to be compared with other similar ones. Let's say you have a Ref column (or Enum/EnumList basetype Ref) that serves as a filter to the Customers Slice or something like that, so you can just check if their Key the same (or is inside the EnumList) that you selected as a filter.

But, how do you get the data from a parent record from the CurrentUser slice? You can't use dereference expressions like this INDEX(CurrentUser[RefToTableA], 1).[ColumnFromTableA] or ANY(CurrentUser[RefToTableA]).[ColumnFromTableA]
It just doesn't make sense and won't work.

The solution:

Similar to how I told you about a similar solution using Ref columns on UserSettings, this method makes use of the UserSettings and it's [_THISUSER] expression.

1. Use the UserSettings tab/table

This is mandatory for this to work and to be honest I almost always use it since I discovered it.
If you don't know about it, you can use it to filter your data via Security Filters, to manage UI preferences, Localization and so on. 

2. Let's configure one of the columns for this

Take any column (I tend to use the last one) and tweak it this way:

Config	Description
Column Name	Any name descriptive enough, like "Filters"
Type	Can be Ref or Enum/Ref
Show?	Important, we want this column to be available but the user should not see it. To make it work this way, activate the "Show?" but add an expression that always evaluates to false. I use 0=1 because I'm weird and I like numbers. So an expression that returns false is not the same as unticking the option
Initial Value	Assuming that the way you filter your Users/Filters table is by email, use USEREMAIL() here. You can replace it with any expression that returns the value you use for ownership of the row. Remember this should be a value, not a list
Referenced table name	If you used Ref column type, the system will ask you to point to the Users/Filters table. If you don't and you selected Enum, make sure that BaseType Ref and Referenced table name are configured properly, as well as Valid If

3. How to use it, what's different

Since now you have a ref to the Users/Filters table you can use a dereference expression like any other, which follows the same principle shown on the Ref on UserSettings article.

Retrieve a value from Users/Filters table:

Show More

Before:

INDEX(CurrentUserSlice[ValueColumn], 1)
// or
ANY(CurrentUserSlice[ValueColumn])​

After:

[_THISUSER].[Filters].[ValueColumn]​

Retrieve a list from Users/Filters table:

Show More

Before:

SPLIT(CONCATENATE(CurrentUserSlice[ListColumn]), " , ")​

After:

[_THISUSER].[Filters].[ListColumn]​​

Retrieve a Value from a parent table of the Users/Filters table:

Show More

[_THISUSER].[Filters].[RefColumn].[ValueColumn]​ // For parent
[_THISUSER].[Filters].[RefColumn].[RefColumn].[ValueColumn]​ // For grand-parent
And so on...

It was not possible before without the usage of Select()

Retrieve a List from a parent table of the Users/Filters table:

Show More

[_THISUSER].[Filters].[RefColumn].[ListColumn]​ // For parent
[_THISUSER].[Filters].[RefColumn].[RefColumn].[ListColumn]​ // For grand-parent
And so on...

It was not possible before without the usage of Select()

 4. Does this really work? Is that simple?

Well, yes. Make sure it's configured properly and you use the UserSettings for other stuff or your users will see a blank form view when they click the Settings menu action.

5. Is it reliable? I have many questions...

Well, post them here and also make your own testing!

I'll start with one that I'd consider a FAQ:
Do my users need to open the Settings menu action before for this to work? No, the first time they log in the usersettings will take the Initial Values even if the user doesn't open the Settings form. I've been using Initial Values on UserSettings for other stuff before and that's why I knew this had the potential to work and it worked like charm! (AppFormulas on the other hand seems to not work)

Hope you like it and found it useful!

[SkrOYC]

Hey @MultiTech 

What do you think?

[MultiTech]

This could be handy in certain scenarios, for sure 😃

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

My only problem is that (Security Filters + UserSettings) is what I call a See-Saw bug:

• They're fixed one day, then AppSheet pushes a release and they're broken; then it's fixed, then it's broken.

This doesn't happen so much anymore, but when it does... screeching halt to my app - and I can't have that with mission-critical software systems.  Ya know?

Typically the problem you'll have is that your UserSettings, which your security filters are looking at, will have an initial value such as the ID of the user - but this sometimes doesn't get populated.  Once you open the UserSettings form and save, THEN it's there; it's not supposed to work like that, and initial values should populate right away... but sometimes they don't.

We'll say something, they'll fix it... they'll push a new update and it'll break, we say something.. they fix it... rinse & repeat

I don't utilize setups like this because of this - but maybe these problems don't happen anymore?  @SkrOYC you say you've been using this setup regularly... any issues?

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

Also, the following syntax doesn't work (I wish!!!):

• [Ref].[Ref].[Ref][Related Whatever]

---------------------------------------------------------------------------------------------
I can definitely see how this would clean things up in some instances

[SkrOYC]

My only problem is that (Security Filters + UserSettings) is what I call a See-Saw bug:

• They're fixed one day, then AppSheet pushes a release and they're broken; then it's fixed, then it's broken

I'm with you that we should always care about reliability rather than innovation or simplicity. And I know that some things are changed now and then breaking our apps, just see how Text() was "fixed" to consider user's locale, breaking templates that considered its "broken" way of working.

We'll say something, they'll fix it... they'll push a new update and it'll break, we say something.. they fix it... rinse & repeat

I don't utilize setups like this because of this - but maybe these problems don't happen anymore?  @SkrOYC you say you've been using this setup regularly... any issues?

In my experience UserSettings have been working fine without any hiccup, so I was confident enough to post this tip. You should give it a try and also see it yourself, maybe there are different use cases I'm not considering, Idk! 😉

Also, the following syntax doesn't work (I wish!!!):

• [Ref].[Ref].[Ref][Related Whatever]

Yikes! I was completely sure I've made list dereferences after a chain dereference. I'm going to edit the post removing this so that no one is confused.

Btw, the syntax I mentioned was this:

[Ref].[Ref].[Ref].[Related Whatever][ValueFromWhatever]

[MultiTech]

Man... I was really hoping that I'd missed something, and all this "chaining de-ref with a list-dereference at the end" stuff worked.  😞 

I think someone made a request for this functionality awhile back...

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

The Solution To This Problem

You just have to "prepare" the list of data you need ahead of time, at that last end of your dereference chain.  Meaning, if you need the [Related Users][User_Full_Name] list... you need to create a VC with that - on whatever table you're ending your dereference chain on.

• So from my example above... the [Lease_Company_Link] is the last table in the chain
• On the Company table, where my list of [Related Users] sits, I'd create a new VC with the list dereference formula in it - creating a derivative list.
  [Related Users][User_Full_Name]
• Then my chaining dereference just pulls that secondary column I created (the one that holds my derivative list)

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

Still a handy tip

[SkrOYC]

You just have to "prepare" the list of data you need ahead of time, at that last end of your dereference chain.  Meaning, if you need the [Related Users][User_Full_Name] list... you need to create a VC with that - on whatever table you're ending your dereference chain on.

Yep! That works without problems, I've made that before, maybe that's why I was confused about list dereference after a chain one

---

Still a handy tip

Sure 😊

[MultiTech]

Boo ya! 

[Marc_Dillon]

This is a good tip. Definitely be aware of implementing this into an existing in-use app though, you'd need to force all users to re-save their user settings.

[SkrOYC]

I tested it and it worked even for users that have never opened the Settings form.

It's important that is done properly the first time, all of the show if condition and initial value, so that after our users get their local copy of the app, the app receives the new UserSettings field and applies the Initial Value automatically

[JPAlpano]

I've tested this before and can confirm that USERSETTINGS() always gets the initial value upon initial sync/open of the app and SECURITY FILTERS works well using those initial values.

Great tip!

[SkrOYC]

Thanks for letting us know! For sure that will help others and pushes AppSheet reliability of current features

[Fabian_Weller]

---

@SkrOYC wrote:

Retrieve a list from Users/Filters table:

Show More Before:

SPLIT(CONCATENATE(CurrentUserSlice[ListColumn]), " , ")

---

Thank you so much for this post @SkrOYC I also can confirm, that Initial Values in UserSettings work fine. I use it since 6 months in an App with more than 1000 users without any problem.
Sadly all my 10 columns in UserSettings are already in use so I cannot use this new method.
But I have a question to the "before method".

Your expression is to retrieve a list from the Users Table.
Via an Action I want to add a value to that list.
I saw that this is possible with 
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") + LIST("Value")

But this would add an extra comma to the list. So if it was like:
Value1 , Value2
It will become
Value1 , , , Value2 , Value

So the only solution I found is to subtract the comma.
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") + LIST("Value") - LIST(",")

Can you confirm that this is a good solution, or do you know a better way?

[MultiTech]

---

@Fabian_Weller wrote:

So the only solution I found is to subtract the comma.
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") + LIST("Value") - LIST(",")

---

Oh you're so close! 🙂

Instead of removing a list of commas, remove a list of blank values.

What's actually happening in that scenario is your split has found a whole bunch of blank values, that's what each one of those commas is separating out - a blank value.

So remove the blank values from the list.

This is actually something that I'm finding I have to do more and more. I'm just basically starting to assume every time I use split, I need to remove the blanks out of that list as well. (Unless I'm needing the blank values to be there, for some reason.)

[Fabian_Weller]

Hi @MultiTech I think you mean
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") + LIST("Value") - LIST("")
This was also my first idea. But it's not working.
It will become
Value 1 , , , Value2 , Value

[MultiTech]

What's the original list look like?

[Fabian_Weller]

---

@Fabian_Weller wrote:

But this would add an extra comma to the list. So if it was like:
Value1 , Value2
It will become
Value1 , , , Value2 , Value

---

[SkrOYC]

Can you share the expression assistant test's output of just the SPLIT() part?

[Fabian_Weller]

The column value is:
Value1 , Value2
The expression assistant test's output of just the SPLIT() part
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") is:
Value1 , , , Value2

[ListColumn] is an EnumList.

[SkrOYC]

Can you check this way?:

SPLIT(
 CONCATENATE(
  CurrentUserSlice[ListColumn]-LIST("")
 ),
 " , "
)

[MultiTech]

I think I see what's happening...

Can you confirm that the formula you're using is indeed missing this comma?

[Fabian_Weller]

Shame on me! You are absolutely right @MultiTech I forgot a comma! 😳
Thank you and sorry for this mistake.

Interesting that the expression assistant does not give an error.
Even SPLIT(Hello) will not give an error. Must be a bug?

[MultiTech]

---

@Fabian_Weller wrote:

Hi @MultiTech I think you mean
SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") + LIST("Value") - LIST("")
This was also my first idea. But it's not working.
It will become
Value 1 , , , Value2 , Value

---

Really!?  Wow, I wonder what's happening there then... 🤔

________________________________________________________________________________

Try this, just checking:

• Subtract the list BEFORE you add the value

[Fabian_Weller]

SPLIT(CONCATENATE(CurrentUserSlice[ListColumn])" , ") - LIST("") + LIST("Value")

Gives the same result.

[MultiTech]

---

@Fabian_Weller wrote:

Interesting that the expression assistant does not give an error.
Even SPLIT(Hello) will not give an error. Must be a bug?

---

That thing has been letting SOOOOOO much slop through lately, I'm not surprised at all

[Fabian_Weller]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Expression-Assistant-does-not-show-an-error-for-...

[JPAlpano]

---

@SkrOYC wrote:

Thanks for letting us know! For sure that will help others and pushes AppSheet reliability of current features

---

What I found more reliable is instead of using the Initial Values, I baked it into the app formula instead.

For the benefit of others reading this, 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 expressions this way vs using the ANY() or INDEX() approach. 

[SkrOYC]

---

@JPAlpano wrote:

baked it into the app formula instead

Some of us have found that this is the opposite, sometimes it just doesn't work for us.

---

@JPAlpano wrote:

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

---

I actually suggest to just use one field of the UserSettings and then you can refer to any of your CurrentUser fields by using a chained dereference expression

Dereference expressions - AppSheet Help

---

@SkrOYC wrote:

[_THISUSER].[Filters].[ValueColumn]

---

[JPAlpano]

---

@SkrOYC wrote:

Some of us have found that this is the opposite, sometimes it just doesn't work for us.

I just looked into what I have and found that I have it both as app formula and initial value.

Maybe initial value helps if app formula doesn't kick in? Idk.

---

@SkrOYC wrote:

I actually suggest to just use one field of the UserSettings and then you can refer to any of your CurrentUser fields by using a chained dereference expression

---

Awesome idea. I'll use this! 

[MultiTech]

---

@SkrOYC wrote:

I actually suggest to just use one field of the UserSettings and then you can refer to any of your CurrentUser fields by using a chained dereference expression

Dereference expressions - AppSheet Help

---

@SkrOYC wrote:

[_THISUSER].[Filters].[ValueColumn]

---

I'm giving this a shot!

I'm creating an enhanced dashboard, and have a need to know some derivative information about one of the filter selections.

• My first thought was to create a global variable slice, simply holding the items selected in the filter inside a slice - then I can easily access them anywhere
  • But then I remembered this post, and everyone's comments about how they've been using UserSettings and it's stable now

So I figured I'd give this a go!  (^_^)

____________________________________________________________________________

My use case is a bit complex, but I'll outline it below:

I've got a room booking app I'm working on, and instead of creating an individual time-slot record for each room (holding the information about the available times people can book each individual room for), I wanted to create a master set of time-slots and use these for ALL the rooms.

• But I've also included the ability to include custom time-slots if someone wants
• So I needed a way to see how a room has been setup "Standard" or "Custom" time-slots
  • This is a value inside the room records

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

On my User table, I've got a field for the users to select what room they're currently filtering the dashboard for

• So I needed a way to dereference from that selection, the value for that record's time-slot setting.

SOLUTION

1) I made a Current_User UserSetting

2) From this I then chain deref`ed the value I needed inside my slice formula

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

I could migrate everything inside this formula away from the Current_User (slice), and I likely will eventually.  First I'll run this setup through it's paces, see if I can't break it.

I really like how clean this is though (^_^) 👍

[MultiTech]

And I fell into the trap of using the App Formula space in UserSettings - thinking it would evaluate when the user opened the app.

• Strange that the Initial value calculates, but the app formula doesn't. 🤔

This is an example of the reasons why I generally don't bring this UserSettings feature up; little things like this, where if you weren't more experienced with the platform you might think you were doing something wrong.  When in reality, it's just a peculiarity of the platform that's unintuitive and not working the way you're expecting it to.

• AppSheet is already hard enough to learn, no need throwing them into a feature that's got strange behavior you wouldn't expect. 

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

I'm gonna stick to it though, see what else I can find  😉

[SkrOYC]

Thanks @MultiTech for giving this a go!

I'm sure that the advance use cases you have will be a really good debugging process for this feature.

BTW, I saw you tried with AppFormula, now you changed it to initial value?

---

@MultiTech wrote:

I'm gonna stick to it though, see what else I can find  😉

---

😉

[MultiTech]

---

@SkrOYC wrote:

BTW, I saw you tried with AppFormula, now you changed it to initial value?

---

I started with my formula inside the AppFormula - thinking that would be the space that would dynamically update itself all the time (like a VC or something)

But for whatever reason:

• UserSetting App Formulas are NOT calculated when an app loads
• But Initial Value Formulas ARE calculated

What ever sense that makes.... 😝

You'd think things would be system-standard for calculation behavior

• aka: you need to open the form (or edit the row) for the values to calculate

But the initial value has had some... tweaks made to it over time - so it updates when the app is loading

• This way you can use that for security filters
• But why they didn't extend that functionality to the app formula field... *throws hands up in air*

[SkrOYC]

---

@MultiTech wrote:

But for whatever reason:

• UserSetting App Formulas are NOT calculated when an app loads
• But Initial Value Formulas ARE calculated

What ever sense that makes.... 😝

---

---

@SkrOYC wrote:

the first time they log in the usersettings will take the Initial Values even if the user doesn't open the Settings form. I've been using Initial Values on UserSettings for other stuff before and that's why I knew this had the potential to work and it worked like charm! (AppFormulas on the other hand seems to not work)

---

Yep

[SkrOYC]

Hey budy, I was taking a look at this old comments again. Btw, about this:

---

@MultiTech wrote:

I could migrate everything inside this formula away from the Current_User (slice), and I likely will eventually.  First I'll run this setup through it's paces, see if I can't break it.

---

How is your testing going? Any problems?

Thanks! 😉

[takuya_miyai]

Hi @SkrOYC @MultiTech 

I've missed these important tips until now!
It's a permanent fixture.🤩

[Kawano164]

@SkrOYC Thank you so much for the great tip! It change my messy expression so neat.

But I have one problem. I'd be very appreciated if you give me some advice.

When I use "Preview app as" option in the editor, the usersetting won't be overwritten. It stays as it was

━━━━━━━━━━━━━━━━━━━━

1-1. If I change "Preview app as" from UserA@gmail.com to UserB@gmail.com and press apply, the usersetting filter column won't change.

1-2.When I turned on the show? option of filter column to look into, it stays UserA@gmail.com. But If I change column value manually to UserB@gmail.com the app works as thought.

2. If I enter USEREMAIL() also in the app formula and press save in the blank user setting view (because show? is false), the filter column value is overwritten.

━━━━━━━━━━━━━━━━━━━━

Is this tip can't work on "Preview app as "? Or Do I mistake something?

Here is my Usersetting Filter Column. (I name the filter column "Setting")

[SkrOYC]

---

@Kawano164 wrote:

Is this tip can't work on "Preview app as "? Or Do I mistake something?

---

Technically you are correct. Since you have everything configured properly, it seems like Preview as is just not updating the cache data for the usersettings section, although as far as I remember it should be reseted every time you save the app.
Could you try this?

1. Use a "Preview as" email of your choice
2. Change something in the app and Save that change
3. Check if the user setting is applied for that user

My guess is that "Preview as" doesn't trigger the cache reset for usersettings as Save does

[Kawano164]

Great! The save button works well; the user setting is applied for the changed user automatically.

Thank you very much!