Objectif Lune joins Upland Software.Learn more >

Back to all How-tos

Interating with the Data Repository API

This How-To will show you how to interact with the Workflow Data Repository using it’s scripting API. Because the default tasks involving the Data Repository only offer to Insert or Update, any other action requires the API to be used in a scripting action.

While the script examples demonstrated here are in JavaScript, the API is available for all languages supported by the Run Script action.

The Workflow Data Repository

As you probably already know, the Workflow Data Repository is accessed by clicking Tools > Data Repository Manager.

For the purpose of this article, an example Group named “customers” containing the Keys “customerID”, “firstname”, “lastname” and “email” has been created. Some fictional data has been added in KeySets as shown below:

Querying a single row

So one of the things that we need to do, is to grab one or more rows from the repository and use them in your process. This example gets 3 fields (“firstname”, “lastname” and “email”) from the CustomerID field. It assumes there’s a local variable called %{CustomerID} set in the workflow process.

var CustomerID = Watch.GetVariable("CustomerID");
var Repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
var customer = Repo.GetKeySets("customers",'["firstname","lastname", "email"]',"customerID = '" + CustomerID + "'");

Omitting the last option from GetKeySets (the filter on CustomerID) you can get all the rows from the data repository. Useful for reports, cleanup, or custom filters based on more complex conditions!

Alright, so now we have a JSON file in the %9 variable… what do we do with it? Well, if you have a webpage that’s making an HTTP request to Workflow, you can return that exact data and it’ll be easy to process – JSON is the simplest way to transfer information between any system that supports JavaScript.

You can also pass this variable to Designer and it can be loaded up directly as an object in a script there.

And finally, you can convert that JSON to XML, which makes it useable in, say, the DataMapper module. This can be easily done in a preprocessor script

Deleting a row

Another thing that you might want to do with the Data Repository, is delete a row. This script attempts to delete a client from the rows, then returns “true” or “false” in Job Variable %9 as a response.

var CustomerID = Watch.GetVariable("CustomerID");
var Repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
var deletedCount = JSON.parse(Repo.RemoveKeySets("customers","customerID = '" + CustomerID + "'"));
var answer = (deletedCount > 0) ? "true" : "false";
Watch.SetJobInfo(9, answer);

Inserting values

I put this at the end for the simple reason that in most cases, you won’t need to script since the insertion or update can be easily done through the Push to Repository action task. However, in some cases you might want to use this in a script for simplicity’s sake.

So here’s a basic example of an insertion:

var Repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
Repo .AddKeySets("customers", '[{"CustomerID": "CUJS123456", "FirstName": "John","LastName": "Smith"}, {"CustomerID": "CURD654321", "FirstName": "Richard", "LastName": "Doe"}]');

This inserts 2 different rows into the Users group. But what about updating? Well, updating is a little tougher – there is currently no “update” feature in the API for a whole KeySet, only individual keys. To do this we need to get the ID first.

// Get ID
var Repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
var keySetID = Repo.GetValue("customers", "ID", "CustomerID='CURD654321'");

// Update Values
Repo.SetValueByID("customers", "email", "rich_d@example.com", keySetID)

Going Further

There are other things you can do with the API, essentially all that can be done with the Data Repository Manager is also achievable with the API.

To see all the possible methods of the API, along with examples, please see the Data repository API reference.

Leave a Reply

Your email address will not be published. Required fields are marked *