cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SqueezeOJ
Level: Powered On

Help with Large Sharepoint List

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Power Apps Staff KeremY
Power Apps Staff

Re: Help with Large Sharepoint List

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. 

View solution in original post

4 REPLIES 4
Power Apps Staff KeremY
Power Apps Staff

Re: Help with Large Sharepoint List

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. 

View solution in original post

SqueezeOJ
Level: Powered On

Re: Help with Large Sharepoint List

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
Level 8

Re: Help with Large Sharepoint List

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

Power Apps Staff KeremY
Power Apps Staff

Re: Help with Large Sharepoint List

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. 

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,502)