cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Countrows in  power apps (cavs) always return 500

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Countrows in  power apps (cavs) always return 500

Hi @Frank_Fei ,

Glad to see you again!😁

The reason why you met this problem is because of delegation limit. Just as @mdevaney  and @WarrenBelz  said.

Even if you set the delegation limit to 2000, you still could only deal with at most 2000 records.

Do you want to count the number of your records?

There are two ways to avoid the affect of delegation:
1)only use delegate functions

However, countrows function is not delegate and there's no delegate function with similar feather.

delegate functions for sharepoint:

POWER APPS DELEGABLE FUNCTIONS AND OPERATIONS 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

 

2)use collection

In your issue, I think this solution is more suitable for you.

In collection, there's no delegation limit and one collection could hold at most 10K records.

Firstly, you need to save your data to collection.

For example:

set the app's OnStart:

ClearCollect(collection1,listname)
//please replace with your sharepoint list name

Secondly, use this collection to deal with data in your app.

For example:

CountRows(collection1)

 

 

 

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: Countrows in  power apps (cavs) always return 500

Hi @Frank_Fei ,

Quite simply you cannot, CountRows is not a Delegable function.

 

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.

Highlighted
Super User
Super User

Re: Countrows in  power apps (cavs) always return 500

@Frank_Fei 
The COUNTROWS function has a 2,000 record limit.  This is known as the 'delegation limit.'  You can change the number of rows to 2,000 in the advanced settings but increasing it any further will not have any result.

 

The same limit occurs for SUM.  I wrote an article showing how to bypass this.  You could easily adapt it to COUNTROWS as well.


Link To Article:

https://matthewdevaney.com/sum-rows-in-a-sharepoint-list-with-no-delegation-warnings/

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Community Support
Community Support

Re: Countrows in  power apps (cavs) always return 500

Hi @Frank_Fei ,

Glad to see you again!😁

The reason why you met this problem is because of delegation limit. Just as @mdevaney  and @WarrenBelz  said.

Even if you set the delegation limit to 2000, you still could only deal with at most 2000 records.

Do you want to count the number of your records?

There are two ways to avoid the affect of delegation:
1)only use delegate functions

However, countrows function is not delegate and there's no delegate function with similar feather.

delegate functions for sharepoint:

POWER APPS DELEGABLE FUNCTIONS AND OPERATIONS 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

 

2)use collection

In your issue, I think this solution is more suitable for you.

In collection, there's no delegation limit and one collection could hold at most 10K records.

Firstly, you need to save your data to collection.

For example:

set the app's OnStart:

ClearCollect(collection1,listname)
//please replace with your sharepoint list name

Secondly, use this collection to deal with data in your app.

For example:

CountRows(collection1)

 

 

 

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.

View solution in original post

Highlighted
Super User
Super User

Re: Countrows in  power apps (cavs) always return 500

Hi @v-yutliu-msft 

Unless I'm missing something, Collect and ClearCollect are also not delegatable so your second solution also will be limited to the first 2k items.  You would need to do 5 successive collections of the next 2k item blocks to get all of @Frank_Fei 's items into a single collection.  IMHO, @mdevaney,'s solution is quite creative and is the newest approach to what has been a problem with large SharePoint datasets for as long as I have been working with PowerApps.  It is my personal observation that large datasets are better handled with SQL and CDS than with SharePoint.  

Highlighted
Super User
Super User

Re: Countrows in  power apps (cavs) always return 500

@v-yutliu-msft 
Your ideas about collections being a path to avoiding delegation issues are incorrect....

Collections can hold more than 2,000 records but they will not download 10,000 records from a datasource.  I agree with @Drrickryp here.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,423)