Combining SharePoint Workflows and Calculated fields

In one of my projects I wanted to allow teams to work privately with their documents but also allow the rest of the organization to be able to access everything considered final. The two level versioning feature allowed me easily to do exactly this, but the challenge was to also find a way to encourage the teams to finally “publish” their items. The solution was to have a special column indicate the state of the item.

To make things more interesting, I decided to only use the dialogs of SharePoint designer and the web interface to accomplish this. The easiest way to insert HTML  into a list is by using a calculated column. Unfortunately, as I quite quickly found out, the calculated columns do not update on version and approval changes. The only workaround was to create workflow to react on these changes and then use the calculated column to generate the output.

This is what the list column setup looked like

The IsPublic column gets updated by the workflow and the Visibility column generates the HTML based on the value. There are many posts describing how to use a calculated column together with JavaScript to print out HTML, but all you actually need is to set the result type of the column to integer!

Using SharePoint designer I created the following workflow for the list

The problem with document libraries is that the file might not have been completely uploaded before the workflow kicks in. This is why the workflow needs to first make sure it can access the item before editing it.

There are probably many ways to check if there is a major version available, I decided to convert the version number (which is actually a string) to an int and see if it is higher than 0. After some trial and error I found out that the only way to convert a string representing a decimal (yes, the version column is a text field) value is by first converting it to a double.

The final task was to hide the IsPublic field from the list forms and find some nice icons to indicate the visibility.