Custom SharePoint Workflow Action “CalculateSum”

SharePoint offers the possibilty to create different list views, calculate and display totals grouped by one or more columns. Unfortunatly building totals (column sums) and use them in other lists or in the chart webpart seems to be a very difficult task with SharePoint 2010. With this article I’ll present a custom workflow action for building column sums. The workflow action enables Sharepoint to calculate totals, store them in a different list and e.g. bind that list to the chart webpart. If you are experienced in using workflow actions you’ll notice that “CalculateSum” is almost self explanatory.

Feel free to use and adopt my source code. If you need guidence in developing custom sandboxed workflow actions visit the following webpages: http://msdn.microsoft.com/en-us/library/gg615449.aspx and http://msdn.microsoft.com/en-us/office365trainingcourse_3v_3.aspx. If you’ve read these articles it will be very easy for you to apply the following source code:

Elements.xml:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <WorkflowActions>
    <Action Name="CalculateSum"
            SandboxedFunction="true"
            Assembly="$SharePoint.Project.AssemblyFullName$"
            ClassName="CalculateWorkflowActions.CalculateActions"
            FunctionName="CalculateSum"
            AppliesTo="all"
            UsesCurrentItem="true"
            Category="Calculate WorkFlow Actions">
      <RuleDesigner Sentence="calculate sum of column %1; filter by column %2 with value %3 and filter by column %4 with value %5; result in %6 (execptions logged to %7)">
        <FieldBind Field="totalColumn" Text="column to sum" Id="1" DesignerType="FieldNames"/>
        <FieldBind Field="filterColumn" Text="filter by this column" Id="2" DesignerType="FieldNames"/>
        <FieldBind Field="filterValue" Text="filter value" Id="3" DesignerType="TextBox"/>
        <FieldBind Field="filterColumn2" Text="filter by this column" Id="4" DesignerType="FieldNames"/>
        <FieldBind Field="filterValue2" Text="filter value" Id="5" DesignerType="TextBox"/>
        <FieldBind Field="result" Text="result" Id="6" DesignerType="ParameterNames"/>
        <FieldBind Field="exception" Text="exception" Id="7" DesignerType="ParameterNames"/>
      </RuleDesigner>
      <Parameters>
        <Parameter Name="__Context" Type="Microsoft.SharePoint.WorkflowActions.WorkflowContext, Microsoft.SharePoint.WorkflowActions"
                   Direction="In"
                   DesignerType="Hide"/>
        <Parameter Name="totalColumn"
                   Type="System.String, mscorlib"
                   Direction="In"
                   DesignerType="FieldNames"
                   InitialValue=""
                   Description="column to build total"/>
        <Parameter Name="filterColumn"
                   Type="System.String, mscorlib"
                   Direction="Optional"
                   DesignerType="FieldNames"
                   InitialValue=""
                   Description="filter by this column element"/>
        <Parameter Name="filterValue"
                   Type="System.String, mscorlib"
                   Direction="Optional"
                   DesignerType="TextBox"
                   InitialValue=""
                   Description="filter value"/>
        <Parameter Name="filterColumn2"
                   Type="System.String, mscorlib"
                   Direction="Optional"
                   DesignerType="FieldNames"
                   InitialValue=""
                   Description="filter by this column"/>
        <Parameter Name="filterValue2"
                   Type="System.String, mscorlib"
                   Direction="Optional"
                   DesignerType="TextBox"
                   InitialValue=""
                   Description="filter value"/>
        <Parameter Name="result"
                   Type="System.String, mscorlib"
                   Direction="Out"
                   InitialValue=""
                   DesignerType="ParameterNames"
                   Description="result"/>
        <Parameter Name="exception"
                   Type="System.String, mscorlib"
                   Direction="Out"
                   InitialValue=""
                   DesignerType="ParameterNames"
                   Description="Exception encountered"/>
      </Parameters>
    </Action>
  </WorkflowActions>
</Elements>

CalculateActions.cs:

using System;
using System.Collections;
using Microsoft.SharePoint;
using Microsoft.SharePoint.UserCode;

namespace CalculateWorkflowActions
{
  public class CalculateActions
  {
    public Hashtable CalculateSum(SPUserCodeWorkflowContext context, string totalColumn, string filterColumn, string filterValue, string filterColumn2, string filterValue2)
    {
      Hashtable results = new Hashtable();

      decimal sum = 0;
      bool addValue = true;

      try
      {
        using (SPSite site = new SPSite(context.CurrentWebUrl))
        {
          using (SPWeb web = site.OpenWeb())
          {
            SPListItemCollection items = web.Lists[context.ListId].GetItems();

            foreach (SPListItem item in items)
            {
              addValue = true;

              if (IsNumeric(item.GetFormattedValue(totalColumn)))
              {
                if ((string.IsNullOrEmpty(filterColumn)) ||
                   ((!string.IsNullOrEmpty(filterColumn) && (item[filterColumn].ToString() == filterValue))))
                {
                }
                else
                {
                  addValue = false;
                }

                if ((string.IsNullOrEmpty(filterColumn2)) ||
                   ((!string.IsNullOrEmpty(filterColumn2) && (item[filterColumn2].ToString() == filterValue2))))
                {
                }
                else
                {
                  addValue = false;
                }

                if (addValue)
                {
                  sum += Convert.ToDecimal(item.GetFormattedValue(totalColumn));
                }
              }
            }
          }
        }

        results["result"] = sum;
        results["exception"] = "Success";
      }
      catch (Exception e)
      {
        results["result"] = 0;
        results["exception"] = e.ToString();
      }

      return results;
    }

    private static bool IsNumeric(object Expression)
    {
      bool isNum;
      double retNum;

      isNum = Double.TryParse(Convert.ToString(Expression), System.Globalization.NumberStyles.Any, System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);

      return isNum;
    }
  }
}

If you have succeeded in building and uploading the .wsp-file you can create and configure the custom workflow with SharePoint designer.

Add “CalculateSum” workflow action to your workflow:

CalculateSum1

Then enter parameters in workflow action (all filter parameters are optionally):

CalculateSum2

That’s it! In case you need the complete visual studio 2010 source code and/or the compiled solution (.wsp-file) please let me know.

 

Downloads:

WSP: https://skydrive.live.com/redir?resid=A72444E9DFA299A1!804

SourceCode: https://skydrive.live.com/redir?resid=A72444E9DFA299A1!805

 

Addendum:

In case you are unable to activate the solution check if Microsoft SharePoint Foundation Sandboxed Code Service is running!

19 thoughts on “Custom SharePoint Workflow Action “CalculateSum”

  1. This is great. I’ve been trying to figure this out for days, and this is a huge help. Thank you. I’d really appreciate it if you could forward me the wsp file. I could probably figure out the build, but it’s a little outside my area.
    Thanks so much.

  2. Could you help with this please, to create a workflow. I am trying to add a column referencing the same job id and copy the subtotals to a different column. Which i can use in a different list as a lookup column. I am trying to find the solution from days. It would a real treat if you could help me.

    • First of all download and install the solution (wsp). Then check if Microsoft SharePoint Foundation Sandboxed Code Service is running! Mark your list. Go to “settings->worflow settings” in the ribbon. Click “create workflow in SharePoint designer”. Creating a workflow is almost self explaining. In case the wsp is correctly installed and activated you will find the following entry in the action
      list: “Calculate WorkFlowActions->BuildTotal”. Hope this helps!

  3. Dr. Dieter Knabben I have followed the steps, When i choose the column in the calculate sum of column and filter by a column, and saved it. I dint see any changes to the list. Like i wanted, would it be possible to copy the sub-totals to a different column. Could you please mail me, joseph_rock72@yahoo.com i could send you some snapshots. Thank you very much.

  4. Hello this is exactly what I was looking for thank you. Now looking for a least path of resistance solution I have downloaded th wsp and uploaded and activate. Ok so far so good Calc Dum appears in workflow as action choice. I setup a two line workflow using the calc sum action on a number field with two filters and result in variable. Then I et a second action to set field in current list as the resulted variable.
    Work flow completes successful but value in field is always ‘0’ so it doesn’t semm to be calculating a sum. Any thoughts? Thank you,
    Best,
    Greg

  5. Hello Dr. Knabben,
    OK, so the sun calc is working correctly, the issue is in trying to filter by a group /person column. So I am performing a sum on number column filtering by two columns the first is a mult-choice column with static value the second is a person group column where I am doing a lookup based on current item (i.e. current item = created by returned as string).
    It works great just filtering by the multi-choice column static value but not if I use the person/group column.
    Any/all suggestions are appreciated.

    Thank you,
    Greg

  6. Dr. Knabben,
    Thanks for this, it has worked the way I needed it to. I was wondering though, how difficult would it be to change this to calculate average of a column and use it in a workflow? That is the only other thing I need.
    Thanks again,
    Jason

  7. Calculating the average of a column should not be a big deal! Please download source code and try to add this functionality.

  8. Forex EA (robot) software is a good choice for them. But when you download a trading and investing software, you’ll want to consider the next features:
    proven track record, stock alert frequency, way of sending you stock alerts,
    and profit potential. The ticker program could be used without registering, but users must register to have access to the communications features with the program.

  9. //–Please enter some pattern
    calculate the sum of column????; filter by column???? with value???? and filter by column?? value wit????; result in VARIABLE; RESULT ( exceptions logged to variable: exception)
    //–I have all the time error

  10. Thanks for the reply….my parameters are:
    //-
    calculate sum of column AMONT PAID(Euro);filter by column COURSE CODE with value ME04413P and filter by column COURSE CODE with value ME0EE13P; result in VARIABLE;RESULT1 ( exceptions logged to VARIABLE:EXCEPTION1)
    //-
    Error description:
    AN ERROR HAS OCCURRED IN “FOLDER AMONT PAID”
    workflow ceases to work

  11. Hi
    I installed again by the SITE SETTINGS / SOLUTIONS now works (before I installed by PowerShell)
    //–
    Now I have a problem with the RESULT = 0 AND EXCEPTION = SUCCESS
    –//
    What am I doing wrong ?

  12. Hi
    The type of information in this column is:
    Currency ($, ¥, €) not working properly
    Number (1, 1.0, 100) works !!!
    Thank you

  13. FILTER BY COLUMN ( NOT WORKING )
    VARIABLE;EXCEPTION
    System.NullReferenceException: Object reference not set to an instance of an object.
    at CalculateWorkflowActions.CalculateActions.CalculateSum(SPUserCodeWorkflowContext context, String totalColumn, String filterColumn, String filterValue, String filterColumn2, String filterValue2)

  14. Thanks for posting this. Could you possibly point me in the right direction. I am a relative beginner. I have been able to upload this Sandbox solution to our site and it appears in Designer. Here’s my situation.
    I have several columns of parts that can be filtered by status (Ordered, Delivered, etc.)
    What I need to do is sum each column after applying the filter and then updating another list with the result. After doing that for each filter, I’ll use a calculated column to do a calculation using the different totals.

    My confusion is in two areas. One, is how to apply the filter. In your solution, the filter seems to come after the sum. Is there a way to apply a filter prior to using your solution? Two, I’m a bit confused as to how to get the sum into another list for calculations. I assume the sum is put in a variable but have not had any success using the variable to update a list. Ultimately, I would like the list sum to be updated as the numbers change in the main list.

    Any thoughts would be appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s