In this two part series, I'll be showing you how to take advantage of SharePoint calculated columns in your SharePoint Designer workflows. Part 1 will introduce calculated columns. Part 2 will show you how to consolidate your columns into a "function library" of sorts.

Part 1 of 2: When Basic Arithmetic Won't Cut It

SharePoint Designer workflows are very powerful, but there are a number of acknowledged limitations. One of these is the fairly limited selection of operations for performing calculations. For numbers, you have a basic selection of arithmetic (add, subtract, multiply, divide, and mod) operations, but no advanced math. For strings, you have the dynamic string builder, but you have no way to break down, or analyze, a string.

One way around this would be to design custom actions to perform the desired manipulation. But that requires Visual Studio, coding skills, and installation on the server by a willing administrator. Fortunately there is another option, already built into SharePoint, that gives you access to a wide array of functions.

Meet the Calculated Column

This option is the "Calculated Column". While this isn't a direct part of a SharePoint Designer workflow, your workflows do have access to the calculated columns in he lists and libraries of your site. If you haven't used a calculated column, this is a feature in SharePoint that allows you to create a column (or field) based on information contained in other fields in your list.

Creating a calculated column is pretty easy. Just select "Create Column", either from the Settings dropdown of a list view, or from the Columns section of your list's settings page. Near the bottom of your Type choices will be "Calculated".

When you select Calculated, the Additional Column Settings section is changed to show the Formula builder. In the example shown below, I have two date fields, Start and End. I want to know how many days are between them. All I need to do is enter the formula [End]-[Start], and I now have the duration of my event.

Note: After you save a formula, it is reformatted slightly, so if you go back into the field to review it later, you will see "=End-Start". You can also enter the formula directly in this format if you like.

But in addition to being able to display this in my list, I can now use that duration in my workflow to make decisions. For example, if the list is a time-off notification form, I might want to alert HR to arrange temp coverage if the employee is going to be out for more than 3 days:

Of course, that was a very simple calculation, and one that could have been done within the workflow. Where the calculated column starts coming into its own is more complex operations.

Let's say our time-off notification list is configured to allow people to email their requests into it. That's simple enough - SharePoint supports email enabling in several list types.

But, what if you want to process the request differently based on information in the subject line, such as whether the person is on vacation or sick. Your convention might be to put extra information in the subject (e.g. "sick - going to the doctor"). The relevant information to the workflow is to the left of the dash, so you need to grab just that. You could create another calculated column, called "Reason". The formula here is a bit more complex:

TRIM(LEFT([Title],FIND("-",[Title])-1))

You can find a complete list of the functions available for calculated columns at either of these links (the list is the same for WSS and MOSS):

http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx 

http://office.microsoft.com/en-us/sharepointserver/CH101760291033.aspx

There are a couple of limits to be aware of when creating calculated columns:

• You can only nest functions 8-deep
• Your total formula length can't exceed around 1000 characters.

A Good Start

In this post, I've shown you how SharePoint calculated columns can help you get around the limited calculation ability built into SharePoint Designer. You can use calculated columns to perform almost unlimited manipulation of your SharePoint data, and make use of that in your workflow. But what if you need many calculations in your workflow, or you don't want your users to see any "interim" values? In Part 2, I'll show you how to build a "function library" that you can call from any SharePoint Designer workflow in your site.

Until then, Happy Computing!