OL Learn

Repository query help

I’d like to sort a result set I get from Workflow’s repository by a date. Can I do this with a query clause like “SORT BY”?

There’s no native method to retrieve sorted Keysets from the repository. Your best bet is to fetch the keysets and then use JavaScript’s sort method on the results. So for instance, if the GetKeySets method returned the following:

var keysets = '[{"Date": "2020-04-06"},{"Date": "2020-03-18"},{"Date": "2021-01-14"}]';

Then you can sort the dates with the following code:

var myArray = JSON.parse(keysets);
myArray.sort( function(a,b){
  var d1 = a.Date.split("-");
  var d2 = b.Date.split("-");
  return (new Date(d1[0],d1[1],d1[2])) > (new Date(d2[0],d2[1],d2[2])) ? -1 : 1;
});

One thing I figured out that needed to be added when returning the array back to a Workflow variable is “JSON.stringify” otherwise I just got back a bunch of generic “object object” values.

Here’s an example:

//Purpose: query the repository for a result set matching the current transaction id
//Get transactionid from variable
var sTransactionId = Watch.GetVariable("TransactionId");
//Query the repository
var RepoObject = new ActiveXObject("RepositoryLib.WorkflowRepository");
var ResultKeySet = RepoObject.GetKeySets("Tracking",'["JobFile","sequence"]',"TransactionId='" + sTransactionId + "'");
var KeySetArray = JSON.parse(ResultKeySet);
//Sort the Array by sequence
KeySetArray.sort(function (a, b) {
//convert string to integer for proper sorting
    var seq1 = parseInt(a.sequence);
    var seq2 = parseInt(b.sequence);
    return seq1 - seq2;
    });
//Save results into a variable
Watch.SetVariable("JSONResult", JSON.stringify(KeySetArray));
//Show results in the log
Watch.Log(JSON.stringify(KeySetArray), 2);

Yes, Workflow variables can only contain strings.