cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sababneh
Advocate III
Advocate III

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
dinusc
Microsoft
Microsoft

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

Drrickryp
Super User II
Super User II

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

21 REPLIES 21
dinusc
Microsoft
Microsoft

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

Drrickryp
Super User II
Super User II

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

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

 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

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

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?

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

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

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
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 is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (79,450)