Hi. I'm using the security filter to filter r...

[Stephen_Robb]

Hi. I’m using the security filter to filter rows a user sees. I have set up a rule which should return “Y”, but returns “N”. You can see the user’s ID in the list (ne of two key). Can anyone explain why this us returning “N”?

[Steven_Coile]

I don’t have permission to view the image.

[pravse]

Could you post the expression your are using? Also have you tried using the expression tester to debug it?

[Stephen_Robb]

Hi Guys. I’ve tried to give permissions to the image that shows the results of the test of the expression. The Expression passes the syntax check and it’s the “Test” that doesn’t provide the expected result. I’m wondering if I need to use “INCLUDES” rather than “IN”? The list I am checking for the inclusion of the key is returned from an inline list from a referenced table. (The “User” is a “Team Member” and their ID is in the “Team Members List” which is a Slice).

[Aleksi_Alkio]

First try to type the formula with fixed user ID, like IN(“IWIqJbWE”,…) so you would have a hunch which one could be the reason.

[Stephen_Robb]

@Aleksi_Alkio Thanks. I’ve figured it out. Rather than using “IN”, I need to use “CONTAINS”. I’m guessing this is because, rather than being the result of a SELECT, and therefore a true List, this series of ID’s returned from the referred table is simply a comma-delimited string, and not a true List. I’ve changed to “CONTAINS” and it’s now working as expected.

[Aleksi_Alkio]

If the “list” is coming from the one text cell, you could read the “list” with the IN expression like… IN([User],SPLIT(LOOKUP(“KeyColumnValue”,ThisAppUserMonitoredTeams,KeyColumn,Team Members)," , "))

[Stephen_Robb]

@Aleksi_Alkio Thanks. The value I’m looking for ([User]) is actually the ID that’s already in the string, so I don’t need to look up another value from the table. Good to know I can though, if I need to somewhere else. Thanks!

[Steven_Coile]

@Stephen_Robb, @Aleksi_Alkio’s suggestion to use SPLIT() and IN() is a good one. Using CONTAINS() could result in false positives if the user’s name is short and may occur as part of some longer name (e.g. “steven” contains “steve” but isn’t an exact match).

[Stephen_Robb]

+Steve Coile Thanks Steve. Note that I’m using keys (generated using UNIQUEID) rather than actual names, so no chance of false positives in this instance. I wasn’t aware of the LOOKUP function though, but have already been able to use it in a couple of instances to make things more streamlined.