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

[pawann]

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

Example:

Usersettings:
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:

SWITCH(
  USERSETTINGS("SiteLanguage"),
  "DE", [DE],
  "FR", [FR],
  "IT", [IT],
  [EN]
)

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

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

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

Please refer to the screenshot.

Question:

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?

[Steve]

What result does this give?

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

[pawann]

Steve:

What result does this give?

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”:

[Steve]

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

[pawann]

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

[Steve]

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

[pawann]

Steve:

it shouldn’t matter

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

[pawann]

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):

[MultiTech]

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… )

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.

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[pawann]

MultiTech_Visions:

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:
USERSETTINGS("Language")
by
LOOKUP("Email", "Users", "Email", "Language")
in all expressions.

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

[MultiTech]

pawann:

Having first time users to create their profile entry in a User table sounds feasible.

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

pawann:

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

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.

[pawann]

@MultiTech_Visions , your inputs were awesome!

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

Two questions remain:

1) ad:

MultiTech_Visions:

several well defined posts

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!

help.appsheet.com

LINKTOROW()

Constructed deep link to row

[Steve]

pawann:

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

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.

[pawann]

Steve:

INDEX(Current_User[Email], 1)

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:

Error:

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?

[pawann]

Steve:

INDEX(Current_User[Email], 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

[pawann]

Steve:

INDEX(Current_User[Email], 1)

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

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

Thank you very much Steve!

[Steve]

pawann:

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()

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

[MultiTech]

Steve:

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

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.

[pawann]

Steve:

share the “quite complex formula

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:

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

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

IF( 
	USERROLE() = "Admin", 
	"ALL_CHANGES", // an admin
	IF(	
		AND( 
			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)

[Steve]

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()

IFS(
  USERROLE() = "Admin",
    "ALL_CHANGES",
  ISNOTBLANK(INDEX(Current_User[Email], 1)),
    "UPDATES_ONLY",
  NOT(IN(USEREMAIL(), AllUsers[Email])),
    "READ_ONLY",
  TRUE,
    "ADDS_ONLY",
)

[pawann]

Steve:

The expression can be condensed using IFS()

I totally agree with you. Thank you!

[Steve]

For your second expression…

IF(
  USERROLE() = "Admin",
    "ALL_CHANGES",
  AND(
    USEREMAIL() = INDEX(Current_User[Email], 1),
    ISNOTBLANK(INDEX(Current_User[Site], 1)),
    (FIND(USEREMAIL(), Current_User[Editors]) > 0)
  ),
    "ADDS_AND_UPDATES",
  TRUE,
    "READ_ONLY"
)

What are you trying to accomplish with this?

(FIND(USEREMAIL(), Current_User[Editors]) > 0)

[pawann]

Steve:

What are you trying to accomplish with this?

(FIND(USEREMAIL(), Current_User[Editors]) > 0)

For each site, there is a responsible person [Site].[User1]. This person can register SiteUsers. All these users should be allowed to add and update the data table. All others (except Admin) shall have read_only access.

Concept: Concatenate the [Site].[User1] and relevant SiteUsers[Email] into one String Current_User[Editors] by:

CONCATENATE(
UNIQUE(
SPLIT(CONCATENATE([Site].[User1]), “,”) +
SPLIT(
	CONCATENATE(
		SELECT( SiteUsers[Email], [Site] = [_THISROW].[Site], TRUE)
	),
",")
)
)

… and check whether the USEREMAIL() is found in this string.

Do you have any concerns about this approach?

PS: until now, no information from the support team on how to tackle the actual problem USERSETTINGS() not being retrieved properly. ;-(

[Steve]

FIND() is case-sensitive, so if (for instance) the stored email address is lowercase (e.g., a@example.com), but the address provided by USEREMAIL() is uppercase (e.g., A@EXAMPLE.COM), FIND() won’t find it. Also, using FIND() (or CONTAINS(), as some people do), doesn’t match the complete address. For instance, if the stored address is feba@example.com and USEREMAIL() gives a@example.com, that’s a match, but probably shouldn’t be.

My suggestion:

ISNOTBLANK(
  FILTER(
    "Current_User",
    IN(USEREMAIL(), [Editors])
  )
)

See also:

help.appsheet.com

CONTAINS()

Does text contain fragment?

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

FIND()

Position of fragment in text

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

ISNOTBLANK()

Is value present?

[pawann]

Steve:

that’s a match, but probably shouldn’t be

yes, you are absolutely right! Thanks for pointing this out and for the reminders about how the functions work in detail.

Even though our email addresses follow a stringent nomenclature and a “partial match” is very unlikely, your suggestion is way more robust.

Implementation is easy by changing the [Editors] column type to list and reducing the formula to:

UNIQUE(
SPLIT(CONCATENATE([Site].[User1]), “,”) +
SPLIT(
	CONCATENATE(
		SELECT( SiteUsers[Email], [Site] = [_THISROW].[Site], TRUE)
	),
",")
)

Thank you very much, Steve. I highly appreciate that you share your knowledge with us!