cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SPA
Level: Powered On

SharePoint Delegation with simple Filter on Text

I created a PowerApps Canvas app from scratch, and it has a  Drop Down box. For some reason I'm not able to perform a simple delegated filter against a SharePoint list. I have a SP list ("MMD") that only uses single-line-of-text, number, and date fields. The "PRG_MSA" field is a single line of text field in the "MSA" SP list. 

 

I have spent days searching online and making changes, trying to get it to delegate to SP, and all my research indicates the below code should not be a problem:

The Items property for the Drop Down control = Filter(MMD_Programs,PRG_MSA = varSelectedMSA)

 

I have also tried changing the text variable "varSelectedMSA" to a hard-coded text value. Tried changing "=" to "StartsWith" (even that would not work with the data I will eventually have). And I've tried loading the filter results into a collection for the heck of it. Despite changing the approach, the result is always the same...

 

I do not get any warning for delegation, but when I run the app, the number of items in the Drop Down is limited by whatever I set in the "Data row limit for non-delegable queries" setting. I changed this from 500 to 1, 2, and 3 to ensure I will not have delegation issues when we deploy at scale and we have more than 2000 items in the MSA SP list. Every time I change the Delegation row limit, I get exactly that many values returned.

 

If it is not delegating, why do I not get the non-delegation warning? More importantly, why am I only getting the non-delegated number of rows?

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: SharePoint Delegation with simple Filter on Text

In the situation in the article that would be a correct understanding.  The problem is that when populating a dropdown through binding there is no delegation at all.  So you will always be limited to the non-delegable rate if you are populating a dropdown.  That's why @juresti is suggesting you switch to a gallery.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

15 REPLIES 15
Super User
Super User

Re: SharePoint Delegation with simple Filter on Text

@SPA 

The delegation limit applies not only to the number of rows evaluated but also the number of rows returned.  Example: if you delegation limit is set to 500 rows, the entire Sharepoint List will be searched with Filter but only the 1st 500 rows will be returned into PowerApps.

 

Why does PowerApps behave in this way?  Speaking generally you wouldn't want to return more than 500 rows in a mobile app at a single time because they would use alot of data and would not be viewable on the screen.  There are ways to get around this using pagination.

 

@mr-dang had an excellent post where he suggested a workaround.  I suggest you check it out if needed 

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/500-item-limit-in-CDM-entity-search-...

 

You might also be interested in this post by @juresti 

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Delegation-limit-solved-avoid-delega...

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."  

SPA
Level: Powered On

Re: SharePoint Delegation with simple Filter on Text

@mdevaney thank you for the prompt response! Your post makes total sense, but seems contradictory to the Microsoft online documentation on Delegation (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview😞

"To ensure that your app can scale to large data sets, reduce this setting down to 1. Anything that can't be delegated returns a single record, which should be easy to detect when testing your app. This can help avoid surprises when trying to take a proof-of-concept app to production."

 

This was the guidance I was using in my testing. I do note that the documentation was last updated on 07/04/2018, and I know there have been many enhancements deployed since then, so perhaps it just needs to be updated?

 

I will read the links you sent to see if there is another way to test delegation... unless the delegation warnings are now entirely reliable (more than they must have been in July 2018 when the documentation was written).

Highlighted
Super User
Super User

Re: SharePoint Delegation with simple Filter on Text

@SPA 

I need to make a quick correction.  Only the first 500 records that satisfy the condition will be returned.  This is not affected by the delegation settings.  Therefore you need to paginate the results like @mr-dang suggests, use the workaround provided by @juresti or find some way that doesn't need greater than 500 records returned all at once.

juresti
Level 8

Re: SharePoint Delegation with simple Filter on Text

Hello,

 

I have had this exact same issue you are having.

 

I would use a gallery as a select list which can display many more records if you need to see them all on screen.

 

The reason is the drop down control does not support delegating because I think it is doing some type of "collect" action inside to populate itself which of course Collect does not support delegation.

 

I will post my solution, it involved using flow which can fetch all the records for you.

 

@mr-dang has a complete video on using flow to get unlimited data to your powerApp, which I can't find anymore.

Anyhow, I changed the solution a bit to easily get the column data by using regular expression to find data between column names.

If you find his video this is how I went about grabbing the column data:

 

Set(varTruckList, truckList.Run().trucklist);
Clear(colTruckList);
ForAll(Split(varTruckList,"\n"),
Collect(colTruckList,
{ eqNumber: Match(Result, "(?<=eqNumber"":"").+(?="",""eqDescription)").FullMatch,
eqDescription: Match(Result, "(?<=eqDescription"":"").+(?="",""eqCategory)").FullMatch
})
)

 

I'll respond here when I post my complete sample of it.

Dual Super User
Dual Super User

Re: SharePoint Delegation with simple Filter on Text

Let me add something to the conversation at this point.  Delegation aside, the Dropdown control in PowerApps has a hard coded limit of no more than 500 items.  This can not be changed.  No matter what size collection you bind to the dropdown it will never display more than 500 choices.  Look at the last sentence in the description for the control in the official documentation.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-drop-down

 

The control will show a maximum of 500 items



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
juresti
Level 8

Re: SharePoint Delegation with simple Filter on Text

@Pstork1 I had not looked at the control info.

 

I am only getting up to 512 items.

 

At first I thought I was going over 500 but it is only up to 512.



 

 

Dual Super User
Dual Super User

Re: SharePoint Delegation with simple Filter on Text

Just to be clear.  512 is over 500.  If you retrieve 512 you will only see the first 50 or whatever you set the delegation limit to, whichever is smaller.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Dual Super User
Dual Super User

Re: SharePoint Delegation with simple Filter on Text

Sorry, typo. That should read 500



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
SPA
Level: Powered On

Re: SharePoint Delegation with simple Filter on Text

@mdevaney @Pstork1 @juresti ... thank you for the input. To be clear, I don't expect to return more than say 20-30 items for the drop down control. Personally, much more than that would rapidly become unmanageable. 

 

To recap the problem in light of recent posts... I have 5 matching values in a SP list that only has 10 rows currently. I have a filter in the Items property of a Drop Down control that is so basic (Filter(MMD_Programs,PRG_MSA = varSelectedMSA)) that it should be delegable, but it's only returning the number of matches allowed by the Non-Delegable setting. For some reason, I can not replicate what @juresti shows in the post: https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Delegation-limit-solved-avoid-delega.... According to that post, my filter should return all 5 matches if the Non-Delegable setting is = 2, but what I get is only 2 matches returned. As my SP List contains nowhere near 500 rows (let alone 500 matches!) I am still perplexed why I see this behavior in my app, and I'm concerned the filter will be treated as Non-Delegated when running at scale.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,695)