Solved: How to filter a select expression in 2 columns - Page 2

leeca · 03-17-2022 06:14 AM

I'm working on a HR app that displays selected rows to two groups of users: Admin and User.

The approach I took was to display a slice of a table to a logged in user that either has:

- [isvisibleBy]: Rows assigned to be visible to him/user (col type EnumList, values: allowed user ids)

- [createdBy]: Rows that are created by him/user (col type Text, values: SELECT(User[code], [email]=USEREMAIL()))

The slice expression:

IN(SELECT(User[code], [email]=USEREMAIL(), TRUE), [isVisibleBy])

Is valid and it works, but if the user creates a column that populates createdBy with his/her userId, then that column is hidden from him/her if it is not in [isVisibleBy].

I have also tried both these expressions:

Expression #1

IN(SELECT(User[code], [email]=USEREMAIL(), TRUE), {[isVisibleBy], [createdBy]})

- Valid, but only shows [createdBy] rows

Expression #2

OR(
IN(SELECT(User[code], [email]=USEREMAIL(), TRUE), [isVisibleBy]),
IN(SELECT(User[code], [email]=USEREMAIL(), TRUE), [createdBy])
)

- Results in the error: Parameter 2 of function IN is of the wrong type

The equivalent SQL expression for this would be:

SELECT * FROM table
WHERE [isVisibleBy] IN (SELECT code FROM User WHERE email = USEREMAIL())
OR [createdBy] IN (SELECT code FROM User WHERE email = USEREMAIL())

WillowMobileSys

2 things:

1) The first parameter of an IN() function must be a value. SELECT() returns a LIST type - even if there is only one value in the list. To get the value, use the ANY function like this ANY(SELECT(...))

2) You can use the "+" operator for List addition to create one list.

Putting both these together you should be able to do this (note: not tested):

IN(ANY(SELECT(User[code], [email]=USEREMAIL(), TRUE)), [isVisibleBy] + [createdBy])

View solution in original post

SkrOYC

Two things:

1. How do you add user id on [isVisibleBy]? It seems like you have a User table and [Code] column? Try to simplify things by making Usertables where their ID is the email.

Now, that's not mandatory and the usage of a slice called something like "CurrentUser" can help you a lot. Make it's row filter condition with this expression:

[email]=USEREMAIL()

Now you have a dataset where the only row available is the row that corresponds to the user, something like a global usersetting not dependent on device. With this one, you can then use the values from that table in order to create security filters and other slices. This is not the only way and some people uses LOOKUP() for that but I prefer the slice method.
Make sure to have a good Valid_If to prevent to email duplicates, if you don't you will get more than 1 row.

2. With the CurrentUser slice your expression can be changed/simplify to:

OR(
  IN(
    INDEX(CurrentUser[Code], 1),
    [isVisibleBy]
  ),
  INDEX(CurrentUser[Code], 1)=[createdBy]
)

Some reference:

INDEX() | AppSheet Help Center
IN() | AppSheet Help Center
Slice Row Filter Conditions | AppSheet Help Center
List Expressions | AppSheet Help Center

View solution in original post