cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SqueezeOJ
Frequent Visitor

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
KeremY
Power Apps
Power Apps

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

5 REPLIES 5
KeremY
Power Apps
Power Apps

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
Frequent Visitor

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
Continued Contributor
Continued Contributor

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

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
Advocate II
Advocate II

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,284)