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

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.

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

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:

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?

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)

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

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

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)

I totally agree with you. Thank you!

1 Like

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. ;-(