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

Countrows in  power apps (cavs) always return 500

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

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

6 REPLIES 6
WarrenBelz
Super User
Super User

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.

mdevaney
Super User
Super User

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

v-yutliu-msft
Community Support
Community Support

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.

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.  

@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
Frequent Visitor

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.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,994)