1.877.833.1202

Using Powershell to mass approve workflow tasks

Sunday, August 13, 2017

Sometimes you need to automate an update of data in FileHold for a large number of objects. Of course, if the objects are documents, you can use tools like the mass metadata edit or some of our API example programs if you are changing metadata values.

What if you want to mass approve a bunch of workflows? Workflows are highly controlled in FileHold to prevent arbitrary changes or updates. There is no standard client feature that allows you to mass approve them.

Perhaps you have a workflow activity to deal with a specific government regulation. New legislation comes into force that makes the activity redundant, but you have thousands of active workflows that are, or will be, waiting on this activity. You could cancel all the workflows en masse, but perhaps there are many completed steps that you do not want to do over. A single activity cannot be removed from an active workflow, but you could simply approve them all to move pass the redundant activity.

Approving thousands of activities could take hours or days as passwords may need to be entered to approve each task. A one-time bulk approval script could really help the situation. It could either run according to the user id who is intended to approve the task or it could delegate each task to a special user before approving it. Also, it could update the comments to indicate the reason for the bulk approval for anyone that was auditing the workflows in the future.

In an earlier blog, I showed how Windows Powershell could be used to interact with the FileHold API. Powershell is a great way to go for making quick work of specialized one-time bulk tasks like the one described. The FileHold API documentation tells us that the RaiseTaskCompletedEvent method of the WorkflowManager web service can be used to approve tasks. Reviewing that method’s documentation tells us that we need the task and workflow ids for the task we would like to approve.

TIP: I am assuming you have some Powershell skills in order to use the information in this article, but whether you do or not, try any new scripts on your development or test server first before you deploy them to your production server.

Finding the task and workflow id could be fairly tricky using the API as there may be a number of variables that come into play when selecting the task. We may even want to have a manual override on certain tasks. This is a job ideally suited to SQL and the Workflows and WorkflowTasks tables in the library manager database. We can join these tables on the WorkflowInstanceGuid column and then use the other columns to select the specific rows we are after.

Once we have our set of rows in SQL we can save the results as a tab delimited file. We could load this into Excel or some other tool in case we want to review before doing our updates.

TIP: SQL Management has options to save results as comma separated or tab separated values. Using a tab separator is often safer than a comma as a tab character is unlikely to occur in your data, but a comma could be in a workflow name and complicate using the file later. We will only need the task and workflow ids for our bulk update, but it is often helpful to include many of the other fields if you will be reviewing the data before the update.

Here is an example list from SQL that we will use with our bulk update.

TaskGuid WorkflowInstanceGuid
AB0D25E1-9257-4AF1-9EB9-0022CD0A404B 80195656-0D6E-474E-BBFD-66E5E39729D1
B1717405-D57C-462A-AEFD-4939E3948589 420B9CE1-926D-4C44-895F-4CB39E49F57E
3C08B59B-6C9C-4260-B198-8F3568E59774 D714C925-2660-4F00-9AF4-C7A577457DE5

With the list of tasks and workflows in our delimited file, we can use them in Powershell quite easily using Import-Csv.

$tasks = Import-Csv TaskList.txt -Delimiter `t

Now the task variable contains the list of tasks we want to update in just the right form for looping through one at a time. If we imagine we have a FileHold approval function in Powershell called FH-Approve-Task, our loop might look a little like this.

foreach ( $task in $tasks )
{
    FH-Approve-Task $task.TaskGuid `
                    $task.WorkflowInstanceGuid `
                    ( "Bulk approval. See Fred Smith.” ) `
                    $password
}
TIP: You can improve the readability of your Powershell code by extending lines with the grave accent (`) character when you are not using to escape special characters like tabs.

The Powershell foreach command will assign a single record in the task variable for each row from the task list. We can access each column from that row by its heading name in the file. The headings come directly from the column names in SQL: TaskGuid and WorkflowInstanceGuid. We need the id for the task and the id for the workflow to use the RaiseTaskCompletedEvent method. That method also requires us to provide a password for the user who is approving and gives us a chance to add a comment.

All we need now is the FH-Approve-Task function. There are a few things that it should and could do. First off, it needs a reference to the web service. We will first check to see if we already create the reference as we only need to do it once for the session as we will store the reference in a global variable. The basic principal for this is described in my previous blog when we accessed the session manager service to login. The big difference here is now we must have a valid session. This is provided by adding a FileHold cookie along with the session id we retrieved during the login. The cookie name is always FHLSID. We have created a Powershell constant with the name FILEHOLD_COOKIE_NAME, so it can be easily used elsewhere.

if ( !$lmWorkflowManagerProxy )
{
   $uri = $fileHoldUrl + "/LibraryManager/WorkflowManager.asmx?WSDL"
   $lmWorkflowManagerProxy = New-WebServiceProxy -Uri $uri
   $lmWorkflowManagerProxy.CookieContainer = New-Object System.Net.CookieContainer
   $cookie = New-Object System.Net.Cookie( $FILEHOLD_COOKIE_NAME, $global:sessionId, "/", $global:hostUrl )
   $lmWorkflowManagerProxy.CookieContainer.Add( $cookie )   
}

Now that the web service is setup, we can start to call its methods. First off, we should double check that the task is not already approved just in case something changed since we created our list. We should also do a quick check on the workflow id. We could ignore both these things, but for a quick one-time script it is often a good idea to check.

The task list was created manually and perhaps there is a mistake where the workflow id does not match what it should for the task. Perhaps we partially run this script before it encounters an error that causes it to stop. We could delete the completed rows from our task list and start it again, or if we check the task status, we can just run it without changing a thing.

$workflowGuid = $lmWorkflowManagerProxy.GetTaskWorkflowAndState( $taskGuid, [ref]$taskStatus )
if ( $workflowGuid -eq $workflowInstanceGuid )
{
    if ( $taskStatus -eq [FileHold.Common.WorkflowEngine.TaskApprovalStatus]::Undetermined.value__ )

A task that has not been completed has the status Undetermined. That value like so many others is kept in the FileHold.Common DLL and with a little setup at the beginning of our script, we can use it here.

Add-Type -Path 'C:\Program Files\FileHold Systems\Application Server\fileholdadm\FileHold.Common.dll'

At this point we know everything is correct and we can actually approve the task. We provide the ids, comments, and password provided in our original call to the function. We will not be attaching a file, so we can set the two related parameters to null values.

$lmWorkflowManagerProxy.RaiseTaskCompletedEvent `
    ( $workflowGuid `
     ,$taskGuid `
     ,[FileHold.Common.WorkflowEngine.TaskApprovalStatus]::Approved.value__ `
     ,$comments `
     ,$NULL_GUID `
     ,$NULL `
     ,$password )

As for most web services methods in the API, errors will be returned as exception, so we can setup Powershell’s exception handling if we would like to provide any special exception processing. I often have a mix of special handling and no handling in my one-time tools as it is often easier to let Powershell’s default exception handling deal with the issue. I also like to use the Start-Transcript cmdlet included with Powershell to keep a log of the processing. This gives me an easy way to review what happened over the thousands of records.

A downside to this approach of bulk approving the tasks is that you need to know the user id and password of the person who is the approver. This problem is even worse if there is more than one approver. Another side effect is that the logs will show the approvals were performed normally by this person, which is not really true.

An enhancement that can be made is to use the RaiseTaskDelegated method to delegate the approval to another user. You could create a temporary user and name it Bulk Approver, or something similar, with the appropriate rights and delegate this user to this task before doing the approval. When the bulk approval script is done, simply disable the user. This in addition to the special comments will mean that no one can confuse how the tasks were approved.

Well, that is the meat of it. A link to the complete cmdlet is included at the bottom of the page.

You can learn more about the various FileHold features used in this article at the following links. If you would like help creating automation like this for your FileHold installation you can contact the FileHold professional services team at support@filehold.com for assistance.

https://www.filehold.com/help/workflow/approving-documents

https://www.filehold.com/help/workflow/delegate-tasks

The FileHold API is included with every FileHold license, but unlimited FileCare support is not included. Also, you should be aware of the terms of use for the API.

http://www.filehold.com/help/developer/web-services-api-overview

The full sample script is at the link below.

ApproveTasks.zip

profile picture russ beinder 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 the last 30 years he has used computer technology to help organizations solve business problems.