Usersettings expression in a slice row filter

Trying to use a value from the Usersettings () within a slice row filter condition.

The data table has corresponding company reference [COMPANYLOG] and I thought it was as simple as:

[COMPANYLOG]=USERSETTINGS(Company)

I have checked I have values in the data table and Usersetting that match but not coming through - am doing this correct?

Try to use USERSETTINGS(“Company”) if that would help.

In what way is it “not coming through”? Some rows you’d expect to appear aren’t? None of the rows you’d expect appear? Rows you don’t expect do appear?

At the very least, because of the way the is-equal-to? operator (=) works in AppSheet, if the left-hand argument ([COMPANYLOG], in your case) is blank, the expression will be TRUE regardless what the right-hand argument (USERSETTINGS(Company)) is. This might result in the occurrence of unexpected rows in your slice if [COMPANYLOG] is ever blank.

Thanks - they were blank - no rows coming through. I think then discovered the usersettings weren’t getting updated and saved unless I went into the settings menu view and “clicked” save.

Which leads me to ask a question - when a user logs in for the first time, does the usersettings pick up an initial value expression or do you actually have to go into the setting menu and click “save” to create the record?

I wanted the usersettings to default to values in my “user” table - such as which company they represent and what role they have. I then use this info in the slice row filter to allow:

  1. any user to see their own data
  2. any manager representing a company to see all users data from that company
  3. any user with “Admin” rights to see all data

I also have a filter to only bring up the last 7 days, as well as only “Active” flag lines.

The filter I am using is as below - is this the best way to control my data?

and( [Date]>Today()-7,
OR([Useremail]=Useremail(),
AND([COMPANYLOG]=USERSETTINGS(“Company”) , USERSETTINGS(“Role”) = “Manager”),
USERSETTINGS(“Role”)= “ADMIN”),
[STATUS]=“ACTIVE”)

To get around this not working i then used lookup() function below - is this suitable?
Seems to work on mobile device fine. My next thought was to setup virtual columns to pull in the user credentials to use in the same expression instead of lookup() function - would that work and is it a better way to handle data?

and( [Date]>Today()-7,
OR([Useremail]=Useremail(),
AND([COMPANYLOG]= LOOKUP(Useremail(),“USERS”,“USER EMAIL”,“COMPANY”), LOOKUP(Useremail(),“USERS”,“USER EMAIL”,“ROLE”) = “MANAGER”),
LOOKUP(Useremail(),“USERS”,“USER EMAIL”,“ROLE”) = “ADMIN”),
[STATUS]=“ACTIVE”)

Thanks in advance if anyone can give some guidance and apology’s for the long questions.
Cheers

@tony

1 Like