Unanswered

Filtering a SharePoint list Choice field by a Choice value

(0) Share

Report

Posted on by dcullen_

I have a SharePoint list called JobsTest that has the following structure. JobsTest(ID, Title, Assigned Person). The Assigned Person field is a Choice data type and can contain multiple values.

I want to be able to filter all of the Jobs where "Alice" is one of the Assigned Persons. This should return Jobs 1 and 3 from my data below and see screenshot.

Data:

JobsTest( 1, "Job 1", "Alice")

JobsTest( 2, "Job 2", "Bob")

JobsTest( 3, "Job 3", ["Alice", "Bob"])

I have tried the following formulas with no luck:

Filter(JobsTest, 'Assigned Person' in "Alice")

Filter(JobsTest, 'AssignedPerson'.Value = "Alice").Title

I have also played with the Choices() function.

Please can someone point me in the right direction.

Thanks

Categories:

Building Power Apps

image002.png

image003.png

image001.png

I have the same question (0)

All responses (7)

Answers (0)

PTBBSolutions 691 on at

Like (0)

Report
Hello @dcullen_ ,

Where are you writing this formula? (in what control)

Could you please try something like this:

Filter( JobsTest, "Alice" in Concat(Choices,Value,";") )

Was this reply helpful? Yes No
dcullen_ 8 on at

Like (0)

Report
Thank you, I've just tried this:

Filter( JobsTest, "Alice" in Concat('Assigned Person',Value,";") )

It seems to work, however two issues.
1) I get a delegation warning on the IN clause
2) I want to display the "Title" field in the dropdown. It is greyed out (see screenshot)

Was this reply helpful? Yes No
PTBBSolutions 691 on at

Like (0)

Report

What is the message when you hover over the "in"? As for the display field, just put .Title afther the closing bracket of the Filter.

Was this reply helpful? Yes No
dcullen_ 8 on at

Like (0)

Report

Cool thanks, Title is now been displayed so it's just the delegation warning:

Does this mean that if my rows exceed 1000 this may not work? If so this is quite a limitation.

Cheers

Was this reply helpful? Yes No
PTBBSolutions 691 on at

Like (0)

Report
In operator is not delegable in SP. Try to replace the filter with search:
Search( JobsTest, "Alice", Concat('Assigned Person',Value,";") )

Was this reply helpful? Yes No
dcullen_ 8 on at

Like (0)

Report
It appears that Search is not delegable in SharePoint either!

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

I wonder if I can get away with adding another Filter() to limit the number of rows returned so that it will always be less than 100. e.g. a type field where type = "SomeJobType".

Filter( JobsTest, "Alice" in Concat('Assigned Person',Value,";"), Type = "SomeJobType" )

Was this reply helpful? Yes No
PTBBSolutions 691 on at

Like (1)

Report
You can, but the sintax is different:

Filter( Filter( JobsTest, Type = "SomeJobType" ), "Alice" in Concat('Assigned Person',Value,";"))

Was this reply helpful? Yes No