cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Filtering Choice Columns in Sharepoint

It seems there is some problem using choice fields to filter in your powerapp.  I have looked around, and it seems that tricks that were used before do not work any more.  My need is simple...

 

I need my PowerApp list to filter based on a choice selection in my SharePoint list.

 

Consider the Choice column, ChoiceColumnName

 

Method 1:

Use ChoiceColumnName.Value

Previous forum result:  Works, but gives you a "blue dot" error, that is supposedly a 500 item list limit.

My Result:  It just yeilds an error "Invalid Name".  I have used .value for choice items in other senarios, not filters, in PowerApps with no problem.

 

Method 2:

Create a Calculated field in Sharepoint. 

Use =[ChoiceColumnName] for your calculation.

Select "Single Line of Text" as the type

Use this new columnname in your PowerApp filter.

Previous result from other forum entry:  Works perfect with no limits.

My Result: This methed gives the "blue dot" error (the 500 item limit), but then does not work.

 

Whats the deal?  I need this to work.  Its too fundamental to be able to filter for a choice field!!!

7 REPLIES 7
Community Support Team
Community Support Team

Re: Filtering Choice Columns in Sharepoint

Hi @martinav,

 

Is the Choice field a Multi-value enabled field?

If yes, then please take a try to change the formula as below:

Filter(SharePointListName,"SearchValue" in Choice.Value)

For single value Choice field, try:

Filter(SharePointListName, Choice.Value="SearchValue")

Adding the delegation reference:

Understand delegation

Delegable data sources

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DanielaH
Level 8

Re: Filtering Choice Columns in Sharepoint

Hi Michael

 

Thanks for providing your response to @martinav, however neither solution has worked for me, and I am trying to solve the same issue.. I am not sure which kind of choice field I have so I tried both:

 

Single value Choice field:

ClearCollect(ComplianceProfiles, Filter(Profiles, Profile_x0020_Type.Value = "non-site"))

 

This results in the error below, suggesting the issue relates to the 500row limiation. However, the Profile_x0020_Type column is an index column, and I am filtering for which should result in less than 200 records... so that's strange. 

 

Screenshot (45).png

 

Multi-value choice field:

ClearCollect(ComplianceProfiles, Filter(Profiles, "non-site" in Profile_x0020_Type.Value))

 

Here I get the following error:

 

Screenshot (47).png

 

@martinav, have you been able to resolve this for yourself?

Super User
Super User

Re: Filtering Choice Columns in Sharepoint

Your formulas are correct. The blue dots are warnings that you have a non-delegable query. A choice column is a complex field type and isn't delegable in SharePoint. This means the connector will return 500 records then apply the filter. This means if you have more than 500 items in your list you may not get the expected results. Even though your formula should return 200 items the first 500 items returned may not include those 200 items. There is a feature that will let you increase non-delegable row limit up to 2,000.

 

PowerApps data row limit for non-delegable queries

 

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
DanielaH
Level 8

Re: Filtering Choice Columns in Sharepoint

Hi 

 

Thanks so much for your response. 

However, I had already increased the non-delegable row limit to 2000.

 

It appears that TRUE/FALSE and also CHOICE field filters do not work, if the list is longer than 500 rows - even if the row limit was increased to 2000 rows.. is that possible? 

 

The formula syntax filters perfectly when used on a list that is shorter than 500. I guess the increase to 2000 is still an experimental feature after all.. Has anyone else experienced that? Any workarounds?

btontheway
Level: Powered On

Re: Filtering Choice Columns in Sharepoint

I'm facing with the same issue.

Get warning message, when referencing Choice fields as:

Filter(Database_on_Sharepoint; ChoiceFieldName.Value = "Anything")

The problem is at the Value.

I'm pretty beginner, so still searching for solution but may be it could help us to add one hidden column in the sharepoint list, where we use Single line of text column and we automatically use the choosen value of the choice column and then reference this hidden column n Powerapps.

I'm still searching for a workaround on this.

Option A: there is a solutiuon to use the choosen choice in the Default properties somehow of the hidden column

Option B: use a workflow to do the copy and paste task

I would prefer Option A if doable...

 

May be we get a help from somebody here.

Thanks, Tamas

btontheway
Level: Powered On

Re: Filtering Choice Columns in Sharepoint

It seems, that I can't do this on Sharepoint's side.
What I think, we can do is:
1. Create a hidden column in Sharepoint with any name (ChoiceFieldNameHidden for example)
2. On the form in Sharepoint, where the record can be created or changed put a hidden datacard, which is the ChoiceFieldNameHidden, but the default value is the ChoiceFieldName. This way we will have a column with the same text in a single line of text column in sharepoint and then we will be able to use the Filter based on that column instead of the choice type column...
Let's see, if this can work.

Tamas

btontheway
Level: Powered On

Re: Filtering Choice Columns in Sharepoint

This is how it works finally:

1. Create a (hidden) column in Sharepoint with any name (ChoiceFieldNameHidden for example)
2. On the form in Sharepoint, where the record can be created or changed put a hidden datacard, which is the ChoiceFieldNameHidden, but the default value is the DataCardValue of the ChoiceField.

This way we have a column in Sharepoint with the same value(text) as the choice field in a single line of text column  and then we are able to use the Filter (delegable this way) based on that column instead of the choice type column...

 

I could find this solution for my problem, but please if you find a simplier method, post it here.

Thanks, Tamas

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 31 members 4,260 guests
Please welcome our newest community members: