Re: Show_if Column Expression Returns True Yet is ...

Ferret · 01-12-2021 02:58 PM

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

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

Can you please elaborate?

Bahbus

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

@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

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

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

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

Show_if Column Expression Returns True Yet is Hidden