Delegation Warning and Filtering

[Anonymous]

Hello, 

I've been trying to figure out how to filter through 4,000 files on PowerApps for the past few days. I am not having any luck, so i thought I would post my problem here. My code is below. In the picture you can see that the first files are in the 4,000 area while the ones below it are below 600. I know when I use the Sort Columns code it starts from the top, but that code isn't delegable from what i understand. Overall, my goal is to be able to filter through 4,000 items rather than the 500 powerapps only loads.

Filter(
'Cleared Stories',
StartsWith(
Title,
SearchTxtTask
),
(CoopFilter.Selected.Value in Cooperator.Value || Len(CoopFilter.Selected.Value) = 0) && (StoryFilter.Selected.Value in Story_x0020_Type.Value || Len(StoryFilter.Selected.Value) = 0) && (ComFilter.Selected.Value in Commodity.Value || Len(ComFilter.Selected.Value) = 0) && (CRFilter.Selected.Value in 'Country/Region'.Value || Len(CRFilter.Selected.Value) = 0)
,
"ID",
SortOrder.Descending
)

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

I will give you two options here as I do not know if your choice fields are single or multiple choice. If multiple, the best you are going to do is "pre-filter" the records to under your limit with your Search box - this can work before or after you select the others

With(
   {
      wData:
      Sort(
         Filter(
            'Cleared Stories',
            StartsWith(
               Title,
               SearchTxtTask
            )
         ),
         "ID",
         SortOrder.Descending
      )
   },
   Filter(
      wData,
      (CoopFilter.Selected.Value in Cooperator.Value || Len(CoopFilter.Selected.Value) = 0) && 
      (StoryFilter.Selected.Value in Story_x0020_Type.Value || Len(StoryFilter.Selected.Value) = 0) && 
      (ComFilter.Selected.Value in Commodity.Value || Len(ComFilter.Selected.Value) = 0) && 
      (CRFilter.Selected.Value in 'Country/Region'.Value || Len(CRFilter.Selected.Value) = 0),
   )
)

If however they are single choice fields, you do not need the in filter

Sort(
   Filter(
      'Cleared Stories',
      StartsWith(
         Title,
         SearchTxtTask
      ) &&
      (Cooperator.Value = CoopFilter.Selected.Value || Len(CoopFilter.Selected.Value) = 0) && 
      (Story_x0020_Type.Value = StoryFilter.Selected.Value || Len(StoryFilter.Selected.Value) = 0) && 
      (Commodity.Value = ComFilter.Selected.Value || Len(ComFilter.Selected.Value) = 0) && 
      ('Country/Region'.Value = CRFilter.Selected.Value || Len(CRFilter.Selected.Value) = 0)
   ),
   "ID",
   SortOrder.Descending
)

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

[JayMagness · Accepted solution]

HI,

I have a program I just did to track all the airports of the world. there are over 75,000 Airports and the most is over 3,500 airports in Dallas. now I set the data row limit to 2,000 in Powerapps. in the case of Dallas this is still going to miss 1,500 records. so, I do clear collects on all the regions to pull and filter. on the type of airport which is less then 2,000 of each time. I create the different lists and add them all together to get the result I want.

The process runs in less than 3 seconds from start to finish and display.

The first section clears the variable, the second section loops through the main dataset to filter out all the airports in the region and the specified type., then I add all the variables together to make the final dataset that is used in the Gallery display. Please note there is a concurrent statement this allows the different loops to run at the same time. but the final step is outside this statement to make sure all the datasets are pulled. 

Here is the code I use:

Clear(APt);
Concurrent(
ClearCollect(
SA1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "Small_airport"
))
),
ClearCollect(
cl1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "closed"
))
),
ClearCollect(
he1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "heliport"
))
),
ClearCollect(
ma1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "medium_airport"
))
),
ClearCollect(
la1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "large_airport"
))
),
ClearCollect(
sb1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "seaplane_base"
))
),
ClearCollect(
ba1,
(Filter(
Airport1_1,
iso_region = DataCardValue27.Text && Type1 = "Balloonport"
))
)
);
ClearCollect(
APt,
SA1,
cl1,
he1,
ma1,
la1,
sb1,
ba1
)

[WarrenBelz]

Hi @JayMagness ,

If you want to save some collections

Clear(APt);
Collect(
    APt,
    Filter(
        Airport1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "Small_airport"
    ),
    Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "closed"
    ),
    Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
       Type1 = "heliport"
    ),
    Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "medium_airport"
    ),
    Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "large_airport"
    ),
   Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "seaplane_base"
    ),
   Filter(
        port1_1,
        iso_region = DataCardValue27.Text && 
        Type1 = "Balloonport"
    )
)

[Anonymous]

Hi Warren,

Thank you for the big help. I was wondering if there is a way to make columns searchable with a text box? Everytime I try to use the IN function in the first section of the code i run into delegation warnings and the filter function in the second section has an error.

[WarrenBelz]

Hi @Anonymous ,

The in Function is not Delegable, but you can use StartsWith or you can use the function in the bottom part of the first code I posted