Help with Show If expression for EnumList Ref column

[Lucinda_Mason]

IN(LOOKUP(USEREMAIL(), “Users”, “USER_EMAIL”, "DPT_NO”),{10})

I think the problem has to do with the DPT_NO column being an EnumList, Ref because other columns with different data types all work. What am I missing?

This works: LOOKUP(USEREMAIL(), “Users”, “USER_EMAIL”, "VIEW”)=“Simple”
and this: LOOKUP(USEREMAIL(), “Users”, “USER_EMAIL”, "PI_NO”)>15

Thank you.

[Marc_Dillon]

What is the expression supposed to be checking, in English? Because it doesn’t make much sense how you have it written. IN wants a single value as first argument, and a list as second. You’ve given it a possible list as first, and a list of a single item as second. huh?

I can never remember how LOOKUP() returns when you ask it to return a List, but you could try one of the following options depending on exactly what you answer for the above.

LOOKUP(…) = 10 (or maybe “10”)

CONTAINS( LOOKUP()…) , “10” )

CONTAINS( CONCATENATE( LOOKUP(…)) , “10”)

IN( 10 , LOOKUP(…) )

IN( 10 , SPLIT( LOOKUP(…) , “,”) )

[Lucinda_Mason]

Thanks Marc. I’m want to show the view if DPT_NO contains the value of 10. DPT_NO can have one or more values.

[Marc_Dillon · Accepted solution]

I’d try this one

Marc_Dillon:

IN( 10 , LOOKUP(…) )

[Lucinda_Mason]

That worked. Thank you!

[Marc_Dillon]

Marc_Dillon:

I can never remember how LOOKUP() returns when you ask it to return a List

hey @Steve , just saw you liked this post. Can I get your wisdom on the above comment. Remember something written elsewhere about certain data Types running through LOOKUP/ANY that maybe get converted. Or am I making stuff up?

[Steve]

Oh, that you specifically recommended IN(..., LOOKUP(...)) suggested to me LOOKUP() behavior has recently changed. In my experience, LOOKUP() would only return singular values, so lists would have to be SPLIT() to get to their values. That @Lucinda_Mason marked your suggestion as the solution and said it worked tells me SPLIT() is no longer needed.

[Marc_Dillon]

Good to hear I didn’t completely make it up. I also tested it in my test app, and was surprised that it worked.