cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Darkmatter5
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

Hi @Darkmatter5 ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
eka24 Community Champion
Community Champion

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

If your Gallery is all done then counting Clinic should be.
CountRows(Gallery1.AllItems,Clinics)
WarrenBelz Community Champion
Community Champion

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

hI @Darkmatter5 ,

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

Super User
Super User

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

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Community Support
Community Support

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

Hi @Darkmatter5 ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (8,466)