Answered

Collections with a filter on a large dataset?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

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

Can I load the entire list into a Collection or will it stop pulling at the 2000th row?
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.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

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#M71518

https://powerusers.microsoft.com/t5/Building-Power-Apps/Loading-60-000-SharePoint-List-Items/m-p/448633/highlight/false#M133427

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-sources

Best regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

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,