Count Rows > 2000 Records

[Art_Ratta]

Dears

I have a data in share point list around 10000 record on the list , now limitation can be count 2000 record. 

Can i use count rows more than 2000 record ? 

Formula : CountRows('DataSource')

please.

[WarrenBelz · Accepted solution]

Hi @Art_Ratta ,

Not using a standard data set as it is not a Delegable function.

This post of mine explains how to collect more than 2,000 records and then your CountRows would work on the collection.

Happy to elaborate further if required.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-yutliu-msft]

Hi @Art_Ratta ,

Just as @WarrenBelz  said, the reason why you met this problem is because of delegation.

Powerapps could only deal with at most 2000 records by default.

If your data source has more than 2000 records, you need to use delegate functions. Or else you will get wrong result.

Please refer the delegate functions for sharepoint :

Item	Number	Text	Boolean	DateTime	Complex [1]
Filter	Yes	Yes	Yes	No [4]	Yes
Sort	Yes	Yes	Yes	Yes	No
SortByColumns	Yes	Yes	Yes	Yes	No
Lookup	Yes	Yes	Yes	No	Yes
=	Yes	Yes	Yes	No [4]	Yes
<, <=,<>, >, >=	Yes [2]	No	No	No	Yes
StartsWith	-	Yes	-	-	Yes
IsBlank	-	No [3]	-	-	No

The SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

CountRows() function is not delegate. That's the reason why you got wrong result.

How to deal with this? To use collection. In collection, there's no delegate limit.

However, to save data to collection, you need to use ClearCollect function, which is not delegate neither.

So you could only save at most 2000 records in one collection and use Filter function to split data.

Please notice that: ID field in sharepoint list is not delegate for "<,>"in filter function.

So you need to : use another number field to filter to save data to multiple collections.

You could set like this:

1)set the app's OnStart:

ClearCollect(collection1,Filter('DataSource',numberfield<=....));
//make sure the filtered items less then 2000
ClearCollect(collection2,Filter('DataSource',numberfield>....,numberfield<=....));
ClearCollect(collection3,Filter('DataSource',numberfield>....,numberfield<=....));
....;
//save data to multiple collections
ClearCollect(result,
collection1,
collection2,
collection3,....)
//save all the filtered data to one collection

Then count this collection's record number:

CountRows(result)

Best regards,

[WarrenBelz]

Thanks @v-yutliu-msft for confirming my logic.

@Art_Ratta , I will wait for your response.

[Art_Ratta]

Sorry for late reply , because I still can't do

@WarrenBelz 

for your suggestion , 

the result of my testing , still count 2000 record i not sure , did wrong for my self or not. i will test again.

@v-yutliu-msft 

for your formula , and i testing with my SP list the result as below 

CountRows(Result) = 2000

CountRows(Collection1) = 0

CountRows(Collection2) = 0

CountRows(Collection3) = 0

[WarrenBelz]

@Art_Ratta ,

If you look at my post, you will see you need to add another numeric field in the data, ideally the same value as the ID and populate every record (including new ones) with it. It does work once you have done this,

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @Art_Ratta ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Art_Ratta]

@WarrenBelz 

it's seem look like working , thank you very much.