Count Rows from SQL table with more than 2000 rows

[SearchingYT]

Hi Power apps experts 🙂

My sql table has 3600 rows, and I am not able to return this in my power app, due to no delegation with CountRows function. 

I have tried using collection as I have read that a collection can store up to 10,000 rows, but it didnt work for me.

On my screen OnVisible property, I do ClearCollect(var1, tableName) and then on a label in my screen I say CountRows(var1).
This still returns 2000.

What can I do? This should be possible..
Any help would be much appreciated. I have been stuck with this for days..

[CNT]

@SearchingYT PowerApps will fetch a maximum of 2000 records only. If you want all the 3600 records in a Collection you will have to collect them batches and combine them into one collection.

ClearCollect(var1, FIlter(tableName, ID>0 && ID<2001);

ClearCollect(var2, FIlter(tableName, ID>2000 && ID<4001);

ClearCollect(var3, var1, var2) 

[timl · Accepted solution]

Hi @SearchingYT 

With SQL Server, we can solve pretty much any delegation problem through the use of views and that's what I would recommend in this example.

The trick is to create a view on your base SQL table and to add a column that contains the numeric value 1. You then sum this column to produce the count. This technique is delegable because Sum is a delegable function. Furthermore, you can combine the Sum and Filter functions to produce counts on records that match specific search criteria in a delegable way.

I have a post here with more details:

http://powerappsguide.com/blog/post/sql-how-to-count-rows-accurately

The practice of using collections to count records is not efficient, and I would not recommend it.

[SearchingYT]

Totally agreed, best solution for me. Works great. Thanks.

[SearchingYT]

Yes, I have read about this as well, but it is not a good work around for us as we will have to update this code whenever the records increase by more than 2000..

[SearchingYT]

Hi @timl One issue I am experiencing using this technique. My formula looks like this:
Sum( Filter( sqlView, DateColumn = DateValue("1900-01-01")), NumericValueColumn)
It is returning nothing.. I cant see why?

My other tests work exactly how I expect, e.g.
Sum(sqlView, NumericValueColumn) - it works
Sum( Filter( sqlView, TypeColumn = "Type1"), NumericValueColumn) - it also works

I have used DateColumn filtering in other galleries which also works. I believe that the issue is in the sum function and using filtering?
What do you think?

[timl]

@SearchingYT 

This technique works OK for me with date columns. The thing I would check is the data type of the source column. If it contains a time component, filtering by a date will only match those records with a time of 00:00. In this case, you would have more success with this:

Sum( 
   Filter( sqlView, DateColumn >= DateValue("1900-01-01")
                    And DateColumn < DateAdd(DateValue("1900-01-01"), 1, Days)
), NumericValueColumn)

[gidiscacciati]

Hi there!

I'm using your technique, but when the sum is equal to 0, it is not the # 0 in the screen. Is that the expected outcome?