Advanced | Flow of The Week: Calculate running totals and tracking maximum values in Flow

A common question we get from Flow creators is how to calculate a running total or sum across a collection. Achieving this task in Flow might be straightforward to a programmer familar with imperative programming paradigms, but it might not be as obvious to a non-developer. In this post, I'll show how to:

– Iterate over a collection of SharePoint items and track a running total of one of the list's fields using the Initialize Variable and Increment Variable actions. 

– Track the SharePoint list item that has the maximum value for one of the list's fields using the Initialize Variable and Set Variable actions.

– Store objects inside a variable and reference their properties in expressions.

Here is the list that I have set up on SharePoint. My goals will be to calculate a sum of the Capacity field across all items, and track which city has the highest Capacity. 

To start, I will set up a When an item is created or modified trigger so that my Flow runs when I edit or add a new item. I will also add a Get items action because my flow needs access to the full set of properties on each item.

 

The basic algorithm for tracking the sum and maximum will be:

1. Initialize an integer variable to 0.
2. Initialize an object variable with some simple JSON to track the list item that has the highest Capacity
3. Loop over each item
   • Increment the variable tracking the sum of capacities.
   • Check the current item's capacity value against the current highest-capacity item. If the current item's value is greater than the stored item, replace the stored item

Initializing the integer is easy, I only need to add an Initialize Variable action, select "Integer" from the type menu, and set the initial value to 0.

Initializing the object is only slightly more complex. I added another Initialize Variable action. Object variables in Flow can be represented using JSON (JavaScript Object Notation), a common format for representing objects and their properties. The objects fetched from SharePoint Get items are conveniently also represented as JSON, so we need to initialize our variable to something that "looks" similar. I will assign a default Title property and an initial Capacity of -1. (This simplifies the Apply to each action, as we will see shortly.) I could use two variables to track the maximum capacity and the title/ID of the item with the maximum value, but where's the fun in that?  I'd also have to potentially re-query the item from SharePoint if I only stored the ID.

Now for the iteration — I can select the value output of the Get items to process each list item in a loop. As I mentioned above, I first want to increment the SumOfAllCapacity integer variable. I can do that with an expression that references the current item's Capacity property. (As the field type from SharePoint is actually decimal number, I need to convert it to a whole number using int()). 

Okay, the summation is handled, but now for tracking the maximum. I can add a Condition inside the Apply to each that will compare the current item's capacity against the Capacity property of our object variable. If it is greater than the current stored value (the If yes fork of the Condition), I will use Set Variable to overwrite the stored object with the current Sharepoint list item. If no, I will leave the variable alone, as the current stored object has a Capacity greater than the current item. Remember how I initialized the Capacity property to -1 above? This allows the check against the stored value to succeed the first time through the loop (assuming our Capacity list field on SharePoint restricts to values >= 0). If I didn't initialize to a value, the Flow would have to be more complex to handle the first item comparison. 

That's it! I've added a simple Send an email action to send me the results. I've embedded the SumOfAllCapacity variable to show the total sum of all list items, and the item link, Title, and Capacity fields from the item stored in our object variable at the end of the iteration. A more real-life example might use the results to send a nicer report, Start an Approval to authorize rebalancing capacity elsewhere, etc… The Send an email action goes outside of the loop, since we only want to execute it once the sum and maximum have been calculated. I've added some incredibly simple HTML tags to allow me to link to the SharePoint item directly. Here you can see how I reference the Capacity, Title, and {Link} properties of our MaxCapacity complex object with the following syntax in the Expression window: variables('MaxCapacity')['Capacity'].

Let's take a look at the Flow Run history for this flow when it ran against my list. There were 3 items at the time of the change that triggered the flow: Seattle with 15, Redmond with 5, and Bellevue with 28. As expected, the Apply to each ran 3 times, and we can see the SumOfAllCapacity variable incrementing appropriately. Here's iteration 3 where we add the 3rd item (Bellevue, 28) to achieve the correct answer of 48.

The run history also shows us that on iteration 1 and 3 we take the If yes branch of the condition, but on the second we didn't. That is because on the first pass we overwrite the -1 initial value with the Seattle record (15 > -1), on the second pass Redmond's capacity of 5 is less than 15, and on the third and final item, Bellevue's 28 causes us to replace Seattle.

As expected, the email arrives with the expected results:

That's it! As always, please let us know if you have any questions, suggestions, or feedback about Flow. Feel free to post below in the comments, on the Flow Community page, or on Twitter.