cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Darkmatter5
Level: Powered On

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
Community Support Team
Community Support Team

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
Level 10

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
Level 8

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

Highlighted
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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
Community Support Team
Community Support Team

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (2,331)