Solving complex delegation warning to show row ids above 2000 - 8 filters and 1 search [2000 Data Row Limit] [Delegation Warning]

[tpj1215]

Hi Powerapp experts,

I hope to solve this complex delegation warning which I think can solve showing row ids above 2000.

The below function worked even though I had delegation warning until row id hit 2000. 

Now I have row data above 2000, row ids above 2000 do not appear in the gallery at all even though data exists in the sharepointlist.

I looked into so many youtube videos and searched online but could not find the answer for this case.

I hope someone with similar challenges or super expert in Powerapps can share his/her solution!

Data source: sharepointlist

8 filters' data type: all single line of text; all Combo box

1 search data type: single line of text; Text input

LastN(Sort(Search(Filter(Data_Source,
(IsBlank(Class_Filter.SelectedItems) || IsEmpty(Class_Filter.SelectedItems) || Class in BU_Filter.SelectedItems) &&
(IsBlank(Quarter_Filter.SelectedItems) || IsEmpty(Quarter_Filter.SelectedItems) || 'Current Qtr' in Quarter_Filter.SelectedItems) &&
(IsBlank(QtrType_Filter.SelectedItems) || IsEmpty(QtrType_Filter.SelectedItems) || Qtr_Type in QtrType_Filter.SelectedItems) &&
(IsBlank(CurrentWK_Filter.SelectedItems) || IsEmpty(CurrentWK_Filter.SelectedItems) || CurrentWK in CurrentWK_Filter.SelectedItems) &&
(IsBlank(Month_Filter.SelectedItems) || IsEmpty(Month_Filter.SelectedItems) || Month in Month_Filter.SelectedItems) &&
(IsBlank(Customer_Filter.SelectedItems) || IsEmpty(Customer_Filter.SelectedItems) || Customer in Customer_Filter.SelectedItems) &&
(IsBlank(Check_Filter.SelectedItems) || IsEmpty(Check_Filter.SelectedItems) || Check in Check_Filter.SelectedItems) &&
(IsBlank(Level_Filter.SelectedItems) || IsEmpty(Level_Filter.SelectedItems) || Level in Level_Filter.SelectedItems) &&
(Valid =1 )
), ID_Search.Text, "field_4", "field_17"), ID_Num, SortOrder.Descending),500)

[WarrenBelz]

Hi @tpj1215 ,

You can separate the Delegable parts as below (I have simply copied your filter and assumed it is valid)

With(
   {
      _Data:
      Sort(
         Filter(
            Data_Source,
            (IsBlank(Class_Filter.SelectedItems) || IsEmpty(Class_Filter.SelectedItems) || Class in BU_Filter.SelectedItems) &&
            (IsBlank(Quarter_Filter.SelectedItems) || IsEmpty(Quarter_Filter.SelectedItems) || 'Current Qtr' in Quarter_Filter.SelectedItems) &&
            (IsBlank(QtrType_Filter.SelectedItems) || IsEmpty(QtrType_Filter.SelectedItems) || Qtr_Type in QtrType_Filter.SelectedItems) &&
            (IsBlank(CurrentWK_Filter.SelectedItems) || IsEmpty(CurrentWK_Filter.SelectedItems) || CurrentWK in CurrentWK_Filter.SelectedItems) &&
            (IsBlank(Month_Filter.SelectedItems) || IsEmpty(Month_Filter.SelectedItems) || Month in Month_Filter.SelectedItems) &&
            (IsBlank(Customer_Filter.SelectedItems) || IsEmpty(Customer_Filter.SelectedItems) || Customer in Customer_Filter.SelectedItems) &&
            (IsBlank(Check_Filter.SelectedItems) || IsEmpty(Check_Filter.SelectedItems) || Check in Check_Filter.SelectedItems) &&
            (IsBlank(Level_Filter.SelectedItems) || IsEmpty(Level_Filter.SelectedItems) || Level in Level_Filter.SelectedItems) &&
            (Valid = 1 )
         ),
         ID_Num, 
         SortOrder.Descending
      )
   }, 
   LastN(
      Search(
         _Data,
         ID_Search.Text, 
         "field_4", 
         "field_17"
      ), 
      500
   )
)

Bear in mind that the top filter needs to return record numbers under you Delegation limit (it can filter any number of records).

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tpj1215]

Is there a way to replace "in" since it's not delegable?

[tpj1215]

This script gives error..

[WarrenBelz]

@tpj1215 ,

Your code was so bulky, I missed that, so please disregard my last post. It should not error as I simply copied your filter (what is the error ?)

Other than Valid = 1, your entire filter is not Delegable. If all of your Combo Boxes are multi-select, there is no workaround, if some are single selections, then something can be achieved. Please advise which ones are single and the Items of these.