Delegation warning. The "Filter" part of this formula might not work correctly on large data sets (gallery)

[kinghnvn]

Hi all,

I am getting the delegation warning on the gallery when I am using the below code to filter data into my gallery.
Who knows how to solve it?

Filter(
Desks,
Not(
Title in Filter(
'Desk Reservations',
'Check Out From Number' >= Value(Text(startTime,"yyyymmddhhmm")) && 'Check Out To Number' <= Value(Text(endTime,"yyyymmddhhmm")) || 'Check Out From Number' <= Value(Text(endTime,"yyyymmddhhmm")) && 'Check Out To Number' >= Value(Text(endTime,"yyyymmddhhmm"))
).'Desk Text'
)
&& Active = 1
&& (DropdownOffice.Selected.Result = "Please select office" || OfficeName = DropdownOffice.Selected.Result)
&& (DropdownArea.Selected.Result = "All" || Area = DropdownArea.Selected.Result)
)

[WarrenBelz]

@kinghnvn ,

You have three non-delegable items there - the in Filter, the Not() argument and the relational lookup to 'Desk reservations'. You will have to re-think you data structure unless you have lists under 2,000 items and make collections for the query.

Also why are you using the complex formula on the dates when you can simply compare them directly.

[kinghnvn]

@WarrenBelz 

You have three non-delegable items there - the in Filter, the Not() argument and the relational lookup to 'Desk reservations'. You will have to re-think you data structure unless you have lists under 2,000 items and make collections for the query. -> Yes, my list under 2,000 items so I can ignore that?

Also why are you using the complex formula on the dates when you can simply compare them directly. -> Could you please help to edit it?

Many thanks.

[WarrenBelz]

@kinghnvn

Firstly your formula on the dates suggests you have numbers you are comparing (instead of dates) - is this correct? If they were simply dates and you will never have more than 2,000 items and you have your limit set to that) or the newest 2,000 items will do the job, you can get rid of the warning with

With(
   {
      wDesks:
      Sort(
         Desks,
         ID,
         Descending
      ),
      wReserve:
      Sort(
         'Desk Reservations',
         ID,
         Desending
      )
   },
   Filter(
      wDesks,
      !(
         Title in 
         Filter(
            wReserve,
            (
               'Check Out From' >= startTime && 
               'Check Out From' <= endTime
            ) || 
            (
               'Check Out To' >= startTime && 
               'Check Out To' <= endTime
            )
         ).'Desk Text'
      ) && 
      Active = 1 &&
      (
         DropdownOffice.Selected.Result = "Please select office" || 
         OfficeName = DropdownOffice.Selected.Result
      ) && 
      (
         DropdownArea.Selected.Result = "All" || 
         Area = DropdownArea.Selected.Result
      )
   )
)

You also had endTime in there three times instead of two.

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.

Visit my blog Practical Power Apps

[kinghnvn]

@WarrenBelz 

Firstly your formula on the dates suggests you have numbers you are comparing (instead of dates) - is this correct? -> Yes, that's correct, below is my SharePoint list

If I use your code, it will show 11 errors

with my code, only 4 warning messages

[WarrenBelz · Accepted solution]

@kinghnvn ,

The code was more to show you the structure required and was free-typed (I do not have the advantage of the red and blue lines) missing one bracket (now corrected).

More important was the question I asked - why are you using numbers instead of the dates they refer to as Date queries are Delegable - your Delegation issues were elsewhere.

Please take a moment to understand what I have posted as it will assist you greatly in the future.

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.

Visit my blog Practical Power Apps

[kinghnvn]

thank you so much @WarrenBelz