Answered

SharePoint Delegation with simple Filter on Text

(0) Share

Report

Posted on by SPA

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

mdevaney 29,989 Moderator on at

Like (1)

Report

@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-filter-need-to-switch-to-asp/m-p/22980/highlight/true#M9872

You might also be interested in this post by @juresti
https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Delegation-limit-solved-avoid-delegation-and-get-all-joined/m-p/378017

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

Was this reply helpful? Yes No
SPA 81 on at

Like (0)

Report

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

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
juresti 319 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
juresti 319 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Sorry, typo. That should read 500

Was this reply helpful? Yes No
SPA 81 on at

Like (0)

Report

@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-delegation-and-get-all-joined/m-p/378017. 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.

Was this reply helpful? Yes No
juresti 319 on at

Like (0)

Report

Hello,

I'm using a gallery in my post to do display the items without delegation affecting them.

The drop down control does not support this.

I think you could build a gallery into a pop up to use as a select list. If you needed to select multiple items at once it would require a little more work.

Was this reply helpful? Yes No