Does delegation extend to nested filters?

[Anonymous]

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!

[Drrickryp]

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. 

[Pstork1 · Accepted solution]

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)

[WarrenBelz]

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.

[Jeff_Thorpe]

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 
)

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[WarrenBelz]

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.