Unanswered

Replace CountIf in Power Apps for counting desired rows in a large database

(0) Share

Report

Posted on by RudolfBurnat

Hello!

Im working on a project where I want to get the quantity of the same named items from an excel (or lists) database by using a Barcode Reader component.
The excel database as I said contains a lot of items, and there multiple items with identical 'Circuit Number' and I want to get the quantity (sum of matching rows which i scanned) of these items (i used label in my project). BUT when i try to use CountIf function i get Delegation Warning, this is my exact code:

CountIf(Table1, 'Circuit Number'=First(BarcodeReader1.Barcodes).Value)

So I assume countif is not what im looking for in this problem. I tried to make a pivot from the database it creates the pivot table very nicely shows the Quantity of the items, but i cant import pivot table in power apps to get the data I need in my app.

Any suggestion, how do I count these Barcode Scanned codes which matches the items in database?
Thanks in advance! (Sorry for bad english)

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Ash13 576 on at

Like (1)

Report
Copy link

Link copied!

The Filter function is delegable, so you can filter your data first, then count the rows.

Something like:

CountRows(Filter(Table1, 'Circuit Number'=First(BarcodeReader1.Barcodes).Value))

Bear in mind CountRows is not delegable, so if the amount of rows after filtering is greater than 500 (or 2000 if you change the delegation amount to the max), then it will show 500 for the count.

Edit: I don't believe delegation is supported for excel, but is for SharePoint. You can find the documentation for delegation here.

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (1)

Report
Copy link

Link copied!
One way to do this, and keep things delegatable is to build a collection of your duplicates. You will need to do this on a behavior-type property like OnVisible of the Screen or OnSelect of a button, etc.).

If( CountRows( ClearCollect( colCheckDuplicates, Filter(Table1, 'Circuit Number'=First(BarcodeReader1.Barcodes).Value) ) ) > 0, UpdateContext({varLocalDupCheck: true}), UpdateContext({varLocalDupCheck: false}) )

Was this reply helpful? Yes No
RudolfBurnat 6 on at

Like (0)

Report
Copy link

Link copied!

And where does it stores the counted rows? I mean how do i put the calculated value in the label? if I write it in the OnVisible's event wouldnt it calculate the value before i scan the barcode?

Thanks for your reply

Was this reply helpful? Yes No
Anchov 1,986 on at

Like (0)

Report
Copy link

Link copied!

You could store in a variable, and I would guess you would re-run the count rows formula any time you have scanned the item, get the item ID, but before you save back to your datasource (Patch, SubmitForm, etc.). Do you have a Scan Button or Save on your screen? Without knowing your app, it might make sense to put it as as part of you OnSelect property on one of these buttons.

Was this reply helpful? Yes No