How to count distinct classes in a list and populate a second list with the value

(0) Share

Report

Posted on by Jltitus

138

I have two SharePoint lists:

Class Assignments - Lists learner name and classes they are assigned. (Many learners can be assigned to one class/classID. and each learner is a separate line.
Class Schedule - Lists each class only once with date/time of class. This is where the class/classID comes from that is in the Class Assignments list.

Goal: I'm trying to figure out a flow that counts all the instances of class A in the Class Assignments list and puts that number in the SeatCount field of the associated class in the Class Schedule so that I can keep track of how many people are assigned a class.

ASK: I can get the count of the number of items in the Class Assignments (104) list but I'm not sure how to get the count for each of the distinct classes (84). I'm sure I probably need to use variables and I'm sure I need to do some sort of "Get Items" for the Class Schedule list so I can put the count there.

Power Automate Flow.jpg

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Jltitus

I have knocked together a quick example with excel below, Microsoft lists are the same.

Get your items, use a select to get the column you wish to count distinct values of (use text mode in select), then with a compose use union (on the select twice), this will return distint values, then you can use length.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
Jltitus 138 on at

Like (0)

Report

@DamoBird365 This gets me closer but the issue I'm running into is this:
Say there are 5 people in the class assignments listed for the "Start Your Journey" class. I want the #5 to display in the class schedule list for that class.

The above gets me a full count of how many different classes there are, not how many people in each different class.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Jltitus

No problem. If you now add an apply to each, and insert the compose as input, i.e. the union of distinct classes you will loop through all class names. With this, insert a filter into the apply to each, using the value of list rows as input, create a condition where classname is equal to current item. This will create an array in each loop of the users attending each class. You can then run a length in the loop to count the number of students/rows for each class.

If you want to get these totals back out of the apply to each, you can use an outputs() expression on a compose within the apply to each. Best give the above a go and let me know what you want to now do with the count of students for each class?

Damien

Was this reply helpful? Yes No
Jltitus 138 on at

Like (0)

Report

@DamoBird365 I think I'm getting close but running into how to compose the length after the filter. Maybe I don't even have the filter correct?

Was this reply helpful? Yes No
Verified answer

Jltitus 138 on at

Like (1)

Report

@DamoBird365 using this great video from Shane Young I figured out how to do what I needed. Here's the flow.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Jltitus

That looks good to me. What are you experiencing?

If your filter is empty, check your value input in the history of list rows and maybe insert a compose after the filter array with current item in it - to check in history that the current item is as expected. The condition action is not great for reviewing history.

Damien

Was this reply helpful? Yes No