Hyland

Philip_Lamphear · ‎05-21-2018

Can someone provide me a sample script that will read a database table and populate a set of workflow properties? I am looking for a way to populate a unity form with information from a SQL database. My thought was to read the database, populate some properties and then push the properties to the Unity Form fields.

Andy_Barker · ‎05-22-2018

I've found myself having to do this fairly often lately for some bits of custom workflow logic, so here's an example of a script I pieced together from other posts here on the community. This example accepts an input value from a property, allows you to use that in a T-SQL command, then places the result into an output property. To get this to work, you'll have to make sure you have a connection string setup for your database. To do that, go to the Unity Scripts tab in Studio and click "Connection Strings". For this example, I'm using a connection to the OnBase db that I've setup ahead of time named "ONBASE_DB_CONNECTION"

// Skeleton generated by Hyland Unity Editor
namespace DBToProperty
{
using System;
using System.Text;
using Hyland.Unity;
using Hyland.Unity.CodeAnalysis;
using Hyland.Unity.Workflow;

using System.Data;
using System.Data.Common;
using System.Linq;

/// <summary>
/// This script will take an imput value from propInput, and query the database to get a return value to place into propOutput
/// </summary>
public class DBToProperty : Hyland.Unity.IWorkflowScript
{

#region IWorkflowScript
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
string PROP_INPUT = "propInput";
string PROP_OUTPUT = "propOutput";
string ONBASE_DB_CONNECTION = "ONBASE_DB_CONNECTION";
string inputValue = string.Empty;
string result = string.Empty;

DbConnection dbConn;
using(dbConn = app.Configuration.GetConnection(ONBASE_DB_CONNECTION))
{
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Fetching value from database: {0}", dbConn.Database));
dbConn.Open();

DbCommand command = dbConn.CreateCommand();
command.Connection = dbConn;

try
{
inputValue = GetRequiredProperty(PROP_INPUT, args);
//Get the attachment names from the tables
command.CommandText =
"<TSQL Command Here>";

app.Diagnostics.WriteIf(
Diagnostics.DiagnosticsLevel.Verbose,
string.Format("Executing Query: {0}", command.CommandText));

DbDataReader reader = command.ExecuteReader();
while(reader.Read()) {
result = reader.GetString(0);
}
reader.Close();

if(result.Length == 0){
throw new ApplicationException("No value was retrieved from database!");
}

app.Diagnostics.WriteIf(
Diagnostics.DiagnosticsLevel.Verbose,
string.Format("Result = {0}", result));

args.SessionPropertyBag.Set(PROP_OUTPUT, result);
}
catch (Exception e) {
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Error, string.Format("An exception of type {0} was encountered while accessing the DB during Unity script execution.", e.GetType()));
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Error, string.Format("Exception message: {0}", e.Message));
}
finally {
dbConn.Close();
}
} //end using for DB Connection

}

/// <summary>
/// Gets a property value and throws an excpetion if the property does not exist
/// </summary>
private string GetRequiredProperty(string propertyName, Hyland.Unity.WorkflowEventArgs args)
{
string propertyValue = "";
if (!args.SessionPropertyBag.TryGetValue(propertyName, out propertyValue))
{
string errorMessage = string.Format("Failed to get a value for the '{0}' workflow property", propertyName);

throw new ApplicationException(errorMessage);
}

return propertyValue;
}

#endregion
}
}

View answer in original post

Andrew_Bilsbury · ‎05-22-2018

Hello. Rather than writing a script to do this, I think a better way would be to create an External Dataset in Configuration for a Keyword on the form and put the SQL in the dataset.

If you have multiple fields to populate, an Autofill will work to populate them after the first value is selected.

Mike_Schaefer · ‎05-22-2018

I would strongly suggest this, as the answer. There's nothing wrong with utilizing Unity Scripts in solutions, but the best practice is to utilize standard functionality whenever possible. For upgrades, we guarantee that standard functionality won't change, but the scripting engine could change for a variety of reasons (such as .NET requirements for different operating systems). Utilizing an External Autofill should fill the requirement nicely.

Alex_French · ‎05-22-2018

The point about sticking with "built in" functionality is a good one, as long as an External Autofill can do what Philip is looking for.

Here are some of the specific situations where an Autofill won't do what you need, or a Unity Script offers a lot of advantages. If none of these apply to you, then the Autofill probably is simpler!

1) You want to get data into Unity Form *fields* instead of Keywords (and you don't want to create extra Keywords just to move the information around)

2) You want to "fill" data that crosses an MIKG standalone Keywords

3) Your query relies on input from a Unity Form field (and you don't want to create extra Keywords just to hold information temporarily)

4) Your query relies on input from multiple Keywords (and you don't want to create extra Keywords to hold information temporarily and extra WF or Custom Action logic to handle tricky data manipulation)

4A) Your query relies on... just plain more info. Like finding a related doc and getting keywords from it, or knowing about the current user, and so on (and you don't want to create extra yadda yadda...)

5) You need to call a database function or stored procedure (I don't think either is possible from an Autofill?)

6) Your database query might change over time, and having a verifiable record of it is important to you (Unity Scripts give you an auditable history, Autofills don't).

7) You need to implement some kind of business logic around "return 1 result" vs "returned 0" or "returned many". The Unity Script gives a place to do that in ways that are often impossible or complicated in pure Workflow.

Alex_French · ‎05-22-2018

@Michael - I completely agree with the point of sticking with the simplest option that is adequate, but in practice everyone's upgrade experiences will vary.

In our recent upgrade from 15.0.1.84 to 17.0.1.67, we happened to have to work with support on several issues relating to Autofills- I think at least one was definitely a bug, some were effects of "intentional" changes in behavior (that I don't think we could have possibly learned about no matter how thoroughly we read Diamond Delta reports and MRG upgrade considerations.