Associated Calculated Columns in Reusable Workflows (SP2010)

One of the great things about SharePoint 2010 is the ability to create reusable workflows. No more tying a workflow to an individual list or library. Now, you can tie a workflow to a content type, and reuse it in multiple list or library instances. Unfortunately, today I found what I think is a bug in using calculated columns in reusable workflows. Let me lay out the scenario.

Scenario

To play along, you’ll need to understand a bit about site columns, content types, and reusable workflows.

    1. Create a test content type with the following site columns.
      • Cost 1 (currency column)
      • Cost 2 (currency column)
      • Total Cost (calculated column with the function =SUM([Cost 1],[Cost 2]))
    2. Create a test list and set the default content type to be the test content type.
    3. Create a test reusable workflow (no steps needed yet), publish it, and attach it to the test content type.

Got that all set up? Great.

Chances are good, you’re going to want to access the columns from your content type in the workflow – either for logging purposes or to generate messages in emails or what have you. But, since the workflow isn’t directly connected to the list, you’ll need to create association columns to the data you want to use. That’s a straight-forward process, and takes just 3 quick steps.
      1. Click “Association Columns” in the ribbon.
      2. Click “Select a Site Column.”
      3. Find your site column and select it.
Steps to Set Up an Association Column

Steps to Set Up an Association Column

We should be good to go! Now when you want to use the Association Column, it will show up as part of “Current Item” in your workflow.

Selecting an Association Column

Selecting an Association Column

Super easy, right?

The Problem

Hold the phone. Nothing is ever quite THAT simple with SharePoint. What happens with the datatype of that Site Column?

Well, if this is a non-calculated column, you’ve got no problem. SharePoint Designer recognizes the data type of the column and asks you how you want to format it.

Select the format of the returned field

Select the format of the returned field

The same is true if you’re using a list workflow with a calculated column.

But, if you’re using a reusable workflow with a calculated column, you’re out of luck. SharePoint Designer will only return it as a string.

No option to select return type with calculated association columns

No option to select return type with calculated association columns

Worse yet, the string that is returned includes information on the data type – in the case of my currency column, the string returned is “float;#40.0000000000000.”

Unusable string is returned

Unusable string is returned

So, what do you do?

I’m guessing you could write some crazy string manipulation functions to handle the formatting of this weird string.

Or, you could try to set the value of the Association Column to an intervening workflow variable of type double. The interface will choose to return the Association Column as a double.

Setting an Association Column to a Local Variable

Setting an Association Column to a Local Variable

You could try that. But, it won’t work. It LOOKS like it will work. But, if you try to do it, here’s what you get:

Coercion Failed: Unable to transform the input lookup data into the requested type.

Coercion Failed: Unable to transform the input lookup data into the requested type.

Is anybody else getting the feeling this is a bug? No? Okay. Moving right along.

The Total Hack Solution

What I’ve found is that the easiest thing to do is to (if possible) is just replicate your calculations within SharePoint Designer. In my case, this resulted in a series of addition problems with intervening variables.

Replicating the calculation in SPD

Replicating the calculation in SPD

Barring that approach, I think your only option would be to use a list-based workflow on the list to update a secondary non-calculated column and firing that workflow before the reusable workflow. Of course, that completely defeats the purpose of having a reusable workflow.

*sigh*

Any body else have any bright ideas?

Advertisements
Leave a comment

1 Comment

  1. Tony M

     /  February 25, 2016

    Bugger. I was just going to create the same type of reusable workflow with a calculated column that I would fire off from a powershell script and windows task scheduler. Now I’ll have to rethink it. If I come up with anything I’ll let you know.

    Reply

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: