Grouping and Calculate Totals from one SharePoint List to another

By | November 25, 2013

You might be wondering how to summarize list items by grouping and summing them up together to another SharePoint List? Well it’s quite simple and you can achieve them just by using workflows created in SharePoint Designer.

While workflows does not support aggregation of data we can create some smart calculation similar to running totals. To achieve that we just need to store past values of what you want to calculate so we can compare them with the new ones and in return calculate the difference and apply it with the running total. Sounds pretty straightforward? So let’s do it in real scenario.


Let’s assume you have a list called Invoice Detail which contain line item details of an invoice like Description, Amount and Invoice Number. We also need to store the Old Amount so you need a column for that as well.

01 Invoice Detail

Then you have a summary list called Invoice Summary where you group everything by Invoice Number and sum the Amounts then store it in Total Amount

02 Invoice Summary

So let’s create a workflow that will trigger on update and create of a list item in your Invoice Detail.  First step is we need to create a new Summary Item but we need to verify if that exists first by searching for the created or updated item’s Invoice Number in your Invoice Summary List.

03a Condition


03 Invoice Number Condition

If it returns a value it means that the Invoice Number already exists on the Invoice Summary List hence we just need to update them with the new total.  First is we need to calculate the difference of the Old and New Amount values then store it in a local variable called TotalChangedAmount, this step means that we will only apply the changed amount to the running total.

03b Old vs New

Then we need to add that calculated amount to the Total Amount stored in the Invoice Summary List and save it.

03c Save Total

04 Calculate Total

05 Update Total

And if Invoice Number does not exist then it means we need to create them.

04a Create new summary

06 Create Summary

The final step is then store your Old Amount so you can use them for calculation later.

06a Store Old Values

So bringing then all together should look like this.

07 Workflow

Now you can try it and see for yourself.

08 List Summarized


9 thoughts on “Grouping and Calculate Totals from one SharePoint List to another

  1. Dino Lopez

    In step 1b. how can you Calculate from Invoice Summary :: Total Amount… my workflow got me in a loop where I’m not able to select the item because does not have a value….

    Reply
      1. sindura

        Thank you,I read one article workflow looping will work in in sp2013.so I got doubt on this.

        Reply
  2. Ike Rebout

    How does it readjust if the number on one of the invoices adjust though? For instance if an invoice goes from $5000 to $2200 after someone returned items, it’s not giving you a true assessment of the cost is it?

    Reply

Leave a Reply