Delegation warning, filtering large Gallery

[SAMUELAL]

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,

[timl]

@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 · Accepted solution]

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.

[SAMUELAL]

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]

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?

[SAMUELAL]

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

[SAMUELAL]

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

[Pstork1]

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.

[SAMUELAL]

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.

[SAMUELAL]

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.