cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Delegation warning on gallery items filter (SharePoint list data source)

Hi all,

 

I have searched the community here but can't find an answer that seems suitable to my predicament.

 

I have created a canvas app using a SharePoint list as my data source. I have a gallery displaying the list, with a filters pane offering multiple dropdown filter options (drop down values stored in collections). The filters are set in the gallery's Items property and work as expected, except for the fact that I'm getting this warning - "Delegation warning. The "Filter" part of this formula might not work correctly on large data sets."

 

We expect the list to grow substantially and so want to avoid delegation issues.

Might someone know please what part of this formula would be causing a delegation issue?

 

 

SortByColumns(
	Filter('Business plan log',
		If( drpStageOfWorkFilter.Selected.Value = "All", true, 'Stage of Work'.Value = drpStageOfWorkFilter.Selected.Value)
		And If( drpPartnerCentreFilter.Selected.Value = "All", true, 'Partner / Centre Owner'.Value = drpPartnerCentreFilter.Selected.Value)
        And If( drpStrategicPriorityFilter.Selected.Value = "All", true, 'Strategic Priority'.Value = drpStrategicPriorityFilter.Selected.Value)
        And If( drpProposalStatusFilter.Selected.Value = "All", true, 'Proposed Work Development'.Value = drpProposalStatusFilter.Selected.Value)
        And If( drpProposalDirectorSignOffDecisionFilter.Selected.Value = "All", true, 'Director sign-off decision'.Value = drpProposalDirectorSignOffDecisionFilter.Selected.Value)
        And If( drpProposalLeadershipReviewDecisionFilter.Selected.Value = "All", true, 'Leadership proposal review decision'.Value = drpProposalLeadershipReviewDecisionFilter.Selected.Value)
        And If( drpBusinessCaseStatusFilter.Selected.Value = "All", true, 'Business case development'.Value = drpBusinessCaseStatusFilter.Selected.Value)
        And If( drpBusinessCaseLeadershipReviewDecisionFilter.Selected.Value = "All", true, 'Leadership business case decision'.Value = drpBusinessCaseLeadershipReviewDecisionFilter.Selected.Value),
        StartsWith(Title, txtSearchBox.Text) Or 
        StartsWith('Work item label', txtSearchBox.Text)),
"Title", SortOrder.Ascending)

 

I appreciate any and all advice.

Thanks in advance.

Carla

1 ACCEPTED SOLUTION

Accepted Solutions
RezaDorrani
Dual Super User
Dual Super User

Hi @Anonymous 

 

Check out the delagation playlist here -

https://www.youtube.com/watch?v=gwiErbYtRdA&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD

 

The 3rd part of the playlist has a scenario that showcases adding multiple filters to a gallery.

https://www.youtube.com/watch?v=pn50AKn3Q1Y&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD&index=3

 

--------------------------------------------------------------------------------
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.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

View solution in original post

6 REPLIES 6
JayMagness
Solution Specialist
Solution Specialist

 HI,

 

There are several ways to help in this case.

 

would be best to send me an email jay@research-nest.com and I can try to help you

RezaDorrani
Dual Super User
Dual Super User

Hi @Anonymous 

 

Check out the delagation playlist here -

https://www.youtube.com/watch?v=gwiErbYtRdA&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD

 

The 3rd part of the playlist has a scenario that showcases adding multiple filters to a gallery.

https://www.youtube.com/watch?v=pn50AKn3Q1Y&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD&index=3

 

--------------------------------------------------------------------------------
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.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

WarrenBelz
Super User
Super User

Hi @Anonymous ,

Happy to help you on the forum so others can benefit from the solution if we get there (this is the purpose of the forum).

Are any of the fields you are trying to Filter in your code Lookup fields - if so this is your Delegation issue. There are are workarounds (if you really need them to be lookup type) with a "shadow" text field. Happy to elaborate further.

You also need to bracket the Or() argument at the bottom to ensure correct priority of operators.

SortByColumns(
   Filter(
      'Business plan log',
      If(
         drpStageOfWorkFilter.Selected.Value = "All", 
         true, 
         'Stage of Work'.Value = drpStageOfWorkFilter.Selected.Value
      ) && 
      If(
         drpPartnerCentreFilter.Selected.Value = "All", 
         true, 
        'Partner / Centre Owner'.Value = drpPartnerCentreFilter.Selected.Value
      ) &&
      If(
         drpStrategicPriorityFilter.Selected.Value = "All", 
         true,
         'Strategic Priority'.Value = drpStrategicPriorityFilter.Selected.Value
      ) &&
      If(
         drpProposalStatusFilter.Selected.Value = "All", 
         true,
         'Proposed Work Development'.Value = drpProposalStatusFilter.Selected.Value
      ) &&
      If(
         drpProposalDirectorSignOffDecisionFilter.Selected.Value = "All", 
         true, 
         'Director sign-off decision'.Value = drpProposalDirectorSignOffDecisionFilter.Selected.Value
      ) &&
      If(
         drpProposalLeadershipReviewDecisionFilter.Selected.Value = "All", 
         true,
         'Leadership proposal review decision'.Value = drpProposalLeadershipReviewDecisionFilter.Selected.Value
      ) &&
      If(
         drpBusinessCaseStatusFilter.Selected.Value = "All",
         true,
         'Business case development'.Value = drpBusinessCaseStatusFilter.Selected.Value
      ) &&
      If(
         drpBusinessCaseLeadershipReviewDecisionFilter.Selected.Value = "All", 
         true,
         'Leadership business case decision'.Value = drpBusinessCaseLeadershipReviewDecisionFilter.Selected.Value
      ) &&
      (
         StartsWith(Title, txtSearchBox.Text) ||
         StartsWith('Work item label', txtSearchBox.Text)
      ),
      "Title", 
      SortOrder.Ascending
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Anonymous
Not applicable

Thanks all.

Hi @WarrenBelz . None of the fields in the SharePoint list are lookups. All fields referred to in the filter component of the Items property are Choice columns (the SortByColumns fields are Single line of text columns). Does this help perhaps narrow it down further?

Hi @Anonymous ,

Try going back to one filter and then adding one at a time until you get the warning. I have just done a test on a Choice field in a very big list and did not get a Delegation warning.

Anonymous
Not applicable

Thanks for your help. I've resolved the delegation finally by doing as you said (going back to starting with one filter and adding them back in) plus also replacing the filter part of the Items property with the filter pattern referred to in @RezaDorrani's linked video. No delegation warning now - I'm happy!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,026)