ClearCollect for more than 2000 records

[Sababneh]

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?

[dinusc · Accepted solution]

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

[Drrickryp · Accepted solution]

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.

[Sababneh]

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

[dinusc]

 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]

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

[dinusc]

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?

[Drrickryp]

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]

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]

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