Answered

function countif

(0) Share

Report

Posted on by gioce

Hi, sorry my english ... I have a problem to solve ... I need to count the records of a table, but when I exceed 2000 records the result is wrong. I tried the following Sum function (ForAll (Filter ('[dbo]. [USR_RIG_INVENTARI]'; CODINV = Dropdown2.Selected.CODICE); 1); Value), but it gives me the value 2000, when instead the lines are 296628. I also tried with the Countif function, but it reports incorrect values. Could you help me understand?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@gioce

The SUM and COUNT functions cannot be delegated. Therefore you will only get the 1st 2,000 results when using them.

Link to MS Delegation Article

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

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

Was this reply helpful? Yes No
gioce 90 on at

Like (0)

Report
Copy link

Link copied!

is there an alternative to count records that exceed 2000? thank you

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@gioce
The answer is complicated... it depends.

There is no function in PowerApps to CountRows over 2,000. However, you could calculate that in a table in your database or keep a count of it and simply display that number. It’s too long to explain the method here but I am considering doing a blog article about it.

Another idea is simply to use the last ID number of the table assuming no records get deleted.

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Verified answer

v-siky-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@gioceYes, Counting function is not delegable for SQL. But as an alternative workaround, I often use CountRows() function directly on the gallery from SQL, because the SQL is delegable, so the gallery can get all records. Please check this.
1. Add a gallery, set Items property: [dbo]. [USR_RIG_INVENTARI]
2. Set Text of Label: CountRows(Gallery1.AllItems)
Note: there is no need to display the gallery, set its Visible property to false
Sik

Was this reply helpful? Yes No