IN() doesn't work when looking at a column th...

(Grant Stead) #1

IN() doesn’t work when looking at a column that is an enumlist? I had assumed that calling an entire column of enumlist would make the list like one big join… But it doesn’t work. Am I seeing a bug by design, what’s my options?

Example: table PEOPLE with column [people_uid] (I create a slice for this table to parse it down to just the logged in user, it’s called PEOPLE_THISUSER… I use [people_useremail]=USEREMAIL()… there is also a table PROJECT with enumlist column [project_users]. I’ve got an action that adds [people_uid] concatenate to a list ", " of allowed users for this project. So, in the PROJECT table security filter I want to say… IN(ANY(PEOPLE_THISUSER[people_uid]), [project_users])

But it doesn’t work!?


IN doesn’t automatically flatten nested lists. But ANY(PEOPLE_THISUSER[people_uid]) should select the first value, not a whole column. But I couldn’t quite follow what the value would be, what does the value of [people_uid] look like in that slice? Is that where you’re doing the concatenate?

(Grant Stead) #3

@Adam_Stone_AppSheet sorry Adam, I know my example was a little nuts… [project_users] would be the lists I need flattened…

How do I do that?


Not sure if that can be done, maybe some trick with concatenate… if you do Concatenate([project users]) itself, what does the result look like?

(Grant Stead) #5

@Adam_Stone_AppSheet I just tried it, and it won’t work since there is no delimiter parameter built in. I think it just throws them all together…

(Praveen Seshadri (AppSheet)) #6

Grant, if you are setting up a security filter, don’t you want to say something true/false about each row in it? In this case, something based on the enumlist [project_users] ?

I’m guessing you want to see if there is any overlap between [project_users] and PEOPLE_THISUSER[people_id]. if so, you want to include the row, and if not, exclude it. Is that correct?

COUNT(SELECT(PEOPLE_THISUSER[people_id], IN([people_id], [_THISROW].[project_users])) > 0

… or something like that?

(Grant Stead) #7

@praveen Yes, this works!