Workflow automation – part one

Monday, September 21, 2020

For many years FileHold has provided an adaptable and flexible workflow feature that can be used in a wide number of use cases; from approving design documents to driving insurance underwriting processes.

Starting with version 16.2 we have introduced an optional add-on that provides some of the most requested automation features. Functions are available to automatically choose the workflow template to start, set the workflow observers, add supporting documents and select the users and groups that will complete the workflow tasks.

In part one of workflow automation we will take a deep dive into the automatic routing feature that allows you to use the values in metadata and other data sources to select the users or groups that will complete the activity. We will consider the following four use cases:

  • Route the workflow activity to the department manager for approval,
  • Optionally route high value invoices to a director,
  • Select exactly two (or three or more) users to approve a document from a pool of many and
  • Prevent an approver from approving a workflow they started.

Before we get too deep, we should talk a bit about plug-ins. Fair warning, it is about to get technical. If you feel yourself dosing off or feeling confused, this is totally normal. Give your IT colleague a poke or give our professional services team a shout if you want some help.

The workflow feature has always provided a technology solution we call plug-in hooks to allow for customizing workflow behavior. These hooks allow us to augment the normal operation of the application server without changing the product. In version 16 we expanded the available hooks that can be used with workflow.

An add-on like this is delivered as a DLL plug-in that is installed on the application server and configured in a way that lets the application server code work seamlessly with the extension. There are two keys to making this plug-in powerful: it uses a combination of FileHold merge tags and SQL to configure its behavior and this “hybrid” SQL is inserted directly into a workflow template. This makes it straight forward to create template and activity specific automation.

Hybrid SQL is inserted directly into one of the description fields in the workflow template. It allows for an informative description to be included and uses a special sequence of characters to define the start of a configuration modifier. In general, it follows the pattern below.

[informative description][### modifier-name # <hybrid-SQL>] … [### modifier-name # <hybrid-SQL]

The square brackets indicate option information. Angled brackets signify mandatory information. The hash (#) symbols explicitly call out the beginning of a modifier. For example:

This activity is part of a demonstration of using the workflow plug-in for automated routing.
### auto-route workflow #
SELECT ObjectId FROM ch_userrolemanager.dbo.UserObjects
  WHERE ObjectName = [[md|Employee name]]

The configuration is parsed in three phases: the list of modifiers is extracted from the description; merge tags are expanded then the SQL is executed. The auto-route modifier is used in the activity description.

Route the workflow activity to the department manager for approval

With these basic configuration details, let’s look at our first use case. A document may require a different approver depending on some contextual information related to the document. For example, the department or plant associated with a document may determine the approvers for an activity.

The three routing options included in a workflow in the standard product are static user or group, one user from a list of static users or groups and ad hoc where the workflow initiator chooses who to route the workflow to. This means that off-the-shelf, multiple workflows could be configured for each department or plant, all approvers could be added to a one of X activity and trusted to choose the correct workflows to approve or the initiator could make the assignments during an ad hoc workflow start.

With the plug-in, we would like to read the department or plant information from somewhere and use that to decide who to route the document to automatically. For example, we could assume the department the initiator belongs to defines who should approve the document. Or, the document may have a metadata field for department that is used to determine the approver.

If we assume this latter case, we can use the merge tag for metadata fields to get the value of the document’s department. If the template designers adds all the possible approvers to the activity we can use that list to find an approver with the matching department in their contact details.

We will use the WorkflowTasks table in the library manager database to get the list of users. In order to find the correct tasks, we will use a couple of special plug-in specific merge tags. These special tags are similar to the standard merge tags except they are delimited by curly brackets instead of square brackets like {{<tag-field}}. A tag-field could be one of metadata-version-id, document-version-id, document-id, activity-descriptor and workflow-guid. We will use those last two for our case.

We will also need the UserAttributeValues table in the user role manager database to get the department attribute and the AdamObjects table in the library manager database to convert from the user id in the workflow tasks to the user guid in the attributes. This leads us to the following hybrid SQL.

### auto-route workflow #
SELECT [ObjectId]
  FROM [ch_userrolemanager].[dbo].[UserAttributeValues]
  WHERE AttributeId = 16 /* department */
    AND AttributeValue = [[md|Department]]
    AND ObjectId IN (
      SELECT ao.ObjectGuid
      FROM [ch_librarymanager].[dbo].[WorkflowTasks] wt
      INNER JOIN ch_librarymanager.dbo.AdamObjects ao
        ON ao.ObjectId = wt.UserId
      WHERE wt.WorkflowInstanceGuid = {{workflow-guid}}
        AND wt.ActivityDescriptor = {{activity-descriptor}}

One thing to note about merge tags is that they are always inserted as nvarchar. For example, if the department is “Engineering”, the department merge tag will expand as N'Engineering'. This may require you to pay special attention to type conversion, but we will look more at that below.

Optionally route high value invoices to a director

Some approvals change their sequential path according to things like invoice value. For example, a line manager may be able to approve invoices up to $10000 but a director needs to be involved when that amount is exceeded. In this case, we would like to include an optional activity that will only take place when the invoice is $10000 or more. It is a simple variation on the previous case and the auto-route modifier can do this by returning no users. That will effectively override the workflow activity when it is not needed.

For this scenario we will assume there is a FileHold group setup with the directors called “Invoice approver – director”. We will also use a metadata field called “Invoice amount” to know if the invoice exceeds the director threshold. Finally, we will use this opportunity to demonstrate a method to avoid a type case error in SQL by declaring an invoice amount variable and casting our metadata value in the declaration.

### auto-route workflow #
DECLARE @InvoiceAmount DECIMAL = CAST( [[md|Invoice amount]] AS DECIMAL )
  FROM ch_userrolemanager.dbo.FileHoldGroups
  WHERE GroupName = ‘Invoice approver – director’
    AND @InvoiceAmount >= 10000

Select exactly two users to approve a document from a pool of many

FileHold introduced a feature to allow one user of many to approve a workflow back in version 14, but there may be certain document control cases that require two users from many to approve a workflow activity. Although we cannot change the logic for one activity, we can have the same effect by using two one of X activities back-to-back and just make sure the person that approves the first one cannot approve the second.

For this case we will look back into the previous workflow activity to find every user that was voided when the workflow was completed. Voided is the status that FileHold gives to all the users that did not complete the activity in a one of X scenario. The TaskStatus column in the WorkflowTasks table will have a value 5. Note that you can always find enumerations like this in our API reference guide. We will use the activity descriptor tag minus 1 to give us the prior activity. Once again, we will use the AdamObject table to convert between the user id and the user guid.

### auto-route workflow #
SELECT ao.ObjectGuid
  FROM [ch_librarymanager].[dbo].[WorkflowTasks] wt
  INNER JOIN ch_librarymanager.dbo.AdamObjects ao on ao.ObjectId = wt.UserId
  WHERE WorkflowInstanceGuid={{workflow-guid}}
    AND ActivityDescriptor = {{activity-descriptor}} - 1
    AND TaskStatus = 5 -- Voided

We do not need to worry about forcing type conversion in this case with the activity descriptor as SQL can figure it out on its own.

Prevent an approver from approving a workflow they started

Workflow is used for cases other than simply approving a new design document. It also drives business processes, for example, IT procurement. Imagine you request a new mouse by sending a requisition through a workflow to a group of IT managers. It seems simple enough on the surface, but what if one of those IT managers wants a new mouse. Should they be able to approve their own request? If the answer is no, the plug-in can solve that case as well.

This last case is going to look into the workflow tables for answers again, but this time it will include the Workflows table in the library manager database where the initiator details are stored. The template designer will include all the IT managers in the approval activity and the plug-in will compare that list to the initiator and exclude them.

### auto-route workflow #
SELECT ao.ObjectGuid
  FROM [ch_librarymanager].[dbo].[WorkflowTasks] wt
  INNER JOIN ch_librarymanager.dbo.Workflows w
    ON w.WorkflowInstanceGuid = wt.WorkflowInstanceGuid
  INNER JOIN ch_librarymanager.dbo.AdamObjects ao
    ON ao.ObjectId = wt.UserId
  WHERE w.WorkflowInstanceGuid = {{workflow-guid}}
    AND wt.ActivityDescriptor = {{activity-descriptor}}
    AND w.Initiator <> wt.UserId

That’s it! Next time we will have a look at some of the other features in the workflow plug-in and the use cases they solve. If you want to add the optional workflow plug-in to your system, just contact your account representative at [email protected]. Do you need an automation feature that is not handled by the add-on? Drop a line to [email protected] and our professional services team can help.

Russ Beinder portrait Russ Beinder is the Chief Technology Officer at FileHold. He is an entrepreneur, a seasoned business analyst, computer technologist and a certified Project Management Professional (PMP). For over 30 years he has used computer technology to help organizations solve business problems.