Usersettings not working

[1minManager]

I’ve read this by @Fabian but I’m not sure its the same issue

USERSETTINGS() Error Questions

Working with USERSETTINGS() can bring Error messages in the expression builder. This may happen when you use a “&” in the Usersettings column name like “Cats&Dogs”. Function 'USERSETTINGS' should have exactly 1 parameters, the name of an option from the user settings Or when you have a column named “Today”. could not be parsed due to exception: #VALUE!. In this case you just have to wrap the column name in double quotation marks like: USERSETTINGS(“Cats&Dogs”) USERSETTINGS(“Today”) @Sant…

I’ve created a TEXT type UserSetting column called User_Type which gets the user permission level with the following set as the App formula

ANY(Select(Staff[Type],[Login Email]=UserEmail()))

I then wanted to use this to create a Show_If for a column with something like these:

UserSettings(“User_Type”)="Retailer"
Errors after save with:
Column Name ‘Job Number’ in Schema ‘Bookings_Schema’ of Column Type ‘Text’ has an invalid Editable_If constraint ‘=UserSettings(“User_Type”)=“Retailer”’. Unable to find column ‘_THISUSER’, did you mean ‘_RowNumber’?

UserSettings(User_Type)="Retailer"
Doesn’t error but doesn’t = TRUE either

UserSettings([User_Type])="Retailer"
Errors in the formula checker with:
Function ‘USERSETTINGS’ should have exactly 1 parameters, the name of an option from the user settings

I then tried UserSettings(User_Type)="Retailer" in a virtual column, which didn’t return a result

So I changed the UserSetting from an App Formula to a Initial Value. Then everything worked

But I don’t want the users messing with this so I hid the UserSetting and now I’m back to:
Column Name ‘Job Number’ in Schema ‘Bookings_Schema’ of Column Type ‘Text’ has an invalid Editable_If constraint ‘=UserSettings(User_Type)=“Retailer”’. Unable to find column ‘_THISUSER’, did you mean ‘_RowNumber’?

Am I missing something? Is there some logic as to why UserSetting formulas have to be both an initial value AND visible to work?

[Suvrutt_Gurjar]

Hi Simon,

This is an interesting issue. I am sure experts will give a proper solution.

Just for our understanding, could you update if possible, any specific reason you are using USERSETTINGS for that staff Type selection? Since the UserSettings(“User_Type”)="Retailer" has an App formula, it must anyway not be selectable by the user?

Can ANY(Select(Staff[Type],[Login Email]=UserEmail()))=“Retailer” expression be simply directly used in the constraints Valid_if and Editable_if of the columns without going to USERSETTINGS?

[1minManager]

Hi @Suvrutt_Gurjar So in the end I’ve fixed this in the short term with an ANY(Select(…)=“Retailer” formula in the Show_IF. But the reason why I was doing it this way is almost all my Apps have some formulas which reference the user type. To control Show_If, Editable_Ifs etc. Some Apps have tens of thousands of rows, and this ANY(Select(… has to be caluculated on them all. So by doing this once in the UserSettings it should reduce sync time.

As for wanting it as a UserSettings App Formula or hidden is just so the users can’t break the app by typing their user type as “foo”. Or worst still typing “admin” when they are not an admin

[Suvrutt_Gurjar]

Oh okay. Got it.

1minManager:

Some Apps have tens of thousands of rows, and this ANY(Select(… has to be calculated on them all. So by doing this once in the UserSettings it should reduce sync time.

This is an interesting use case of USERSETTINGS.

Thank you Simon for the details. Appreciate.

[Steve]

The User Settings columns behave like any other normal (non-virtual) columns: values are only recomputed when the User Settings row is updated. Further, User Settings columns aren’t even given their Initial value value until the user visits and saves the settings the first time.

[SkrOYC]

From my experience, you can have an Initial Value and it will be calculated even if the user doesn’t interact with the Settins view/form.
I used this to make sure all my users has at least one of the Enum selected and it worked, they didn’t even notice when Settings was added to the menu.
Also, you can try that on the Emulator since it defaults to blank every single time you use it. But, if you have an Initial Value it uses that

[1minManager]

@Steve @SkrOYC thanks guys

SkrOYC:

Tried to make something similar and found that App Formula for UserSettings doesn’t work for some reason. Maybe a bug? Maybe they wanted it to be that way?

Glad its not just me. Seems a bit dumb that one would work but not the other. Is App Formula not allowed to stop us using UserSettings as some global variable?

SkrOYC:

Anyway. You can have an Initial Value and uncheck Editable? Option, making that something like AppFormula.

Not sure if that works - it needs to be editable to run the inital value formula. But something like ISBLANK(UserSettings(“User_Type”)) could be used in the editable_if to lock it once its run the first time

Steve:

values are only recomputed when the User Settings row is updated

Not sure this is correct (though it may have been previously). Since the test virtual column could read the UserSetting if it was an inital value but not an app formula. So it must be calculating it on sync.

@Steve Can you copy in someone relevant at Appsheet to answer this as I think its important we are clear on whats allowed, and if not allowed, why? TIA

[SkrOYC]

1minManager:

@Steve Can you copy in someone relevant at Appsheet to answer this as I think its important we are clear on whats allowed, and if not allowed, why? TIA

+1

1minManager:

it needs to be editable to run the inital value formula

Actually, I use that Initial+Uneditable stuff a lot. Before, I was using AppFormulas because I though that was the only way to take a calculated value without allowing the user to make any input/edit and found that Initial+Uneditable was actually posible and it worked (I just tried to replicate your problem minutes ago).

Why I know this

I use this on two big tables with 80 columns each one where just some of the columns are editable and I allow the user to select a row from my main table (the one that connects to everything else). Then, the other columns executes it formula with a lot of reference-dereference expressions. It may sound like a lot, but remember that I did this before with 70~ AppFormulas
BTW, I’m doing this because I have a big template that I send via email using an Adds Only Event and it allows the user to double-check the info that’s going to be on the template before it triggers and helped me to make my template a lot cleaner by just <<XYZ>> the columns from this big table. I made every column name no more that 3 letters.

[Steve]

1minManager:

Can you copy in someone relevant at Appsheet to answer this as I think its important we are clear on whats allowed, and if not allowed, why?

I’ve made the request internally.

[SkrOYC]

1minManager:

So I changed the UserSetting from an App Formula to a Initial Value. Then everything worked

Tried to make something similar and found that App Formula for UserSettings doesn’t work for some reason. Maybe a bug? Maybe they wanted it to be that way?
Anyway. You can have an Initial Value and uncheck Editable? Option, making that something like AppFormula.
BUT, since it’s an Initial Value, it will be the value that’s calculated just the first time AFAIK.

Just my 2 cents

[SkrOYC]

---

@1minManager wrote:

Is App Formula not allowed to stop us using UserSettings as some global variable?

---

Hi Simon, I just wanted to tell you that I posted something that may work as a Global Variable holder kind of scenario.

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

[1minManager]

I'll take a look 🙂