How to filter a select expression in 2 columns

[leeca]

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 · Accepted solution]

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

[WillowMobileSys · Accepted solution]

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

[leeca]

Tested with the following error:

Arithmetic expression '([isVisibleBy]+[createdBy])' has inputs of an invalid type 'Unknown'

Do I need to wrap the columns in LIST()?

[WillowMobileSys]

How are the columns defined?  I assumed [isVisibleBy] was a List type.  I can see where [createdBy] is just a single value so you could do this:  [isVisibleBy] + LIST([createdBy])

If neither are a List type (includes EnumList) then explain how they are defined and what values they contain.  Depending on that will determine appropriate functions to use.

[leeca]

== USER TABLE ==

I have a User table with 2 columns:

"code", "email".

== DATA TABLE ==

In my data table (the table. I wish to slice), there are 2 columns:

1. [isVisibleBy] is an EnumList which has a few "codes" in it.

One "code" corresponds to one user email.

2. [createdBy] takes an initial value of 

SELECT(User[code], [email]=USEREMAIL())

 so it only holds a single value. 

I intend to use the user "code" to slice my data tables, such that:

1. If explicit permission was given to that user to see the row, the user's "code" is selected in the EnumList [isVisibleBy]

2. Rows created by the user are also visible by him/her

Rows | isVisibleBy | createdBy

1.           User1, User2.   User3

2.                                     User1

3.                User2          User2

In the above 3 rows, User1 would be able to see rows 1 and 2, but not row 3.

[Joseph_Seddik]

What is the key column of your User table? Is it “code” or “email”?

[leeca]

The key column of my table is "code" - I set this as NVARCHAR(8) as the dtype in the backend (SQL Server). My thinking was that if I were to use an email as the Primary Key, it would take up a lot more space than an abbrievated version. However, I need to use the function USEREMAIL() in AppSheet, so I use the email as the lookup value to retrieve the "code", which is used in the tables that I want to slice.

e..g

code: VLN

email: verylongname@gmail.com

[leeca]

As [isVisibleBy] is already an EnumList, to add [createdBy], which is a single valued text column to another list, I needed to wrap it in LIST(), like so:

[isVisibleBy] + LIST([createdBy])

The working expression is:

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

[SkrOYC · Accepted solution]

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

[SkrOYC]

I was writting this before there was any reply, now it seems I came late to the party 😂

@AndrewB This is a killer feature for me. Discourse was fast and everything was real-time. I would be able to see others writting (like on chat apps)

[WillowMobileSys]

I agree!  I have seen a number of times where I post, maybe takes a few minutes or get distracted,  when I come back to review responses, I see there were already a few before me.  It could have saved me time if I had seen them when they occurred.

[leeca]

I appreciate the time taken to reply - I read all replies and try to learn as much as I can from different solutions.

[Joseph_Seddik]

The post was marked as solved even before my reply and I didn’t see it, fortunately it was a short one 😄

[SkrOYC]

Lucky you, I made a long one thinking no one has seen the post.

It's one of those post where the solution is easy and you know you can provide the solution to help