Countrows in power apps (cavs) always return 500

[Anonymous]

[WarrenBelz]

Hi @Anonymous ,

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.

[mdevaney]

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

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

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,

[Drrickryp]

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

[mdevaney]

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

[ArifNajiruddin]

Hai Frankfei, to solve the problem you had, please follow step below.

1. Add A Gallery.

2. Put the filter as you want at the Gallery = Filter(Table, Complaint <> 0)

3. Set the Gallery visible to false.

4. Add a label.

6. Set the label Text = CountRows(Gallery.AllItems)

7. The label will display the row that you had filter from the gallery.