cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kinghnvn
Helper V
Helper V

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

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

kinghnvn_0-1637892196364.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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

 

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

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

 

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

 

@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

@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

 

kinghnvn_0-1638063006595.png

If I use your code, it will show 11 errors

kinghnvn_1-1638063309051.png

 

with my code, only 4 warning messages

kinghnvn_2-1638063355329.png

 

@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

 

View solution in original post

thank you so much @WarrenBelz 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (3,223)