Combining expressions in the UX, Show_if

AppSheet AppSheet Q&A
, ,
1

Hi everyone,

I have 3 expressions I would like to combine in the UX Show_if (Optional Yes/No formula to decide whether this view should be shown). I currently have 3 tables where the first is the Manager table, second is the Employee table and the third is the Client table. I have tried limiting certain view for the client and only tried to give access to Manager and Employee table using this expression in the Show_if

IFS(IN(USEREMAIL(), User_Manager[Email]), TRUE, IN(USEREMAIL(), User_Employee[Email]), TRUE, IN(USEREMAIL(), User_Client[Email]), FALSE)

The view worked as intended for the Manager table and Client table. However, it didn’t work for the Employee table and it has hidden the view for the Employee table when the view for the users in Employee table should have been displayed. The expressions passed when I tried it although I’m not sure what I’m doing wrong here. Hoping anyone can help me resolve this.

Thank you.

2

I do not see anything wrong with your expression.

To debug this, have you tried the expression IN(USEREMAIL(), User_Employee[Email]) to see if this gives Employees access to the view and not managers nor clients?

I usually start with the smallest expression and work my way up to more complex ones when I hit a wall..

1 Like
3

I have debugged the expression for IN(USEREMAIL(), User_Employee[Email]) and it did not give me access to the view. Still facing the issue. Is this a bug?

4

I think it is a bug. It is usually not a system one bur the developer’s. :grin:

You getting correct expression editor’s test results?

IN(USEREMAIL(), User_Manager[Email]) give managers access to the same table?

1 Like
5

Yes this expression IN(USEREMAIL(), User_Manager[Email]) and IN(USEREMAIL(), User_Client[Email] give access to the same table but not IN(USEREMAIL(), User_Employee[Email]. I spent quite some time debugging and changing the expressions it seems that it doesn’t allow the Employee table to be able to view the same table unless I remove any input in the expression Show_if.

6

Your expression can be simplified to:

OR(
  IN(USEREMAIL(), User_Manager[Email]),
  IN(USEREMAIL(), User_Employee[Email])
)

Given the behavior you describe, I’d guess the user’s email address doesn’t match any of the values in the Email column of the User_Employee table.

1 Like
7

Hi Steve, I can confirm that my application doesn’t detect the values in the Email column of the User_Employee table. The table structure of the Manager, Employee and Client tables are all the same with the column structure as following: Email | Name | Designation. Is it due to interference with other expressions? In the Are updates allowed? section, my expression is

IFS(CONTAINS(LOOKUP(USEREMAIL(),“User_Manager”,“Email”,“Designation”),“Manager”),“ALL_CHANGES”)

It did gave me a notice something like this

Note: This expression could impact performance.

1 Like
8

The Are updates allowed? setting has no effect on reading the data or using it in expressions; it only affects the ability to add, delete, or update rows.

Can you confirm the data in the User_Employees is correct? And that there are no extraneous punctuation or spaces in the values?

9

Yes correct, checked twice no punctuations or spaces in the values. The thing is a notice appeared at the top of the expression box.

This formula is not evaluated in the context of a row, so column values are not available.

10

That shouldn’t be a problem, as you aren’t trying to reference a column value of a specific row.

Did you try the OR() expression I provided above?

1 Like
11

Yes I tried with your OR() expression and the problem still persists.