Listing Unpublished Workflows in Collabware

One of the activities that needs to happen when upgrading Collabware 2.X to Collabware 2016 is reviewing and publishing all of the workflows that Collabware creates, as part of the upgrade process, to replace your retention policies.

This may take a couple of hours (or days)  depending on how many policies you have. Now in order to keep the UI nice and Tidy Collabware leaves out all of the metadata in the list of Workflows, but displays them in each Workflow Window, which will normally suffice:

Workflow metadata

In this special case, it will be handy to list all workflows that have not been published yet as a means of easily tracking which workflows you need to publish without having to click on each workflow:

List of Collabware Workflows

We have several options when creating reports like this, but going to the database is fast and easy (just be careful not to change any of the data in the database!)

First let’s understand Collabware’s Database design. Each element in the system gets its own database table, so as we would expect, we can find a Workflow table.

To support multiple languages, the names of the configuration elements (workflows, record categories) will all have a corresponding [Element] Localized table which stores the language specific name of the element (“My Cool Workflow”). So we’ll be pulling data from the Workflow table and theWorkflowLocalizd table joining on the WorkflowID column.

We’ll want to filter on where IsPublished = 0 from the Workflow table and (1 would mean that the workflow is currently published).

Here's an example query:

SELECT WorkflowLocalized.Name FROM [Records_Management_Service].[dbo].[Workflow] Workflow, [Records_Management_Service].[dbo].[WorkflowLocalized] WorkflowLocalized WHERE Workflow.WorkflowID = WorkflowLocalized.WorkflowID and IsPublished = 0

And here's the results:

List of unpublished workflows from the database