IN list not working as expected.

AppSheet AppSheet Q&A
,
1

I have a user table with an email as KEY, and a department col that is an enum list from a department table.

a user can be in multiple departments. so an example USER table record is this.

FirstName LastName email Department
First Last email@email.com DataStds,IT,Finance,Service,Engineering,Operations,Sales,Manufacturing

trying to determine if a user is in a specific department with IN

in(‘DataStds’,select(User[Department],[email]=USEREMAIL()))

returns FALSE

select(User[Department],[email]=USEREMAIL())

returns

DataStds,IT,Finance,Service,Engineering,Operations,Sales,Manufacturing

why is this not working?

2

I was able to accomplish what I was after with FIND, but I can’t understand WHY IN didn’t work… Anyone?

if(find([Worker].[Department],select(User[Department],[email]=USEREMAIL()))>0,TRUE,FALSE)

3

Using SELECT() of a EnumList column does some funky things since it constructs a list of enumlists. I hope that makes sense. So to get around that I would first suggest the following:

IN("DataStds",SPLIT(SELECT(User[Department],[email]=USEREMAIL()), ","))

Now to avoid a IN() with SELECT() in a single formula I would suggest a security filter on your User datasource or creating a slice “This_User” that filters the User data to the user currently using the app. Then the IN() formula would become one of the following two options:

IN('DataStds',ANY(User[Department]))

IN('DataStds',ANY(This_User[Department]))
4

thank you I assumed it has something to do with the way the data was returned. I did not think of SPLIT!