My career in technology

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:

Click TO Enlarge

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.

Advertisements

Comments on: "Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution" (18)

  1. Hi Jim,

    Your workflow method is still a bit heavy. There is an activity called “Pause for Duration” which will do what you need without creating extra list items or launching a secondary workflow. I included that method when I wrote about using calculated columns to enhance SharePoint Designer workflows last fall.

    – Woody –

    • Thanks Woody! I did mention that possibility about a thrid of the way through the article.

      I expressed concern that any specific durarion might be insufficient to ensure the update occurrs prior to the workflow restarting (like the problem I had with the deletion of the list item), and that in cases where waiting an amount of time that is GUARANTEED to make sure that the update is triggered before the workflow is not optimal, then a secondary workflow would be needed.

      • You’re welcome!

        The amount of time in the delay doesn’t matter. The pause for duration method will *always* pause until at least the next event cycle (it can’t do anything else, really). Essentially, you can’t put in a duration less than that, and the “resolution” of the pause is the event cycle period.

        For example, if the event timer jobs are happening every 5 seconds, and you put in a delay of 6 seconds, and your activity hits at 1 second after the event cycle starts, you will actually delay for 9 seconds – the 4 seconds that are less than your delay, plus the 5 seconds until the next event hits. That isn’t neccessarily the actual timing of the delay, just an illustration with understandable numbers. 🙂

        In other words, just set the duration to zero, and you’ll get the shortest effective delay.

      • Yes, but my concern is – will the update happen before that pause expires and the workflow restarts?

        Are you sure? Every time? As with my concern with the incremental numbering, most of the time you shouldn’t have a problem. I’m not worried about most of the time, I want all of the time. (Call me greedy! 🙂 )

        If you can guarantee that the update will happen before the workflow restarts, I’d say go for the minimum delay pause (5 minutes is the default, IIRC). Otherwise, in a situation where you need high reliability, the secondary workflow gives you that reliability.

        BTW, I liked your post.

    • While Woody posted a link to the blog entries he references above in the discussion on LinkedIn, he did not post a link to it here in his comment. I would be remiss if I didn’t share the link, as his articles are very well done (even if I disagree on the use of the pause in this instance). You can find his entries here.

      Also, his SPD book gets a shout out on another LinkedIn discussion.

  2. Jim,
    This is my favorite of your blog posts yet! EndUserSharePoint.com is calling for solutions like this.
    I think this works and is straightforward, but I wonder if there is any place for using a GUID in the Title to do this in one workflow. I agree with you that waiting for a set time or relying on an increment of 1 are not reliable with volume.
    Tom

  3. […] Due Dates are determined during runtime. As mentioned in a previous entry, workflows have very limited calculation ability. Using a calculated column in the SharePoint list […]

  4. […] the pause for zero minutes trick? Zero minutes waits until the next update period, which happens every five minutes. When you […]

  5. […] has only a few fields. Since workflows can’t manipulate strings worth a damn (see my post Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution), for this approach I use a calculated column in the list to create the name string. I changed the […]

  6. I also try to create Serial number base on on auto number ex. 2011-1, 2011-2, 2011-3, and ….. There we be more than one person will submit the form. Some time, they may submit a same time because each user locates diffrent areas. It there is the way to use the formaual inside the column without do any programming? I am new to Sharepoint.

    Thank you.
    Kim

    • My solution minimizes the possibility of collisions (two people submitting simultaneously and being assigned the same number), but you are correct that it does not completely eliminate the possibility of a collision.

      For the lists I was working on, the risk of collisions was considered very unlikely, so additional efforts to completely eliminate the possibility were out of scope for the project.

      I have one suggestion off of the top of my head.

      First, use the ID column that is built in to SharePoint in the calculated column calculation. (ie: something like Year&”-“&[ID]). Two downsides – first, any deleted items will leave holes in the ID numbering ithout manual intervention. Second, to restart numbering at 1 for the next year, someone would have to go to the calculated column and modify the calculation (if you had 732 items in the first year, the calculation would be something like Year&”-“&([ID]-732). To get around this you could implement some compiled code, and event handler that, every year, checks the ID column of the list, finds the highest number, and rewrites the calculation in the calculated column.

      If I think of any other workarounds or variations, I’ll reply again.

    • Sorry Kim, in my haste to respond promptly, I misread – I thought you were commenting on my post, Add a Unique Auto-Incrementing Column to a SharePoint List. Did you look at that post?

  7. This is an excellent post. I’m going to have to refer to this when answering questions on endusersharepoint.com/STP.

    Looking over it again, why didn’t you choose to copy the ListItemID to a variable and then write that to the Calculation List? That would prove to wire up the relationship rather solid and would prevent any kind of collisions. I’m going to try it out to see if I’m able to use the ListItemID. This seems like such a great way to manipulate data, I’d love to make it absolutely bulletproof.

    Cheers,
    Matt

  8. […] Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution […]

  9. […] Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution […]

  10. […] Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution […]

  11. […] Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution […]

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

%d bloggers like this: