SharePoint List Search exceeding 2000 Search / Filter help.

[Anonymous]

Hi Guys, I am back again with a very frustrating and unresolved issue 4 weeks on. 

Trying to find a solution on the board with changing Search & Filter on my formula but with no success.

I have a sharepoint list with over 2000 lines and I am trying to combine the Searchbox and dropdwon to filter a gallery view to give back defined results. It works today but not on any lines over 2000 in a sharepoint list. 

I dont clearly understand "Delegation" to resolve , I have indexed the columns in SharePoint that the formula below will search in for results and that has had no effect.  

My current  formula which works under 2000 lines perfectly.

If(Dropdown1_3.SelectedText.Value = "All", Search('SHAREPOINTLISTNAME', TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "SupplierSub_x002d_Category", "Catrgory", "Title"),Search(Filter('SHAREPOINTLISTNAME', Category=Dropdown1_3.Selected.Value), TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "Catrgory", "Functionality", "SupplierSub_x002d_Category", "Title"))

Can I or how can I create a complete collection of the SharePoint list and bring it to the APP to search within instead of sending the search to SharePoint. Would that work as I am giving up hope, Frustrated that we have this great SharePoint Online list function from Microsoft,(capable of over 20,000 records) and another Microsoft product "Powerapps" which cannot easily search more than 2000 lines!!!

In understand SQL as a data source does not have the challenge but this is not an option to change over. 

Greatly appreciate any help as I have not found a solution in the forum as yet.

[Pstork1]

The basic problem is that Search() won't work on a list with more than 2,000 items.  It will only search the first 2,000 items in the list.  There is no way to get around that other than not trying to search that big a chunk.  The only solution is to use other strategies to get the number of records searched below that 2,000 record limit.  There are a couple ways to do this.

1) Use Filter() and startsWith() instead of search.  A filter will work with as many records as you have and StartsWith() lets you search for partial matches.  This is often the same as using Search.

2) Use an inner Filter() to decrease the number of records the Search is being done on to less than 2,000. Sort() could also be used to get the items being Searched in the first 2,000. You'll still see a delegation warning, but it won't effect what the search returns.

3) As a last resort you can find a way to import the SharePoint list into a local collection and search that.  Although this avoids the delegation issue it normally creates a big performance issue.

[Anonymous]

Hi Pstork1,  Thanks for coming back. I can try use "Startswith" instead of "Search" but that takes away the key work search function in the App I presume. For example if I have a phrase "BIG BLUE CAR" in a column and I use the function "Startswith" to look in that column it wont return results for BLUE or CAR. It will only work on BIG as it is searching a field using "Startswith" i presume. 

Incredibly frustrating that the word "Search" is not valid to work above 2000 lines in Sharepoint list when both products are Microsoft!!!

[Anonymous]

Hi Pstork1,

Still now look with replacing Search with Filter and Starts with. This has just messed up my formula.

OLD FORMULA

If(Dropdown1_3.SelectedText.Value = "All", Search('KEU Innovation Pantry Database 2020', TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "SupplierSub_x002d_Category", "Catrgory", "Title"),Search(Filter('KEU Innovation Pantry Database 2020', Category=Dropdown1_3.Selected.Value), TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "Catrgory", "Functionality", "SupplierSub_x002d_Category", "Title"))

TESTING

If(Dropdown1_3.SelectedText.Value = "All", Filter(StartsWith('KEU Innovation Pantry Database 2020', TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "SupplierSub_x002d_Category", "Catrgory", "Title"),(Filter(StartsWith('KEU Innovation Pantry Database 2020', Category=Dropdown1_3.Selected.Value), TextInput1_Otheringredients_2.Text,"OtherIngredients", "IngredientDescription", "Catrgory", "Functionality", "SupplierSub_x002d_Category", "Title"))

[Anonymous]

Hi All,

Could I create a Collection" in the App and search this collection instead of the Sharepoint list to get all results over 2000. Is the Search function delegate in a collection instead of SharePoint. Its increadible the efforts people designing a powerapp have to go to as the word "Search" cannot do its basic function on a list over 2000 lines.

Would the search function work on an excel list over 2000 lines instead. 

[Pstork1]

Creating a collection was option #3.  The problem is that it can take literally minutes to load a large data source into a collection.  Excel also won't really work since it has limits also and doesn't support delegation at all.

Delegation is the name that Power Apps uses, but its a common concept in any database design.  You can't afford to pull the entire data set into the application.  You have to offload record selection to the database itself.  That's what delegation does.  It has the database do the work and only bring back the necessary data.  The problem is that depending on the platform there are limitations in terms of what the system can do. A full text search of a SharePoint list can't be handled by SharePoint in the list itself.  That is done in SharePoint using the Search subsystem.  At this point that Full text search isn't integrated into what Power Apps connector does.  Other databases like Sql and Dataverse have more support in that area because they are actual databases, which SharePoint isn't and was never designed to be.