cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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 @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,

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
Highlighted
Super User
Super User

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)
Highlighted
Super User
Super User

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

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

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


@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 @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,

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,676)