Use Calculated Columns to Close Gaps in Workflows

SharePoint Designer is the must-use tool everyone loves to hate because it leaves us with certain difficult gaps to bridge for common business requirements. However, with some clever indirection, we can take advantage of other SharePoint features and close this gap.

Many of us have used SPD to create simple, and sometimes quite complex, workflow solutions. With its declarative, UI oriented workflow, we can pick from a variety of pre-build conditions and activities. We can even add our own conditions and activities. In fact, many people have done this and made them available for free to the community via www.codeplex.com (see here for one example: http://www.codeplex.com/spdwfextensions#).

The good news with SPD is also the bad news. If we have a business requirement that we can't meet with SPD out of the box, then we can always create a custom action. The bad news is that custom actions are 1) pretty hard to create in the first place and 2) far outside the realm of what an information worker is ever going to do since it requires coding with visual studio and the like.

This isn't always such a big deal because in many cases, if you need a custom action you're doing something pretty specific to your business and process. In such cases, the effort to create a custom action is probably justified. However, Microsoft missed one entire category of common functionality with SPD 2007 -- string manipulation. There simply isn't good support for common string manipulation features in SPD 2007. These functions include:

* Convert to upper case or lower case
* Substring
* Index
* Trimming

This problem is largely solved in SP 2010, but what about now and today? What's an information worker to do?

Calculated Columns to the Rescue

It turns out that SharePoint, generally speaking, does provide good string manipulation functionality via calculated columns on lists and libraries. We can leverage this capability in our SPD workflows by following these overall steps:

" Create the list or document library against which your SPD workflow runs. " Create a second list whose purpose is to perform calculations. " When a calculation is needed, create an item in the "calculation" list and then extract the calculated result. " Use the result as needed in the workflow.

A Simple Example: Convert Text to Upper Case

The following short example demonstrates how to convert some arbitrary text to upper case. To set this up, we need the following artifacts:

1. One custom list with a Title and calculated field. We'll use this list to perform the calculation.
2. Another custom list to run the workflow in the first place.
3. A workflow that glues these together.

The workflow itself is straight-forward but does force us to do something tricky in order to make it work. These are the steps:

1. Create an item in the "calculation" list.
2. Tricky step: Copy the item we just created into the same list.
3. Retrieve the value of the calculated column from the copied item.
4. Use that value as needed.

Why are we doing that tricky step #2? It has something to do with how and when SharePoint decides to perform the actual calculation in the calculated column. If we skip the copy step and simply attempt to read from the first item we created, SharePoint won't have performed the actual calculation. To get around this, we make copy the item. This action triggers SharePoint's calculation logic to fire and voila! We can access the calculated valued. If don't avail ourselves of this trick, then the workflow will always return back a blank value for the calculated field. This can be quite annoying and difficult to debug since the calculated value displays in the list view.

Glue It All Together

In the demo, I have two lists:

" WF Starter List " WF Calculated Field

The starter list is an uncustomized "custom list" (it just has a Title column).

The WF Calculated Field list is also a SharePoint custom list with one additional calculated column as shown:

It simply has a column, "UpperVar" that is set to the upper case value of Title.

With lists out of the way, we can create the SPD workflow:

In detail, the steps work as follows:

1. Set a workflow variable, "WfTextVariable" to the literal string "Switch me to all upper case!"
2. Create an item in the WF Calculated Field custom list. Set that item's Title equal to WfTextVariable.
3. Next, copy the item to the same list (this is the annoying little trick we need to do in order to force SharePoint to perform the actual calculation on UpperVar.
4. Calculate the ID of the copied item. Interestingly, the copy list item action (unlike the create list item action) doesn't return back to us the ID of the newly create item. Since this is happening in the same workflow, we assume that the ID of the next item is ID of the item we just created plus one.
5. Retrieve the value from the copied list item back into WfTextVariable.
6. Delete both WF Calculated Field list items (the original one we created as well as its copy).

Summary

SPD doesn't give us much in the way of string manipulation functions, but SharePoint calculated fields do offer quite a lot of string manipulation goodness. The technique described in this article offers an approach you can use to harness SharePoint's string manipulation powers inside an otherwise weak spot within the SPD workflow offering. Better still, we aren't limited to string manipulation - calculated fields offer far more than string manipulation tools. Read more about them here (http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx) and then use them as needed to solve your business problems with SPD workflow.