500 Row limit using Sql table as datasource

[Anonymous]

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

[Anonymous]

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

[Meneghino · Accepted solution]

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)

[Anonymous]

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

Thanks ,

Pankaj

[v-xida-msft · Accepted solution]

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:

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

[Meneghino]

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

Much better to use the great possibilities of SQL Server

[Anonymous]

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]

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