using search function for large data sets

[OPGPowerAPPS]

Hi,

I am trying to use the search functionality on a large sharepoint data set (170,000 + entries), however delegation is not supported on the search function with sharpoint data sets. I am currently using the Filter function as a work around, but this method is too slow and inconvenient when seaching for something.

Current Code: If(Connection.Connected,Filter('Maintenance Applications', StartsWith(Title, TextSearchBox1.Text) || StartsWith(txyv, TextSearchBox1.Text) || StartsWith(pqeh, TextSearchBox1.Text)),Table({Title:" ", pqeh:" ", txyv:" ", sply:" ", vlev:" ", in9s:" ", j2ao:" "}))

Is there a work around to have the same functionality as the search function ( ex. when searching for 63481 i get 3-63481-LT43, where as the Filter function would return a blank)?

[Drrickryp]

Hi @OPGPowerAPPS,

To get to use the Search functionality, you will need to pare down your list to no greater than 2000 rows using delegatable functions like Filter(), StartsWith() and Sort().  Then create a collection from the resulting list.  You can then use Search on the collection.  As you have discovered, SharePoint is not the best platform for very large lists because of the delegation issues. Sql or Common Data Service will handle them much better.  Of the two, Sql is the most versatile.  For a comparison of the various platforms please see: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list.  If this answers your question, please mark the thread as Solved.

[Drrickryp]

If  possible, use cascading Dropdown boxes to pare down the list to something manageable with Search(). 

[Shanescows · Accepted solution]

I agree you are never going to be happy with a PowerApps app that is using a SharePoint list with 17,000 items. That type of solution is going to be better in CDS or SQL. 

[OPGPowerAPPS]

If i were to use SQL would there be a way to have the SQL server update based on the sharepoint data. I've never used SQL so i am not familiar with how it operates.