cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmurray
Advocate I
Advocate I

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 II
Dual Super User II

Hi @cmurray 

 

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 II
Dual Super User II

Hi @cmurray 

 

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

WarrenBelz
Super User
Super User

Hi @cmurray ,

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.

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 @cmurray ,

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.

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,787)