cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

 

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?

Highlighted
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