Answered

How to count how many rows have a value in a list.

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

If I have 2 lists in SharePoint named Clinics and Shipments. Shipments has a lookup to Clinics and a status for the shipment. I want to make a gallery that lists all clinics that have shipments that are in the status of ordered or shipped, but NOT delivered. In each row of the gallery I want to show the Clinic name and a count of ordered shipments for that clinic and a count of shipped shipments for the clinic for that row, but only show distinct clinics with the counts?

I'm sure I can just use Distinct(Shipments,Clinics) for the items of the gallery, but how do I get the counts of the value of the clinic?

Thanks!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

If your Gallery is all done then counting Clinic should be.
CountRows(Gallery1.AllItems,Clinics)
WarrenBelz 156,774 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
hI @Anonymous ,
Try
CountRows(YourGalleryName.AllItems)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
For your main gallery, you want to only show the Clinics which have items that are in shipped or ordered, so your Items will be:

Distinct( Filter( Shipments, status="shipped" || status="ordered"), Clinics)

Inside each row, you'll have one label that lists the Clinic name, just pointing to the Title or whatever field you used for the name.

Then you'll have two more labels below, one for items ordered and one for shipped. Now you may find it difficult to add a distinct here if these are meant to be grouped into larger orders, unless you have a column for OrderNo or something that can allow the distinct to determine how many orders rather than individual items.

IF you have an OrderNo or grouping column and want to show totals for overall orders:

Label2:

"Ordered: " & CountRows( Distinct( Filter( Shipments, status="ordered" && Clinic=ThisItem.ClinicNameColumn ), OrderNo) )

Label3:

"Ordered: " & CountRows( Distinct( Filter( Shipments, status="shipped" && Clinic=ThisItem.ClinicNameColumn ), OrderNo) )

If you do not have a grouping column, then you can only show number of individual items shipped/ordered per clinic:

Label2:

"Ordered: " & CountRows( Filter( Shipments, status="ordered" && Clinic=ThisItem.ClinicNameColumn ) )

Label3:

"Shipped: " & CountRows( Filter( Shipments, status="shipped" && Clinic=ThisItem.ClinicNameColumn ) )

ThisItem may only have one item like .Result as we have made it Distinct, in that case use .Result instead of .ClinicNameColumn

Let me know if this helps,

Cheers,

Sancho
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to list all clinics that have shipments that are in the status of ordered or shipped, but NOT delivered within the Gallery?

Could you please share a bit more about the LookUp field in your Shipments List?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter( Distinct(Clinics, Title), Result in ForAll( Filter(Shipments, Status.Value = "Ordered" || Status.Value = "Shipped"), LookUpField.Value ) )

Note: I assume that the LookUp field in Shipments List references values from the Title column in Clinics List.

Within this Gallery, add three Labels, set the Text property of first Label to following:

ThisItem.Result

set the Text property of the second Label to following:

"Count of Ordered Shipments: " & CountRows(Filter(Shipments, Status.Value = "Ordered", LookUpField.Value = ThisItem.Result))

set the Text property of the third Label to following:

"Count of Shipped Shipments: " & CountRows(Filter(Shipments, Status.Value = "Shipped", LookUpField.Value = ThisItem.Result))

Note: I assume that the Status column is a Choice type column in your Shipments List, with available options -- Ordered, Shipped and Not Delivered.

Please consider take a try with above solution, check if the issue is solved.

Best regards,