In this two part series, I show you how to take advantage of SharePoint calculated columns in your SharePoint Designer workflows. Part 1 introduced calculated columns. Part 2 will describe several string manipulation functions, and show you how to consolidate these calculated columns into a "function library" of sorts.

Hide the Clutter

In part 1, I showed you how to use a calculated column to enhance a SharePoint Designer workflow. This is all well and good, but what if you have interim calculations? What if you need to do this manipulation on values entered by your user in the workflow initiation form?  Or maybe you have other types of information you don't want getting in the way of your users when they view the list.

This is where that "Function Library" I talked about comes into play. Technically, in SharePoint terms, it is actually a function list. We will create a list in SharePoint that contains not just the calculated columns, but "input" columns for the functions as well. We will then hide the list in SharePoint designer. Your workflows can still access it, but your users won't even know it is there.

Holiday for Strings

String manipulation is a great application for calculated columns.

One common workflow scenario is processing the items sent to an email-enabled list. Typically, you will want to parse the subject line to find some routing information. This information may take several forms, including:

• The beginning of the subject up to a delimiter
• The end of the subject following a delimiter
• The information contained within a pair of delimiters
• Everything except what is contained between a pair of delimiters

Of course, there are other possibilities, but these should be enough to illustrate the concept.

Building the List

We're going to create a list that supports all of the calculations described in the previous section. To start, of course, we need to create the list. (Remember, SharePoint Designer 2007 doesn't have the ability to create or edit the columns in a MOSS 2007/WSS 3.0 list, so we'll do this in the web UI.)

1. From Site Actions, select "Create"
2. From the Create page, select "Custom List"
3. Give it the name TextFormulas. Since we're ultimately going to hid the list, don't show it on the QuickLaunch
   
4. Click Create

Now that we have a basic list, we need to create our input columns:

1. Display the newly-created TextFormulas list.
2. From the Settings toolbar, select List Settings
   
3. There is a "Title" column created by default. Click on it, change the name to "SourceString", and click OK.
   Note: This step isn't technically required, but it helps things make sense in the Workflow Designer
   
4. Click Create Column
5. Enter "Delimiter1" for the Column Name.
6. Make sure "Single line of text" is selected as the field type, and click OK.
   
7. Repeat steps 4-6, except use "Delimiter2" as the column name.

Once you have your input columns defined, you might think it is time to create your calculated columns. While you could, there is one more step you might want to perform. Because the output from string calculations isn't always obvious, it can be helpful to have some sample data to work with so you can see if the formulas are doing what you want them to.

Add an item to your list:

This item will give you all of the possibilities that we might want to work with - text before, after, within, and outside of, delimiters.

Now we're ready to create the calculated columns.

For the first column we just want the text to the left of the first delimiter. This has the simplest formula, but it isn't necessarily as obvious as you might think. The formula is "trim(left([SourceString],find([Delimiter1],[SourceString])-1))"

We're using three string functions: Trim(), Left(), and Find(). They're also "nested", meaning that we're calling one function from within another function.

The obvious function is "Left()". It takes two parameters, the string we want the left hand side of, and how much of the string we want. Unfortunately, since this is delimited rather than a fixed position, we need to "Find()" the position of the delimiter in the source string.

That's all well and good, but why are we then subtracting 1? That's because we would otherwise return the delimiter itself in our results. Find returns the position of the delimiter, and Left function expects a count of returned characters. Since we don't want the delimiter, we subtract 1 so that we get the position immediately before it.

The Trim() function gets rid of any leading and trailing spaces, as even if you don't see them, these can make comparisons fail.

Create your calculated columns in accordance with this table:

Notice that each formula builds on the one before it - we're using the results of some calculated columns as input to others. This helps us avoid the 8-deep function nesting limit, as well as the 1000 character formula length limit. It also makes them a lot easier to read!

Assuming the formulas are entered correctly, viewing your sample data item will give you these results:

While the value for "AfterDelimiter1" doesn't look very useful in and of itself, that is only because of the particular SourceString we are using. In this case, it is simply an interim value for deriving the BetweenDelimiters result. With a different SourceString it could be the final answer you are looking for. For example, you may have a simple two-part source with a single delimiter, such as "G131131|Memory Failure". That string, with just the pipe (|) as Delimiter1, will result in "Memory Failure" for the AfterDelimiter1 value.

Back to SharePoint Designer

Once you have your function list created, you can use it in your SharePoint Designer workflows.

For this example, we're going to create a workflow on an email-enabled Time Off list. When a new item arrives, we want to parse the subject line to get the reason for the absence and assign it to the reason field. The reason text follows a dash (-), so we're just going to use the "AfterDelimiter1" calculated value.

Note: You may need to enable email-based workflows with the following command before using this example: "stsadm -o setproperty -pn declarativeworkflowautostartonemailenabled -pv true" See this KB Article for details.

When you define your workflow, first check the "Automatically start this workflow when a new item is created box, as shown below:

Click Next.

From the Actions menu, select Create List Item. (Since the menu is built from recently used actions, you may need to select it from the "More Actions" dialog.) You will get a new line in the Actions block that looks like the one below. Click the "this list" link.

Select the TextFormulas list from the dropdown:

The SourceString (*) field will already be chosen, as it is a required field. (This will be the "Title" field if you didn't rename it earlier.)

Click the "Modify..." button. You want to use the E-Mail Subject field from the current item as the source.

After you click OK, Click Add in the Create New List Item dialog, and add the Delimiter1 field. Enter the dash as the Value. When you click OK, the Create dialog should look like this:

Click OK. Notice SPD automatically generates a variable called "Create" as the output of this function. That variable will hold the item ID for the formula item we create. This will be needed later.

A Pregnant Pause

The next action may seem a little odd. We're going to select the "Pause for Duration" action, and then set it to 0 days, 0 hours, and 0 minutes. The reason for this is that the calculations in our function list item don't take place instantaneously. If we were to try to grab the result as the next step, all we would get is an empty string. By telling our workflow to Pause, we give SharePoint a chance to catch up.

Note: Even though we set everything in the pause to zero, the workflow will wait until the next event cycle to continue. This delay may be a minute or so.

Back to Work

Once the workflow comes back from its coffee break, we need to get our value back.

1. From the Actions menu, select Set Field in Current Item.
2. Click the "field" link, and select Reason from the dropdown.
3. Click the "value" link, then click the fx button that appears.
4. Select TextFormulas for the Source, with the Field of AfterDelimiter1.

Now we need to tell the workflow how to find the row we want. Here's where that "create" variable comes into play.

1. Select the TextFormulas:ID field from the Field dropdown
2. Click the fx button beside the Value field.
3. Select Workflow Data for the Source, and Varable: create for the Field
4. If your lookup dialog looks like the one below, click OK
   

The reason lookup should now look like the capture below:

Click OK.

Cleaning Up

Now it is time to clean up after ourselves.

Just as "real" SharePoint developers need to keep in mind the need to "dispose" objects they create once they are done with them, we need to delete the formula record we created now that we are done with it. Fortunately, SharePoint provides a "Delete Item" action item for us. Select it from the Actions menu, and click the "this list" link. As you might guess by now, we're going to use the same "create" variable as before to select the item to delete from the TextFormulas list.

Click OK, then click Finish in the Workflow Designer. SharePoint Designer will then validate the workflow and save it.

Covering our Tracks

Finally, as I mentioned way back at the beginning of this article, you may not want your users to readily see your formula list. Especially since the interim information will be hanging around during that pause. Although we suppressed the list from the Quick Launch when we created it, people can still see it from the "View All Site Content" link. To make it go away from there, we can take advantage of SharePoint Designer's ability to "hide" a list.

To hide a list, first open the Lists folder (you won't need this step for document libraries).

Right-click the list you want to hide and select Properties, as shown below:

Click the "General" tab. Click on the checkbox labeled "Hide from browsers".

Click OK

Now the list will be "invisible" via most normal ways of discovering SharePoint content. That is also why we waited until after the workflow was done in order to hide it. Although you can still see and work with hidden lists in most parts of SharePoint Designer, the function called by the Workflow Designer to enumerate lists is the same one used by the web interface, so you wouldn't be able to select it.

Conclusion

This second part took a bit longer to write than I expected. I hope you found it worth the wait!

In these two articles, I have showed you the power of SharePoint calculated columns, and how to use them in a SharePoint Designer workflow. In the process, I have introduced a number of other concepts, including:

• String functions
• Hidden lists
• Email enabling lists
• Using the output of one calculated column to feed another.
• Workflow variables

Even so, I have barely scratched the surface. I encourage you to explore further the capabilities of calculated columns and SharePoint Designer workflows.