cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

500 Row limit using Sql table as datasource

Hi, 

I have seen lot of post for 500 row limit in apps but not able to get the solution, 

 

I am using CoutRows function on Sql table and i have getting 500 records count only whereas actual count should be 600.

Below is the CountRows expression used in app :

 

CountRows(Filter('TableName';Status="Closed"))

 

Can you please hel me on this,

 

Thanks.

Pankaj

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Meneghino Community Champion
Community Champion

Re: 500 Row limit using Sql table as datasource

Hi @Anonymous

@Anonymous's solution will work but will kill performance.

Best to create a view in SQL grouping by Status, something like:

SELECT Status, Count(*) AS RowCount
FROM TableName
GROUP BY Status

And then use the view in PowerApps to get the count, like this:

LookUp('ViewName', Status="Closed", RowCount)

View solution in original post

Community Support
Community Support

Re: 500 Row limit using Sql table as datasource

Hi @Anonymous,

 

I think you have faced Non-delegable issue within your app.

 

The SQL table data source is a delegable data source, but the CountRows functon is a Non-delegable function, so the formula that you provided could not be delegated within your app.

 

More details about the Delegation in PowerApps, please check the following article:

Delegation in PowerApps

 

By default, if your formula could not be delegated within your app, the data (On your side, it is SQL table data) would be processed locally. In default, there is a limit in Data rows that could be processed locally, you could only process 500 records locally within your app.

 

More details about the Non-delegable limits in PowerApps, please check the following article:

Non-delegable limits

 

As an alternative solution, you could consider take a try to change the Non-delegable limits within your app, the maximum value of the Non-delegable limits you could change is 2000. Please check the following GIF image for changing the Non-delegable limits:Test1.gif

 

 

Please take a try with above solution, then try your formula again to check if the issue is solved.

 

More details about changing the Non-delegable limits, please check the following article:

Change the Non-delegable limits

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
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

7 REPLIES 7
Highlighted
Anonymous
Not applicable

Re: 500 Row limit using Sql table as datasource

You can use MSflow and then collect the data into a collection.  I use this trick and it works. 

Meneghino Community Champion
Community Champion

Re: 500 Row limit using Sql table as datasource

Hi @Anonymous

@Anonymous's solution will work but will kill performance.

Best to create a view in SQL grouping by Status, something like:

SELECT Status, Count(*) AS RowCount
FROM TableName
GROUP BY Status

And then use the view in PowerApps to get the count, like this:

LookUp('ViewName', Status="Closed", RowCount)

View solution in original post

Anonymous
Not applicable

Re: 500 Row limit using Sql table as datasource

Thanks @Meneghino ; i have created view as per you mentioned and it works

 

Thanks ,

Pankaj

 

 

Community Support
Community Support

Re: 500 Row limit using Sql table as datasource

Hi @Anonymous,

 

I think you have faced Non-delegable issue within your app.

 

The SQL table data source is a delegable data source, but the CountRows functon is a Non-delegable function, so the formula that you provided could not be delegated within your app.

 

More details about the Delegation in PowerApps, please check the following article:

Delegation in PowerApps

 

By default, if your formula could not be delegated within your app, the data (On your side, it is SQL table data) would be processed locally. In default, there is a limit in Data rows that could be processed locally, you could only process 500 records locally within your app.

 

More details about the Non-delegable limits in PowerApps, please check the following article:

Non-delegable limits

 

As an alternative solution, you could consider take a try to change the Non-delegable limits within your app, the maximum value of the Non-delegable limits you could change is 2000. Please check the following GIF image for changing the Non-delegable limits:Test1.gif

 

 

Please take a try with above solution, then try your formula again to check if the issue is solved.

 

More details about changing the Non-delegable limits, please check the following article:

Change the Non-delegable limits

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
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

Meneghino Community Champion
Community Champion

Re: 500 Row limit using Sql table as datasource

@v-xida-msft's suggestion also could work but would also kill performance.

Much better to use the great possibilities of SQL Server

Anonymous
Not applicable

Re: 500 Row limit using Sql table as datasource

Thanks @v-xida-msft, for giving the detailed information about this issue, got to learn new things :), after updating the limit to 2000, it worked with my origional formula. 

 

Thanks,

Pankaj

Anonymous
Not applicable

Re: 500 Row limit using Sql table as datasource

Hi v-xida-msft,

Soution which you provided it worked well for up to 2000 records, 

Now, my table is having more than 2000 records and the app is displaying 2000 rows only.

 

What is the solution for to display rows more than 2000

 

Regards,

Pankaj

Helpful resources

Announcements
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,247)