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

ClearCollect for more than 2000 records

Hello all,

 

I have tried ClearCollect-ing a Sharepoint List that holds more than 2100 records.

 

But unfortunately, when I CountRows for the ClearCollect, it only gives 2000 records.

 

And I also tried to ClearCollect with filtering the Data Source. It only provided me with filtration on the first 2000 records.

 

How can I overcome this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft Employee

Re: ClearCollect for more than 2000 records

There're 2 ways to address this limitation:

1. Use only delegable functions and operators in order to avoid the connection limitation on the number of rows (500 - 2000).  See this for more details: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

2. Load your data in batches by calling the "Collect" multiple times. This may require changing the SharePoint list. For example, you could add a "batchID" field to your list (BatchID=1 for rows 1...500, BatchID=2 for rows 501...1000 and so on). 

View solution in original post

Super User
Super User

Re: ClearCollect for more than 2000 records

Hi Sababneh,

 

I use a third method similar to that described by @dinusc.  If you know the number of items in your datasource (say 2100) you can try

Collect(ds1,datasource); //Collects the first 2000 items

Collect(ds2,FirstN(Sort(datasource,Descending),100)); //Collects the rest of the items

Collect(dsfinal,ds1,ds2) //Combines the two datasources 

 

Mr. Dang had a number of elegant solutions to creating large collections from big datasets when the item limitation was 500.  This is one of them.  https://powerusers.microsoft.com/t5/General-Discussion/Overcoming-the-500-record-limit-locally/td-p/...

Hope this helps you.

View solution in original post

14 REPLIES 14
Highlighted
Microsoft Employee

Re: ClearCollect for more than 2000 records

There're 2 ways to address this limitation:

1. Use only delegable functions and operators in order to avoid the connection limitation on the number of rows (500 - 2000).  See this for more details: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

2. Load your data in batches by calling the "Collect" multiple times. This may require changing the SharePoint list. For example, you could add a "batchID" field to your list (BatchID=1 for rows 1...500, BatchID=2 for rows 501...1000 and so on). 

View solution in original post

Super User
Super User

Re: ClearCollect for more than 2000 records

Hi Sababneh,

 

I use a third method similar to that described by @dinusc.  If you know the number of items in your datasource (say 2100) you can try

Collect(ds1,datasource); //Collects the first 2000 items

Collect(ds2,FirstN(Sort(datasource,Descending),100)); //Collects the rest of the items

Collect(dsfinal,ds1,ds2) //Combines the two datasources 

 

Mr. Dang had a number of elegant solutions to creating large collections from big datasets when the item limitation was 500.  This is one of them.  https://powerusers.microsoft.com/t5/General-Discussion/Overcoming-the-500-record-limit-locally/td-p/...

Hope this helps you.

View solution in original post

Sababneh
Level: Powered On

Re: ClearCollect for more than 2000 records

Okay thank you guys. 🙂

One last question.

 

I'm using Sharepoint List as a data source,

 

Can I use these on Sharepoint List:

 

  1. IsBlank
  2. Not
  3. in

If it was less than 2000 rows?

Because it always gives me a blue line error. Can I ignore it? Because I don't always want to use Clear Collecting because I have 600+ users using the app.

 

Thanks

Microsoft Employee

Re: ClearCollect for more than 2000 records

 Unfortunately, IsBlank, Not, In are currently not delegable in SharePoint. 

However, if you intend to pull a number of records that is less than the maximum allowed (500 by default iwhich can be configured to up to 2000 in app settings) then you can safely ignore the blue icon warning. 

Anonymous
Not applicable

Re: ClearCollect for more than 2000 records

Afternoon guys,

 

Just wanting to know, if I have a database in which the application is populating and this database could go well over 10,000->1,000,000 records. Is there a way of pulling the information recorded only in the last 12 hours? (Currently I'm using a filter for the last 12 hours.)

Cheers,

Lazz

Microsoft Employee

Re: ClearCollect for more than 2000 records

That depends on the database you use. Please see this page with information about what operations can be delegated on which databases: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

Please note though that you will not be able to pull a number of records greater than the maximum allowed (500 by default, configurable up to 2000). 

Does that makes sense?

Super User
Super User

Re: ClearCollect for more than 2000 records

Hi @Anonymous,

If you sort the table descending and then collect the table, you will get the last 2k items. That would certainly include the last 24hrs

Anonymous
Not applicable

Re: ClearCollect for more than 2000 records

Morning Drrickryp,

This would definately work.

So let's say I have the below,

Button1 - Patches a record

Database - Name, Date, Time, Location, ColumnA, Column B, etc

NameDropdown - Name of person

Label1 - Want to show how many records based on some filtering (Person, Today)
Gallery - Want to filter some common details to show grouped records. (Person, Today, Location)

Without Collect, the below counts the amount of records until it reaches the 500-2000 limit.
Label1 - Text=Countif(Database, Text(NameDropdown.Selected.Value, Text)=Name, DateTimeValue(Date&" "&Time) > Now()-0.5)


Without Collect, the below counts the amount of records until it reaches the 500-2000 limit.

Gallery - Items=GroupBy(Filter(Search(Database, Text(NameDropdown.Selected.Value, Text), "Name") DateTimeValue(Date&" "&Time) > Now()-0.5, "Location", "ColumnA", "ColumnB", etc, "GroupData1")

 

Then I simply add labels with "Location", "ColumnA" etc in their Text feilds.


Cheers,

Lazz

Anonymous
Not applicable

Re: ClearCollect for more than 2000 records

Morning guys,

I think I've worked it out as per the below.
So in otherwords, it'll only grab the last 2000 records from the database.

 

Button1 - Patches a record

Database - Name, Date, Time, Location, ColumnA, Column B, etc

NameDropdown - Name of person

Label1 - Want to show how many records based on some filtering (Person, Today)
Gallery - Want to filter some common details to show grouped records. (Person, Today, Location)

Without Collect, the below counts the amount of records until it reaches the 500-2000 limit.
Label1 - Text=Countif(Sort(Database, Date, Descending), Text(NameDropdown.Selected.Value, Text)=Name, DateTimeValue(Date&" "&Time) > Now()-0.5)


Without Collect, the below counts the amount of records until it reaches the 500-2000 limit.

Gallery - Items=GroupBy(Filter(Search(Sort(Database, Date, Descending), Text(NameDropdown.Selected.Value, Text), "Name") DateTimeValue(Date&" "&Time) > Now()-0.5, "Location", "ColumnA", "ColumnB", etc, "GroupData1")

 

Then I simply add labels with "Location", "ColumnA" etc in their Text feilds.

Cheers,

Lazz

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 150 members 5,351 guests
Please welcome our newest community members: