Filtering delegation

[bquirosh]

Hello,

I'm trying to create an app to filter within positions of a company, I'm using an Excel Sheet as data source that includes around 2400 rows of data and I'm having the delegation issue just showing 2000 rows of data and need to show everything. I have searched around with no luck even with ChatGPT and need some help.

This is what I have. I'm using five filters to filter a gallery, this is the code for the drop down filters:

Since there are 5 filters, each collect function would go from 1 to 5, and the filter criteria change from Location, Geo, Sub Family, Regional Title and Level (I wont be adding the code for each filter, but they are the same.

OnSelect:

ClearCollect(CollectDD2, {Value: "All"});
Collect(CollectDD2,Sort( Distinct(Filter(USData,
    (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
    (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
    (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
    (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
    (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value)), Location),Value))

 Items:

CollectDD2

This is my gallery code:

Items:

Search(
Filter( 
    USData,
    (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
    (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
    (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
    (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
    (DDLevel.Selected.Value = "All"  Or Level = DDLevel.SelectedText.Value) 
) ,
    TextSearchBox1.Text,"Sub_x0020_Family","Regional_x0020_Title") 

I will really appreciate any help on this!! 

[WarrenBelz · Accepted solution]

Hi @bquirosh ,

You have a non-Delegable function in each - so collection

ClearCollect(CollectDD2, {Value: "All"});
With(
   {
      _Data:
      Filter(
         USData,
         (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
         (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
         (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
         (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
         (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value)
      )
   },
   Collect(
      CollectDD2,
      Sort(
         Distinct(
            _Data,
            Location
         ),
         Value
      )
   )
)

Gallery

With(
   {
      _Data:
      Filter(
         USData,
         (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
         (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
         (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
         (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
         (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value)
      )
   },
   Search(
      _Data,
       TextSearchBox1.Text,
      "Sub_x0020_Family",
      "Regional_x0020_Title"
   )
)

Caution here - these will only work fully if the output (number of items after the filter) is under your Data Row Limit.

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

[bquirosh]

Hi @WarrenBelz 

Thanks a lot for your response.

I just to clarify, would this section of code would be for the filter drop downs?

With(
   {
      _Data:
      Filter(
         USData,
         (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
         (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
         (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
         (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
         (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value)
      )
   },
   Sort(
      Distinct(
         _Data,
         Location
      ),
      Value
   )
)

If that's the case, how can I include the "All" Option I included with my initial collect function? 

I need to have that option so it shows All depending on what the user selects. 

Thanks again for your help! 

[WarrenBelz]

Hi @bquirosh ,

I only changed the bits that needed changing - please see amended code now with Collection

[bquirosh]

Hi @WarrenBelz 

Thank you again for your response and help.

I added the code you provided to me , I filtered the data in the app which is supposed to bring less than 2000 after filtered, but yet it didn't show the items that in the Excel File are after the 2000 row limit,

Not sure if I'm doing something wrong, I didn't get any error message with the code so it should be working. Could you please help me to figure what might be the issue? 

Thanks! 

[bquirosh]

Hi @WarrenBelz ,

Not sure if having the data in Excel(One Drive) is causing the delegation issue? Would it be better to host it in a SharePoint List? 

[WarrenBelz]

@bquirosh ,

I provided the code in the hope that maybe Excel would handle it, however it appears that is not the case. Excel is a terrible data source with limited delegation capabilities and it frequently fails to address complex filters. Yes - SharePoint lists would be much better as that code should work there.

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

[bquirosh]

Thank you @WarrenBelz 

I have moved the data to SharePoint , however, I'm now encountering an issue with the following lines and I'm not able to figure what's the issue, it says there is an invalid argument: 

I have added comments in the line that is not working

With(
   {
      _Data:
      Filter(
         USData,
         (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
         (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And  
         (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
         (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
         (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value)
      )
   },
//This set of code for the Search field is giving me an error 
   Search(
      _Data,
       TextSearchBox1.Text,
      "Sub_x0020_Family",
      "Regional_x0020_Title"
   ) 
)

Appreciate any help on this! 

[bquirosh]

Hi @WarrenBelz 

I'm using this version of code to filter the data in the gallery and seems to be working to show more than 2000 rows of data using SharePoint, however, it is making the time to load a lot longer when filtering and is not very user friendly, do you know if this can be improved?

Filter(
    USData, // Reemplaza "TuTabla" con el nombre real de tu tabla en SharePoint
    (DDLocation.Selected.Value = "All" Or Location = DDLocation.Selected.Value) And
    (DDGeo.Selected.Value = "All" Or Geo = DDGeo.Selected.Value) And
    (DDSubFamily.Selected.Value = "All" Or 'Sub Family' = DDSubFamily.SelectedText.Value) And
    (DDRegional.Selected.Value = "All" Or 'Regional Title' = DDRegional.SelectedText.Value) And
    (DDLevel.Selected.Value = "All" Or Level = DDLevel.SelectedText.Value) And
    StartsWith('Sub Family', TextSearchBox1.Text) 
    
)

Thanks again.

[WarrenBelz]

Hi @bquirosh ,

Assuming you need to filter by those six controls, that is the best you will do. Also with any data source (including local) , the more complex the filter, the more "lag" you will experience.

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