Help with Large Sharepoint List

[SqueezeOJ]

Hello,

I have a Sharepoint list (called LD8-201717) with nearly 10,000 client records, organized by LastName, FirstName. 

I need to make a simple PowerApps app to search that list and return results.  So, I created a default app.

Problem is that BrowseGallery1 asks for all items in the list and that throws error: 

The attempted operation is prohibited because it exceeds the list view threshhold enforced by the administrator.

I don't need to see all items in the list - just the ones with LastName = "SHAW"  (my last name).  So, I changed it 

BrowseGallery1.Items to say:

Filter('LD8-201717', StartsWith(LastName, "SHAW"))

However, now it tries to load, but the BrowseGallery just stays blank.

Is it possible to filter a large Sharepoint list?

If so, then what am I doing wrong?  And how can I fix it?

Thanks,

Jason

[KeremY · Accepted solution]

Hi @SqueezeOJ, what you need is some indexes to make this list work with your queries. 

Go to your list settings, and find "Indexed Columns". Add indexes to the columns that you want to sort by, or filter by. There may already be some indexes, there, but feel free to add your own. 

Now, it should be possible to sort by first name or by last name, and then to do a StartsWith query.

Hope that helps. Let me know if you need more help. 

[SqueezeOJ]

Thank you for your help!  That worked like a charm.

How to add indices to Sharepoint List: https://support.office.com/en-us/article/Add-an-index-to-a-SharePoint-column-f3f00554-b7dc-44d1-a2ed...

[tianaranjo]

@SqueezeOJ

Are you able to search and filter a large SP list using where indices are created?  If so, can you describe, in detail, how you did it in your PowerApp?  I have  a need to search a list of 33k items.  I have 11 indices created.  I am unable to figure out how to fetch the data from my app and bring in only the records I have requested via the search criteria submitted by a user. 

If it's working for you and you can share, I would greatly appreciate it.

[KeremY]

Hi @tianaranjo, as long as you search/filter by a simple text or number column that is indexed using the = or starts with operators, it should work. We don't yet support "delegated queries" (meaning queries executed on the server) that involve more complex column types like choice, people, managed metadata, and lookups into other lists. 

[denglish]

@KeremY - Is this still the case? Can delegated queries of larger lists include the more complex delegable functions and column types?

[Anonymous]

Well I am having 60+ columns and 20 columns are already indexed .Any other way we can achieve this