USERNAME() and FILTERING DATA

AppSheet AppSheet Q&A
,
1

I am currently using USEREMAIL() in combination with a table of Employees (Name, Email, Phone,Role) and LOOKUP functions to do a wide range of filtering of data and deciding what to show, what is editable, etc. for each user.

For example…

  • SHOW IF: LOOKUP(USEREMAIL(),Employees,Email,Role)=ADMIN
  • EDIT IF: [Name] = LOOKUP(USEREMAIL(),Employees,Email,Name) ]

My understanding is you either can’t or shouldn’t use USERNAME() for this.

I know that Lookup is expensive on sync time. The easy way around is to use Email as the Ref column for the employees table, but that gets annoying because I really want to by default show the name of employees for my users, not their email address.

Is there an easy or better way to do this?

I could create new USERSETTINGS fields called Name and Role and set them to the person’s name and role by default, then invoke this filtering using USERSETTINGS(Name) and USERSETTINGS(Role), but I can’t figure out if there’s an admin interface to manually edit settings to ensure all the usernames are set correctly and appropriately locked down.

Suggestions welcome!

2

Nick_Haschka:

SHOW IF: LOOKUP(USEREMAIL(),Employees,Email,Role)=ADMIN

Because of a quirk with the is-equal-to operator (=), your expression will be TRUE if LOOKUP() returns a blank value. Best to swap the operands:

ADMIN=LOOKUP(USEREMAIL(),Employees,Email,Role)

Nick_Haschka:

The easy way around is to use Email as the Ref column for the employees table, but that gets annoying because I really want to by default show the name of employees for my users, not their email address.

Make Email the key and Name the label?

1 Like
3

Nick_Haschka:

My understanding is you either can’t or shouldn’t use USERNAME() for this.

Not heard of this but the main reason to avoid using USERNAME() is, it’s not unique. On the contrary USEREMAIL() is always unique.

1 Like