Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution

I saw a blog post by Paul Galvin on using calculated columns to do things that SharePoint Designer workflows can’t do themselves.  I had a minor quibble with the solution, and played around a bit until I got a better solution.  (You might want to read Paul’s post before reading mine, as he makes the case for why calculated columns are helpful to SPD workflows).

My solution is a little more complicated than Paul’s, and probably would only be needed in cases of high-volume usage. But it guarantees that the correct data is populated to the correct workflow.

In my recent workflow work, I’ve been working with SharePoint Designer (SPD) for several reasons – not in the least because I don’t believe in using a nuke when a flyswatter will do.  Also, in my current situation, as a consultant, there is no guarantee I’ll be there six months from now, or a year from now, and, while I would love repeat business, it is better for the customer to give them solutions they can manage and update themselves, when possible.  But, I have been doing workflows with InfoPath forms, which can fill in a lot of the functionality that SPD workflows are missing.  Paul’s article, then, wasn’t directly applicable to my current work, but I was intrigued with his solution, because I can see it’s applicability down the road in future projects.

Briefly, his solution calls for the creation of a SharePoint list to do the do the calculating or string manipulations and other functions using a calculated column.  The SPD workflow will create a new list item in the special list (which I will refer to as the “Calculation List”) using the data that needs to be manipulated, and then retrieve the calculated data from the list item before finally deleting the list item from the list.  The workflow then carries on, making use of the data it couldn’t calculate by itself.

The only wrinkle is that the calculated column doesn’t do its programmed data manipulation on the creation of the list item (at least not immediately, as we will see). If you try to retrieve data from the column, you get a null value.  Paul’s solution is to copy the new list item to the same list, then retrieve the calculated data from the copy of the new list item.

The only problem I see with this implementation is that he makes the assumption that the ID of the copy is the ID of the item you are copying +1; you create an item then copy it, the copy should be the next item, right? (The copy action doesn’t return the ID of the new copy of the item, unlike the create item action, which returns the ID of the created item.)

If your workflow is on a very busy list in a large farm, this assumption could cause problems.  If two or more workflows were running at the same time, it is possible (though unlikely) that two or more creates could occur before a copy is made. If this happens, the manipulated data you pull from the Calculation List may not be the correct data.

I tried a few workarounds.  For example, say I had a string “My Data” that I wanted to change to all caps.  In Paul’s solution I would assign the new item’s title to equal “My Data” and the calculated column (once it calculates) would have the value “MY DATA”.  By making a copy, the list items would have these values:

ID	Title	CalculatedValue
1	My Data	Null
2	My Data	MY DATA

Remember, I want to avoid situations where:

ID	Title	CalculatedValue
1	My Data	Null
2	Some Other Data	Null
3	My Data	MY DATA
4	Some Other Data	SOME OTHER DATA

Since I know the ID of the created item (“1”), I can guess that the ID of the copy is “2”, but it is only a guess.   Might there be some other way to retrieve the copy of the created item?  

I tried deleting the created item (ID=1), and then trying to match to the copy by matching a list item where Title=”My Data”.  With deleted, you would expect that the only item that the workflow would find would be the copy.  But guess what I got?  When I accessed the values of the matching item, the calculated value was Null.  When I retrieved the ID of the list item I was finding, I was getting ID = 1 – even after I deleted the list item! 

After further investigation, I found that if I had the workflow pause and wait for the five minute minimum to pass, that problem is resolved.  Apparently deleting a list item using an SPD workflow action is asynchronous, and if you don’t wait for the deletion to finish, the workflow will still find the not-yet-deleted item. 

But a better solution suggested itself to me, one that doesn’t require a wait of five minutes.  If I did not delete the list items in the Calculation list as suggested by Paul, when I returned to the Calculation list after running the workflow, the calculated value was there in the initially created item (the one that had a Null value during the workflow).  I bet waiting the five minutes would give the list a chance to update itself, but that would again involve waiting.  But I hadn’t waited for five minutes before visiting the list to check the values.  Something was forcing the calculated value to get calculated on or before my visit.  Was there another way to manage the timing, one that would perhaps force the calculation?  I had something that I thought might do the trick… 

A secondary workflow.  Understanding SharePoint Journal has a great issue on secondary workflows, and it is free to anyone who signs up on the mailing list (look for Bonus Issue #1), and in my experience with their mailing list, it has not been spammy. 

Kicking off a workflow on the calculating list when the new item is created either gives SharePoint the time it needs to do its asynchronous calculating, or it forces the calculation directly. Either way, it removes the need for the copy and the ambiguity of the ID of the copy, and reduces the wait time to the absolute minimum.

He is how to configure the workflows:  

Workflow actions

You have a “Content” list on which the primary workflow (“Content workflow”) is running, and a “Calculation” list on which the secondary workflow (“Calculation Feedback workflow”) runs.  The Content list has a Title, a text field called Text, and a Boolean field called Ready with a default value of “No” (false).  The Calculation list has a Title, a TextToUpdate text field, and a calculated field called UpperVar, that converts the value of TextToUpdate to upper case. 

=UPPER(TextToUpdate)

On creation of the new item in Content, the workflow creates the new item in the Calculation list. Use the Title field in the Calculation list item as an ExternalID field (or add an ExternalID column to the calculating list), and populate it with the ID of the item your original workflow is running on, or some other unique name to uniquely identify the correct workflow item, when the calculating list item is created. Set the value of TextToUpdate to the value of Text.

Set Title to your external ID, set TextToUpdate equal to Text

Set the original workflow to wait until field change (wait until the column Ready has a value of “Yes” (true)). 

Have the secondary workflow on the calculating list start automatically on creation of the new list item, set it to wait to make sure the calculated column is not empty, then find the item that the primary workflow is attached to using the ExternalID, and update the Ready field with “yes” (true).  

Workflow waits, then sets the Ready field on the item whose ID matches the Title field to "Yes"

The next step, now that Ready is now “Yes”, is to reset the flag to “No”, as I have additional uses for the flag later in the workflow. It is not otherwise necessary to reset the flag.

In this particular WF, since the WF is a demo and I am not actually using it to do anything, I am setting the source field (Text) to the value of UpperVar. In a real WF I would set the value of a WF variable to the value of my calculated field to use in the WF.

Get the data in UpperVar...

...from the item whose ID matches the ID in the create WF variable.

Then I delete the list item in the Calculated list. In reality, either WF would then delete the Calculated list item, and it might even be better to have the secondary workflow do it. 

Select the Calculation list item to delete, matching the ID in the create variable.

That is all of the adjustment needed to make Paul’s process work reliably without room for error. But there is more that can be done with this idea.

Implied in the way the text of Paul’s article was written was that there would be a one-to-one relationship between calculations that you needed performed and lists to perform the calculations. IMHO that would be needlessly complex and require more overhead than having one list to perform any calculation you needed, for any workflow on the site.

This list could serve multiple workflows running on different lists all looking for different calculations.  By adding a ListName column to the Calculation list, you can have different lists write to the calculating list and use conditional branches in the secondary workflow to set the Ready flag on the right item in the right list.  By adding different calculated fields, you could use the same source data field, TextToUpdate, to produce different data results.

The DateCalc calculated column adds three months to a date value saved in the TextToUpdate field.

=DATE(YEAR(TextToUpdate),MONTH(TextToUpdate)+3,DAY(TextToUpdate))

The TextCalc changes the format of the dates and adds some text.

="From "&TEXT(TextToUpdate,"mmm-dd-yyyy")&" to "&TEXT(DateCalc,"mmm-dd-yyyy")&" is three months"<br />

The DollarCalc adds tax to the dollar amount saved to the TextToUpdate field.

=TextToUpdate*1.08

(Yes, I know, multiplication is something that can be done in the workflow itself. I just wanted to show a variety of functions using the same TextToUpdate field.)

(Want to know more about calculated column formulae?  Try here and here.)

You could calculate these values at different steps in the WF, or you could add additional input fields and run several calculations at once.  

Here are some examples using the calculated columns above, configured like the first step.

Calculating a date three months away.

Calculating with tax.

I did each of these in separate steps, but they could have been in separate workflows operating on different lists (if I had configured and populated a ListName column, and built the logic into the secondary WF). Here is what the Content list item looked like before:

The initial values before the workflow was run.

And here is after:

Final values after the workflow runs.

And here is what got created (and deleted) in the Calculation list:

Items created (and deleted) from the Calculation list.

In each of these items, the calculated columns tried to perform their calculation on the value of TextToUpdate, but the workflow only retrieves the values that make sense for the particular calculation needed.

So in summary, Paul’s solution will probably work well for most situations, but isn’t guaranteed to return the correct item, especially in heavy traffic. My solution is more complicated, but guarantees to return the correct Calculation item (and the intended calculated value). And the Calculation list can be used to do any calculation you need that can’t be done within the workflow itself, even in multiple workflows on lists and for multiple calculations and kinds of calculations.

Huge thanks to Paul Galvin for the original post, Bjørn Furuknap & USPJ for laying out how to do a secondary workflow.

More posts about SharePoint.