Search function not searching all the rows - cap at first 500 records

[wangl2]

Dear Forum Members,

My App has an Excel data source which contains over 3000 rows. There are names from A - Z. I just released the Search Function under the Browse Gallery is not searching all the rows. It is only display results of first 500 rows.

Here is the formula for Browse Gallery (The first screen).

SortByColumns(Search([@Table1], TextSearchBox1.Text, "Surname","Preferred","ID","New_x0020_Computer_x0020_Name"), "Surname", If(SortDescending1, Descending, Ascending))

I didn't define the actual "Search Items" text box.

This problem is so subtle and I am glad I discovered this problem now. Otherwise it will be disaster if deployed.  

Thank you in advance.

[mdevaney · Accepted solution]

@wangl2 

There are two problems here.

#1  Excel has a hard limit of 2,000 rows when being used as a datasource [a].  No matter how we write the formula it will only be able to look at the first 2,000 rows even if there are 3,000 already in the spreadsheet.  Therefore you must use a different datasource instead.  SharePoint supports tables over 2,000 rows in PowerApps and has no additional cost so it should be the first alternative you consider.

#2  The Search function will only look at the first 2,000 rows even in SharePoint [b] so you shouldn't use it here.  The workaround most people use right now is the FILTER + STARTSWITH function which does not have any limit.  Its not a perfect but its the only thing we've got.

Filter(
    Table1, 
    StartsWith(Surname, TextSearchBox1.Text)
    Or StartsWith(Preferred, TextSearchBox1.Text)
    Or StartsWith(ID, TextSearchBox1.Text)
    Or StartsWith('New Computer Name', TextSearchBox1.Text)
)

If you must have the Search function you will need to use SQL or Common Data Service as the datasource but that will cost more money to license these products as well as a PowerApps premium license for each user.

Note:  I said 2,000 rows is the limit in Excel.  Right now you are only seeing 500.  You can increase this to 2,000 in the Advanced Settings.

Supporting Documents

[a] See section called Delegable Datasources

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

[b] See diagram showing delegable functions is SharePoint

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."