cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Art_Ratta
Resolver I
Resolver I

Count Rows > 2000 Records

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

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.

 

 

View solution in original post

7 REPLIES 7
WarrenBelz
Super User III
Super User III

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.

 

 

View solution in original post

v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@Art_Ratta , I will wait for your response.

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

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

 

 

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.

@WarrenBelz 

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

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,499)