cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SAMUELAL
Resolver I
Resolver I

Delegation warning, filtering large Gallery

I have a Gallery that filters a SharePoint List of 10,000 rows using a DropDown box and a Text Input Box. How can I edit the formula to best avoid delegation warnings?

 

Gallery

Items = Filter('HSS Trial Data-019026f8-5109-44da-a6a1-8394edabceec', StartsWith(Title,ResourceDropDown.SelectedText.Value) And StartsWith('Serial Number',SerialSearchBox.Text))

 

Best regards,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

Will either of the StartsWith() functions get you below 2,000 items by itself?  If so then I would suggest nesting the filters something like this.

 

Items = Filter(Filter('HSS Trial Data-019026f8-5109-44da-a6a1-8394edabceec', StartsWith(Title,ResourceDropDown.SelectedText.Value)), StartsWith('Serial Number',SerialSearchBox.Text))

 

  It won't necessarily get rid of the delegation warning, but it will get you all the records despite the warning as long as the inner filter gets you below the Data Row limit. So for example, assuming an even distribution the first letter can be any one of 26 letters.  10,000/26 = 384 items which is well below a data limit of 2,000.  Filter() and StartsWith() by itself should be fully delegable.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

View solution in original post

9 REPLIES 9
timl
Super User
Super User

@SAMUELAL 

Can you confirm whether or not you see a delegation warning here? StartsWith is a delegable function with SharePoint so I can't quite see where the problem lies.

Pstork1
Dual Super User
Dual Super User

Will either of the StartsWith() functions get you below 2,000 items by itself?  If so then I would suggest nesting the filters something like this.

 

Items = Filter(Filter('HSS Trial Data-019026f8-5109-44da-a6a1-8394edabceec', StartsWith(Title,ResourceDropDown.SelectedText.Value)), StartsWith('Serial Number',SerialSearchBox.Text))

 

  It won't necessarily get rid of the delegation warning, but it will get you all the records despite the warning as long as the inner filter gets you below the Data Row limit. So for example, assuming an even distribution the first letter can be any one of 26 letters.  10,000/26 = 384 items which is well below a data limit of 2,000.  Filter() and StartsWith() by itself should be fully delegable.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

There are 3 delegation warnings:

  1. "Highlighted part of this formula might not work correctly with column "Serial Number" on large data sets."
  2. "The "Filter" part of this formula might not work correctly on large data sets"
  3. "The "Filter" part of this formula might not work correctly on large data sets"
timl
Super User
Super User

Hi @SAMUELAL 

I think the problem for this delegation warning is due to the data type of 'Serial Number'. Is it a calculated column, or some other type of non-text data type?

I reuploaded my table as all text to make sure, but I am still having delegation issues

 

SAMUELAL
Resolver I
Resolver I

Update:

I made sure that the excel spreadsheet was all text before making it a share point list. 

DataTable:
Items = Filter(Filter('HSS Trial Data02', StartsWith(Title,ResourceDropDown.SelectedText.Value)), StartsWith('Serial Number',SerialSearchBox.Text))

 

It now appears that I can reach up to 3800 rows before it stops pulling data (my goal is 10,000).

You won't get that high using Excel as a source.  Excel doesn't do delegation at all and has specific size limits that will keep you from dynamically importing that large a file.  You can probably import it as a static data source, but you won't be able to do anything requiring delegation.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

My apologies, I should have been more specific. I get a report in excel and I then use it to create a SharePoint List. The SharePoint list is the information source that I am pulling from.

Thank you. Having the nested filter helps. The app still won't work with data more that 5,000 rows, but this is good enough for me for now.

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,609)