cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dr_Watcher
Helper I
Helper I

Collections with a filter on a large dataset?

Hi,

 

I have a question around using Collections. If I have a SharePoint List with 10000 rows (columns: customer_name, region, user_email):

 

  1. Can I load the entire list into a Collection or will it stop pulling at the 2000th row?
  2. If I use a Filter in the Collection and base the filter on user email so that user_email = user().email. Will this look through only the first 2000 records or will it be able to reach 10000? Side note, the number of rows assigned to a user will never exceed 150 rows. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Dr_Watcher ,

If the User_Email column is a Text type column in your SP list, and the Filter function does not face Delegation issue, you could also consider take a try with the following formula:

Set(varUser, User().Email);
ClearCollect(testtable, Filter('DataSource','User_Email' = varUser))  // Filter function does not face Delegation issue, and the result amount is not more than 2000

In addition, if the amount of above Filter function result is more than 2000, I think the above ClearCollect function also could not work (please bulk-load your SP List records into collection firstly). Currently, within PowerApps, you could save 2000 records at most from other Delegable data source into a collection once time.

 

If the Filter function faced Delegation warning issue, you must bulk-load your SP list records into a collection firstly, then apply your Filter function to this collection table.

 

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.

View solution in original post

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @Dr_Watcher ,

For your first question, do you want to load your SP List with 10000 records into a collection in your canvas app? The answer to this question is YES. You could consider bulk-load your SP list records into a collection in your app.

Please check and see if the following threads could help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Pulling-in-large-ish-SQL-tables/m-p/243777#M...

https://powerusers.microsoft.com/t5/Building-Power-Apps/Loading-60-000-SharePoint-List-Items/m-p/448...

 

For your second question, if you apply your Filter formula to the Collection, it would look through the whole records (10000) in your collection. The Collection would not be subject to the Delegation issue in your canvas app.

Please check the following article for more details:

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

 

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.

Hi @v-xida-msft ,

 

Thanks for the response. I only want to load the rows for the specific user into a collection onstart of the canvas app.

Do I have to follow the events that I need to first do a bulk upload of the SP list to a collection and then run the filter or by running the following it starts by only pulling said user's specific rows into the collection?

Set(varUser, User().Email);ClearCollect(testtable,Filter('DataSource','User_Email'=varUser))

 

Thanks

Hi @Dr_Watcher ,

If the User_Email column is a Text type column in your SP list, and the Filter function does not face Delegation issue, you could also consider take a try with the following formula:

Set(varUser, User().Email);
ClearCollect(testtable, Filter('DataSource','User_Email' = varUser))  // Filter function does not face Delegation issue, and the result amount is not more than 2000

In addition, if the amount of above Filter function result is more than 2000, I think the above ClearCollect function also could not work (please bulk-load your SP List records into collection firstly). Currently, within PowerApps, you could save 2000 records at most from other Delegable data source into a collection once time.

 

If the Filter function faced Delegation warning issue, you must bulk-load your SP list records into a collection firstly, then apply your Filter function to this collection table.

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,856)