IN() reporting FALSE when should beTRUE

I’m setting up a Show_IF for a view within a dashboard. In it I reference Staff[Statuses] which is an ENUMLIST and check if some text is in that list. Here’s what I get when I test it:

Can’t work out why its false. ENUMLIST is Text, in case this matters. Get same issues when tested as a virtual column.

Could use CONTAINS() but its not as specific as a single item in a list

In the above I’ve used LIST(SPLIT(Select(… But I’ve also tried just SPLIT(Select(… and Select(… Same result with those 2 as well

For nested expressions like these, I developed the following habit:

I go to the relevant table and just create a new virtual column. The first thing AppSheet asks you is to enter the formula. The beautiful thing here is that you are NOT bound by any column type, so you can write any expression you want and test it.

Then I start from the innermost expression going outwards testing every step and see if it gives the correct outcome. I can even manually add a row in the sheet with the optimal values to check against during a specific test.

When I finally get the expression working, I simply delete the sheet row and the virtual column and put the expression in its desired formula field.

4 Likes

LIST(SPLIT(...)) creates a list of a list, which is likely not what you want. Remove the outer LIST().

Also, read Common Problems in the SPLIT() help doc.

4 Likes

Thanks Steve, you pointed me in the right direction. This works fine

IN("Instructed",ANY(Select(Staff[Statuses],UserEmail()=[Login Email])))

So by using SPLIT(Select(… it must somehow think its dealing with a list of lists. Even though this shoudl have rebuilt it in one list :exploding_head:

2 Likes

I use the same technique. But what’s confusing here is a list and a list of lists look identical when displayed as text. Likewise Select(Staff[Statuses],UserEmail()=[Login Email]) and ANY(Select(Staff[Statuses],UserEmail()=[Login Email])) return identical looking results.

2 Likes