cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjayne1984
Level: Power Up

Filtering Gallery with large SharePoint list as datasource

Hi everyone

 

I am currently working on an app that has a datasource of a large SharePoint Online list.  I have a gallery in my app that points to a collection and I want my users to be able to search this gallery to find the name of a customer.  I've setup a text input box (txtSearchPerson) and a search button.  The OnSelect for the button is as follows:

 

ClearCollect(CustomerColl,Filter(CustomerList,txtSearchPerson.Text in Name))

 

In the formula bar I get a delegation warning saying it won't work on large datasets and sure enough when I run the search it only searches on the first 500 items in the gallery.  I know I can increase this value but only up to 2,000 items and my list currently has over 10,000.

 

I've checked the list of delegable fields and in is in there so I'm not sure why it won't work?  I've indexed the Name field in my SharePoint list but this has made no difference.  I'd be really grateful if anyone knows of any workaround I can use to make sure my users can search like this on the whole of the list data.

 

Thanks in advance for your help.

3 REPLIES 3
Super User
Super User

Re: Filtering Gallery with large SharePoint list as datasource

@gjayne1984 

You have a couple of choices depending on your data and your design.

One is that you can consider collecting the entire list initially (I wouldn't do it on the button) and then use that as your source for searching or filtering.

The other choice would be to consider using StartsWith instead of In.  StartsWith is delegable on the SharePoint datasource, whereas In will not delegate. 

 

You might want to take a look over this recent posting dealing with large datasources.  There is a post in that thread (about 6 responses in) that I deal with a very similar situation with a large name/contact list in SharePoint.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Community Support Team
Community Support Team

Re: Filtering Gallery with large SharePoint list as datasource

Hi @gjayne1984 ,

Based on the issue that you mentioned, I think you have faced a Delegation issue with your formula.

 

Actually, this Delegation warning issue is related to in operator within your formula. Currently, within PowerApps, the in operator could not be delegated for SP List data source.

More details about delegable functions and operators supported within SP list data source, please check the following article:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

 

As an alternative solution, I think the StartsWith function could achieve your needs. I have made a test on my side, please modify your formula as below:

ClearCollect(
CustomerColl,
Filter(
CustomerList,
StartsWith(Name, txtSearchPerson.Text)
)
)

Please take a try with above formula, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TopShelf-MSFT
Level 10

Re: Filtering Gallery with large SharePoint list as datasource

Thanks for posting in the community @gjayne1984 - can you review the above replies and update the thread if they were helpful? 

 

Thank you,

 

@TopShelf-MSFT 

Helpful resources

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

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

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 (9,746)