Answered

Does delegation extend to nested filters?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi, just want to ensure I correctly understand how delegation works with nested filters.

I have the following code for the items property of a gallery:

TL_ShowComplete is a Checkbox and SOWTimeline is a Sharepoint list, which currently only has 20 or so records, but I expect it to have upwards of 50k eventually. The formula works as expected, but is obviously throwing a delegation warning. My assumption here is that I'm ok, because the Switch function is only acting on the outer filter, while the inner filter is the one acting on the SP list. I do not ever expect the inner filter to return more than 50 or so results.

Am I thinking about this correctly? I'd rather not ClearCollect() it if it can be avoided.

thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous

I believe that your Switch function is showing a delegation error because TL_ShowComplete is a yes/no field in SharePoint. To test if I am right, change the column types in SharePoint from yes/no to single line of text and leave everything else exactly as it is and see if the delegation warning disappears.

For the exact answer to your question, it would be "that depends". I refer you to @WarrenBelz 's excellent blog post on delegation in SharePoint. https://warrenbelz.blogspot.com/2020/08/power-apps-delegation-is-word-not.html for further discussion.

Was this reply helpful? Yes No
Verified answer

Pstork1 69,397 Most Valuable Professional on at

Like (3)

Report
Copy link

Link copied!
You are correct. This is one of the primary strategies that a lot of people use to work with Delegation. Have an inner filter that is delegable that will shrink the overall data set to a size below the data row limit and then apply non-delegable functions to that result.

One suggestion. If you are going to use an inner and outer filter its usually a good idea to setup a label that will display if CountRows of the inner filter is ever equal to or greater than the data row limit. That label can have a warning that you may be missing data due to delegation. In your case that would look something like this in the display property of the label.

If(CountRows(Filter(SOWTimeLine, SOWID = SOWMasterItem.ID and Hidden = False))>= 500,true, false)

Was this reply helpful? Yes No
WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

I will throw my 20c worth in - this should take care of the Delegation (as long as the With() filter returns less than your Delegation limit. I think I have the bottom part right, but not sure

With( { wSOW: Sort( Filter( SOWTimeLine, SOWID=SOWMasterID && !Hidden ), DueDate ) } Filter( wSOW, Completed = !TL_ShowcaseComplete ) )

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.

Was this reply helpful? Yes No
Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
I might be missing something but can't the Filter() formula be tweaked to be delegable and not use the Switch()?

I was thinking something like this:

Sort( Filter( SOWTimeLine, SOWID = SOWMasterItem.ID And Hidden = false And ((TL_ShowComplete.Value = false And Completed = false) Or TL_ShowComplete.Value = true) ), DueDate , Ascending )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Drrickryp,
TL_ShowComplete is actually just a checkbox object in Powerapps, "Completed" two lines below is the SP field, and it is indeed a yes/no field. I tried changing it in SP along with false -> "false" in the formula, but still get the same error.

Thanks for the link! Bookmarked for later!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Jeff_Thorpe
Sorry, I wasn't very clear. TL_ShowComplete is a checkbox user input in Powerapps. The SharePoint list is being filtered by the inner filter first and, the outer one is filtering down to items that have {Completed: false}, when TL_ShowComplete is unchecked in the app (and vice versa of course).

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz
Thanks for your help once again. You're everywhere!

I didn't do a very good job of explaining what I was trying to do... I need the filter to show me records with {Completed: false} when TL_ShowComplete is unchecked in the app, and {Completed: true AND false} when it is checked, hence the Switch(). I tried your solution out anyway though and found that it gave me a different delegation warning:

I think I'm ok based on Psork1's response, but it was interesting to me that putting the filter inside of the With() function caused the warning where there was none before so I thought I would share.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 Thanks for the tip! In this case, I'm very certain that there won't be anywhere near 500 records, as the user would have to enter them all manually, but I will definitely be using that warning label in other areas.

Was this reply helpful? Yes No
WarrenBelz 155,283 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Yes, danger of free-typing without really thinking it through properly - I was trying to get a Delegable data set and forgot about the Boolean in it. This should do the job

With( { wSOW: Sort( Filter( SOWTimeLine, SOWID=SOWMasterID ), DueDate ) }, Filter( wSOW, Completed = !TL_ShowcaseComplete && !Hidden ) )

You might find this blog of mine on the subject of some use to you.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz
With some tweaks(couple typos and a missing comma after the CurlyClose), that does get rid of the delegation warning, but it still doesn't address my underlying need. Essentially, I need to check a box in the app to toggle the second filter on and off so that I either filter out completed items or show everything.

Thanks for the link, I actually came across the same one a little earlier via this blog that @Drrickryp shared: https://warrenbelz.blogspot.com/2020/08/power-apps-delegation-is-word-not.html

I'll definitely be taking a look though it later on!

Was this reply helpful? Yes No