Click here to Skip to main content
15,867,594 members
Articles / Productivity Apps and Services / Sharepoint
Technical Blog

Grouping and Calculate Totals from One SharePoint List to Another

Rate me:
Please Sign up or sign in to vote.
4.75/5 (4 votes)
25 Nov 2013CPOL2 min read 45.2K   5   5
How to group and calculate totals from one SharePoint List to another

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 contains 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 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 them all together should look like this:

07 Workflow

Now you can try it and see for yourself.

08 List Summarized


Filed under: CodeProject, Configuration, Programming, Servers, Tips
Tagged: Sharepoint 2010, Sharepoint 2013, Sharepoint Foundation 2010, Sharepoint Foundation 2013, Workflows

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
QuestionThank You for Your time! Pin
Rockie(aka Collapse Troll)21-Jun-16 11:01
Rockie(aka Collapse Troll)21-Jun-16 11:01 
QuestionDelete Pin
AlaguEaga2-Jul-14 1:34
AlaguEaga2-Jul-14 1:34 
AnswerRe: Delete Pin
Member 1153965819-Mar-15 10:06
Member 1153965819-Mar-15 10:06 
GeneralThanks Pin
AlaguEaga2-Jul-14 1:22
AlaguEaga2-Jul-14 1:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.