Not getting to work USERSETTINGS()-based translation in dereferenced columns. any ideas?

Dear all

Based on the chosen Language set via usersettings(“Language”), a virtual column should concatenate two columns:

  • the translated name of an item the user has selected from the ref dropdown
  • user text input


USERSETTINGS(“SiteLanguage”) = “DE

User Input:
[User Input] = “Hallo”

Enum Table from which user selects an item in the form column [Select]:

ID° EN DE FR IT Translation**
set1_dog Dog Hund Chien Cane Hund
set1_cat Cat Katze Chat Gato Katze

° key column
** virtual column, used as label.
column formula:

  "DE", [DE],
  "FR", [FR],
  "IT", [IT],

:heavy_check_mark: The “Translation” column is properly used to label the selected item.

:no_entry: However, when trying to access the item’s translated name through dereferencing [Select].[Translation], the fallback value from the switch statement is returned instead of the correct translation [Select].[DE].

:heavy_check_mark:Directly accessing the German translation through dereferencing is working as expected.

Please refer to the screenshot.


How in this case can I access the translated String so that the result of the concatenation example is
Hund: bla instead of Dog: bla?

What result does this give?

LOOKUP([_THISROW].[Select], "Enum", "ID", "Translation")

It returns “Dog” instead of the expected/wanted translation “Hund” ;-(

The tables are setup as follows:

For the sake of testing, the USERSETTINGS(“Language”) is set to a “const formula”:

1 Like

I can’t even imagine what the problem is here! You should probably engage on this one. Please let us know what comes of it.

What I have found out in the meanwhile is that everything is working as expected when the dereferenced value is saved to a real column rather than stored in a virtual column !!

Does that shade some light on it?

green: expected behavior
red: unexpected behavior

So long as the row is updated in a form, it shouldn’t matter.

1 Like

Ok. But in my case it definitely does matter as the virtual column renders the value from the switch’s fallback column.

Will get in touch with the support team and get back here, hopefully with a solution :wink:

1 Like

To make it even funnier: in Form view (right), the correct values are displayed, whereas everywhere else, the fallback values are displayed (e.g. Details view, left):

I imagine this is a problem with the data from UserSettings() not getting where it needs to.

UserSettings is a see-saw type feature - meaning it works one time, then they deploy and it breaks… they fix it, and deploy again and it breaks.

I NEVER use usersettings for anything that’s app-functionality related, specifically because of this nature - you can’t count on that feature working correctly. (You can count on it to break at some point though… :cry: )

I’m curious what happens when you store the language selection data inside a physical table.

  • If you’ve got a Users table, I use that for things like this.

Thanks for sharing your thoughts on this!
Also fancied going in this direction.

Having first time users to create their profile entry in a User table sounds feasible. Or can this be achieved by some action/workflow/automation?

Consequently, I need to substitute:
LOOKUP("Email", "Users", "Email", "Language")
in all expressions.

Is the LOOKUP() function likely to have a significant impact on the calcuation performance?

There are several well defined posts in the community that can direct you in how to do this.

Actually the point of the whole Current_User (Slice) is so you don’t have a 4,000 LOOKUP() running in your app.

Index(Current_User[User_Language], 1)

  • This merely pulls the stored variable, vs. recomputing it over and over. :wink:
1 Like

@MultiTech_Visions , your inputs were awesome! :smiley:

I’ve followed your suggestions and implemented a User table / Current_User (Slice) concept that resulted in a draft for an App Template to handle:

  1. user based access while app is shared “publicly” (no_access, read_only, update…)
  2. provide the app in the users selected language.

As suggested, it does not rely on USERSETTINGS() functionality.
So far everything works flawlessly. Thank you :+1:

Two questions remain:

1) ad:

You are right! Only one thing:

I have an onboarding landing page (one for each scenario).

Scenario c)
In case the user is already registered (=“present in User table”) but hasn’t selected the object of interest (=“blank in User table”), I’d like to have the user directed to the edit form view of his Profile (=1st row of Current_User table)

So far, I am able to define the adequate starting view (i.e. scenario-based landing page) and direct the user to either a Detail View of Current_User (in case the user is registered) or a Form View to create a new entry in RegisteredUsers (in case the user is not registered but legitimate to do so (= present in ‘AllUsers’ table).

What I’d like to achieve is:
in case the user is registered but has not selected his object, instead of having him directed to the Details View of his Profile, I want him being directed to the edit mode of his Profile.

Actually, I somehow need to set the “Finish View” parameter of my onboarding View to something like
LINKTOROW( INDEX(Current_User[Email], 1) , "Current_User_Form" )

But I have no clue how… ;-(

2) ad: performance of IN() function when used for Table Access defintion
Table access for RegisteredUsers table:
My App is supposed to be shared within the domain, but only a limited number of users (populated by an external source) should have the possibility to enroll with READ_ONLY access.

In order to set the Current_User / “is successfully registered” -based Table Access, I use a quite complex formula for the table’s “Are updates allowed?” parameter involving:
IN( USEREMAIL(), AllUsers[Email] )
where AllUsers table has >2000 rows.

Question here is:
what is the best option to check a USEREMAIL() against a whitelist of users in terms of performance?
I assume that IN() has a similar impact on performance as LOOKUP()

Thanks for your thoughts!

Create a new form view atop the Current_User slice with a Row key expression of:

INDEX(Current_User[Email], 1)

Set the Finish view of the onboarding view to that new form view.

1 Like

Yep, IN() and LOOKUP() are going to have similar performance in this case.

We might be able to offer better suggestions if you share the “quite complex formula for the table’s ’ Are updates allowed? ’ parameter”.

1 Like

But those are some of the most efficient of the list-lookup formulas, and the ones you’ll be using.

I can’t think of any other function I would use to “see if an item is inside a list” other than IN().

  • CONTAINS(), but that’s a different use case.
1 Like

Sounds simple, tried to do so, but I got an error saying that the RowKey value does not match the type of the key column?!

Row key expression:


The key column of RegisteredUsers (base table for Current_User Slice) is a Ref column to the AllUsers table, where the key column is of type Email:

Any idea what I am missing here?

Sure, I’m concerned about its performance because of the nested if() and in() expressions:

Background: App is shared domain wide. But only users in AllUsers should be able to access its content.
Concept: give least permissions necessary to the user:

Are updates allowed? ’ expression for RegisteredUsers table:

	USERROLE() = "Admin", 
	"ALL_CHANGES", // an admin
		NOT(IN( USEREMAIL(), AllUsers[Email] )), 
		"READ_ONLY",  // a not whitelisted user
			ISNOTBLANK(INDEX(Current_User[Email], 1)),
			"UPDATES_ONLY", // an already registered user (can only update his record)
				IN( USEREMAIL(), AllUsers[Email] ),
				"ADDS_ONLY",  // a whitelisted user (can only add his profile record)

Are updates allowed? ’ expression for the user input data table:

	USERROLE() = "Admin", 
	"ALL_CHANGES", // an admin
			USEREMAIL() = INDEX(Current_User[Email], 1),
			ISNOTBLANK( Index(Current_User[Site], 1) ), 
			( FIND(USEREMAIL(), Current_User[Editors]) > 0 )
		"ADDS_AND_UPDATES", // a user that is registered and has selected his object (Site), or a user from the object's related Editors list (SiteUsers) table
		"READ_ONLY" // any other user

The rows of the user data table are filtered by the current user’s selected object (site):
[Site] = INDEX(Current_User[Site], 1)

I receive the same error for MINROW("Current_User", "_ROWNUMBER") even though testing the expression yields the email address of the current user correctly

join the results with a blank string et voilà, your suggestion works! :smiley:

INDEX(Current_User[Email], 1) & ""

Thank you very much Steve! :+1:

1 Like

For your first expression…

The second use of IN(USEREMAIL(), AllUsers[Email]) is entirely redundant, as it was already checked and handled the first time.

If the user’s presence in Current_User implies their presence in AllUsers, you could benefit from checking Current_User first to avoid the redundant check of AllUsers

The expression can be condensed using IFS()

  USERROLE() = "Admin",
  ISNOTBLANK(INDEX(Current_User[Email], 1)),
  NOT(IN(USEREMAIL(), AllUsers[Email])),