Show_if Column Expression Returns True Yet is Hidden

[Ferret]

On a column’s show_if constraint, I have the following expression:

ISNOTBLANK(INTERSECT(
LIST("Supervisor","Manager"),SPLIT(Me[Position], ", ")
))

“Me[Position]” is a slice of a Users table that returns only one row (the current user) and the column returned is an EnumList.
Intended behavior is to return TRUE (show column on form) if the current user has “Supervisor” and/or “Manager” in their Position column.

The Test Table Data returns TRUE but yet no field is displayed. Have this problem on multiple columns in multiple apps now, when I could have sworn it worked before (dont hold me to that, I’m still a noob).

Removing the expression or using a simpler true/false statement shows the column as intended (so its not a column order setting on a view at least).

This expression:

if(
ISNOTBLANK(INTERSECT(LIST(“Supervisor”,“Manager”),
SPLIT(Me[Position], ", "))),
TRUE,[User ID]=USEREMAIL()
)

works as intended when used as a security filter within the same app. I had no luck by similarly-wrapping the first expression in an If statement.

Suggestions?

[LeventK (Levent Kulacoglu)]

May I ask why don’t you use a LOOKUP expression provided you have user’s email address in your Users table?

IN(
	{"Superviser" , "Manager"},
	LOOKUP(
		USEREMAIL()
		"Users",
		"Email",
		"Position"
	)
)

[Ferret]

Thank you for your response. I tried this initially, and the problem I had was lookup was a list and so was the column returned. I didnt set it up exactly like that but will try just that and let you know.

My second thought on making a slice instead of a lookup was because this returned result would be done multiple times per app normal app usage. Not exactly sure yet how much little things like that matter in this platform.

[LeventK (Levent Kulacoglu)]

@Ferret

My second thought on making a slice instead of a lookup was because this returned result would be done multiple times per app normal app usage. Not exactly sure yet how much little things like that matter in this platform.

Can you please elaborate?

[Bahbus (Dave Papajcik)]

@Ferret

if(
ISNOTBLANK(INTERSECT(LIST(“Supervisor”,“Manager”),
SPLIT(Me[Position], ", "))),
TRUE,[User ID]=USEREMAIL()
)

Should be:
IN( ANY( Me[Position] ) , {"Supervisor" , "Manager"} )
and that’s it.
or @LeventK’s solution but with the inputs swapped, because they’re backwards.

[LeventK (Levent Kulacoglu)]

@Bahbus
My tests showed that both IN and CONTAINS does not work in this case. The only working expression was:

ISNOTBLANK(
	INTERSECT(
		SPLIT(
			LOOKUP(
				[_THISROW].[USER],
				"MyUsers",
				"EMAIL",
				"POSITION"
			),
			","
		),
		{"Supervisor" , "Manager"}
	)
)

[Ferret]

@LeventK

Can you please elaborate?

Sure.
I found that I had the same Error described here:

Trying to get a Filtered View based on Enum List

Greetings, I’m trying to create an action for Search Form to get filtered view of Users which have the Accounts of all chosen Account Types. App tables: Users | User Name | Related Accounts (virtual list of refs) | Accounts |Account ID | User(Ref to Users) | Account Type(Ref to Account Types) | Account Types | Type | Related Accounts (virtual list of refs) | Search | ID | Search Types(Enum list of Refs to Account Types) | If Search Types is empty - show all users without accounts If Search…

Instead of adding a blank list to the list, SPLIT was suggested to me to use after I contacted support.

ISNOTBLANK(
INTERSECT(
SPLIT(
LOOKUP(
USEREMAIL(),
“USERS”,
“ID”,
“POSITION”
),
“,”
),
{“Supervisor” , “Manager”}
)
)
Works as intended. Thank you. I am gonna try to see if there is a way to do this with a slice still, but only because I expect to use this expression in many areas in the app. Im assuming a reference to a slice 10 times is ‘lighter’ than a lookup 10 times so please let me know if I am overthinking this.

The other expression mention indeed do not fully work as expected:

IN( ANY( Me[Position] ) , {“Supervisor” , “Manager”} )

does not account for multiple values being in [Position].

IN( {“Supervisor” , “Manager”}, ANY( Me[Position] ) )

requires [Position] to contain both “Supervisor” AND (not and/or) “Manager”.

[Ferret]

This is what I was looking for:

ISNOTBLANK(
INTERSECT(
SPLIT(
ANY(Me[Position]),
“,”
),
{“Supervisor” , “Manager”}
)
)

I was so close. Thank you for the final piece.

[Ferret]

DOES NOT WORK

ISNOTBLANK(
INTERSECT(
LIST(
“Supervisor”,“Manager”
),
SPLIT(
Me[Position],
", "
)
)
)

DOES WORK

ISNOTBLANK(
INTERSECT(
LIST(
“Supervisor”,“Manager”
),
SPLIT(
Me[Position],
“,”
)
)
)

The difference is the one space after the comma in the second SPLIT argument. Values in spreadsheet are separated by " , " (Supervisor , Manager , Installer , Courier).
Thanks again for the needed perspective.

[Bahbus (Dave Papajcik)]

Ah, I did not understand that a person could have multiple roles. I figured it was a hieratical setup.