Using OneStream Task Manager to Control OneStream Workflows

A little while ago, a client who I'd been helping with the implementation of Task Manager asked me the following question: "Now that we are using Task Manager, we would like it to control our Workflows. Is it possible to do something to ensure that Workflows cannot be certified until all tasks associated with it are approved in Task Manager?".

I thought the question was very much valid. After all, the tasks in Task Manager are tied to Workflow steps in OneStream (at least they can be, and are for this particular client). Task Manager also has approval levels of these same tasks, so it makes sense that tasks tied to Workflows should be fully approved in Task Manager before they can actually be Confirmed & Certified.

Of course you can make tasks and dependencies in Task Manager - you can create a task, connect it to the Workflow and make it dependant on all the tasks connected to its steps - but as far as I know there's nothing stopping anyone from completing a particular Workflow outside of Task Manager, as long as the steps associated with it have been completed.

My answer to the client was, as it usually is when working with OneStream, that "everything is possible" - but I had no clue how to actually solve it - so I added "let me get back to you on that". I knew that Task Manager uses its own custom tables to store everything it needs, and I knew that the client uses a Confirmation Step in their Workflows.

I also knew that the Tasks in Task Manager tied to Workflow steps have the name of their Workflow stored in their Action Parameters, so there's the link. The idea that formed in my head was to use a Confirmation Rule and run it against the Task Manager tables to check the relevant Tasks for Approval Status. If all the Tasks in Task Manager related to a particular Workflow have been Fully Approved, then allow the Workflow to be Confirmed and on its way to being Certified.

How, though?

I started to dig around in the Task Manager tables to see how things were stored. All the tables used by Task Manager have the prefix "XFW_UTM_", they can be seen in the Database under System:

The table that had everything I needed turned out to be the one marked in blue, the "XFW_UTM_TasklistInstance" table which seems to contain all published tasks per time period.

I won't provide a screenshot of the table because it has a lot of columns that I'm not interested in, but I'll explain the columns that I'm actually using:

• WFTimeName: Contains the time period the task is published for

• Approval: The Approval Status (from the last Approval Step completed)

• ActionParameter: For tasks tied to actual Workflow steps, contains the workflow step I'm after

• ApprovalLevel: The Approval Level of the last approval

• ApprovalLevels: The number of Approval Levels associated with the Task. I haven't confirmed this, but I think TM compares this with the Approval Level of the last Approver to see if a Task is to be displayed as Fully Approved. I aim to do the same :)

Time to start writing out the actual code. I'm far from a star programmer, my background is mostly Finance. I guess that's my way of putting a disclaimer on this blog post: This may not be the best and most optimized code with the best naming conventions and whatnot, but it does what it is supposed to. Without all the logging, it wouldn't be very long either.

Some of the code (mainly how to use SQL in OneStream Business Rules) is actually based on this series by Black Diamond Consulting, I highly recommend it for getting into querying tables in OneStream Business Rules.

We wouldn't want to put the code containing all the logic in the confirmation rule itself - if we spot an error later or something needs to be changed we would have to edit every confirmation rule separately if the code stored in every confirmation rule. I've seen some posts about using a Finance Business Rule to store the actual code and just calling this rule from a Confirmation Rule, in order to re-use code and lower maintenance time. This is definitely what we want. Specifically we'd like to call a Function within a Class, within a Finance Business Rule - from a Confirmation Rule.

I'm creating a Finance Business Rule as a starting point, named "ConfirmationRules". I'm thinking this rule can store code used by Confirmation Rules in general. The Class is simply called "TaskManagerChecks". Again I'm trying to think ahead with my naming, if any other Task Manager checks are needed in the future we have somewhere to put them. I've named the Function I'm putting this particular piece of code in "CheckApprovalStatus":

Notice that I'm returning a tuple (approved and info) from this function. The first is whether to pass or fail, the second is text we'd like to display as information in the "Information1" field when we run the confirmation.

Declaring variables:

Building an SQL Query and executing it against the TM table:

Iterating through the rows in the DataTable returned by the SQL Query. Count number of tasks associated with the current WF profile and count number of completed tasks:

Check if the number of fully approved tasks is the same as the number of tasks associated with the WF Profile. If yes, then allow the Workflow to be Confirmed:

We're returning whether to Pass or Fail (True or False) along with the information to be presented from this function via the Return statements - remember the tuple we want to be returned, we specified these when we declared the function).

Now, we just need a confirmation Rule Group/Profile and a Confirmation Rule, that calls the function we just created. The Rule Group/Profile I won't cover here, but the Confirmation Rule looks like this:

Much shorter than the code of our Function :)

Testing it with 3 out of 4 Test Tasks associated with my current WF being Fully Approved:

Running Confirmation:

It fails, therefore it works :)

If I now Approve the Task that was only Prepared before:

...and run the Confirmation again:

We can see that it now passed. This means that the Confirmation Rule actually works and doesn't allow for proceeding to Certification, unless all tasks associated with the current WF Profile are approved in TM.

I hope you found this useful, I hope you'll return for more useful content later!

Martin