Retrieve Ordered Automation Rows in WSProxy Retrieve

Question

I've written a script with WSProxy that retrieves all Automations (Programs) in a Marketing Cloud business unit. Then, I retrieve all runs (AutomationInstance) of that automation and get the status of the last run to check if the Automation errored. Then this gets outputted to the page as an Automation Dashboard.

I found that the 'Status' property of the Automation itself is not accurate enough, it shows no 'Error' status if the last run errored. So for each Program loop, I need to do another WSProxy request that gets the status of the last run, this is pretty taxing on the loadtime of the page.

This is the script:

var prox = new Script.Util.WSProxy();
  
  // Retrieve all automations
  var cols = ["Name", "Status", "ModifiedDate", "CustomerKey", "ObjectID"];
  var filter = {
    Property: "Status",
    SimpleOperator: "IN",
    Value: [-1,0,1,2,3,4,5,6,7,8]
  };

  var list = prox.retrieve("Program", cols, filter);

  var retrieveAmount = list.Results.length; 
   
  // Loop through each automation  
  for(var i = 0; i < retrieveAmount; i++ ) {
    
    var listItem = list.Results[i];

    // Set List item vars
    var name = listItem.Name;
    var customerKey = listItem.CustomerKey;
    var modified = listItem.ModifiedDate;
    
    // Retrieve the runs of the automation in the loop
    var prox = new Script.Util.WSProxy();
    var cols = ["Status", "CustomerKey"];
    var filter = {
        Property:"CustomerKey", SimpleOperator:"equals", Value: customerKey 
    };

    var res = prox.retrieve("AutomationInstance", cols, filter);
    
    // Get the last run status
    var len = res.Results.length - 1; 
    var status = Stringify(res.Results[len].Status);
    
    // If the last run didn't error, get the status of the automation
    if(status != '-1') {
      var status = listItem.Status;
    }
    
    // Set variables for output
    var statusString = statusString + ';' + status 
    var modifiedString = modifiedString + ';' + modified
    var nameString = nameString + ';' + name
  }

My idea was to retrieve only the first 20 automations, and have some sort of selectbox in which a user can select to retrieve more rows. However this requires the first 20 automations to be sorted by 'ModifiedDate' so the most relevant automations get retrieved first. Is there any possibility to sort the rows when you're retrieving them via the WSProxy? Or are there any other ways I can improve the loading time of the page? Thanks

Answer 1

You can use the native JS function array.prototype.sort() for this. Now admittedly this capability is not perfectly replicated from JS to SFMC SSJS, but with some small changes, you should be able to make this work.

First up. .sort() will not work without a formula in it (which is not the case for JS). So we first need to make a formula.

The generic formula I use for sorting by asc. order is:

function(x, y) {
  if (x < y) {
    return -1;
  }
  if (x > y) {
    return 1;
  }
  return 0;
 }

This will work with both letters and numbers.

The issue here is that, this is only for when the value is directly in the array. It won't work right if the array value is an object. So crud. Now what?

With a slight adjustment to the formula, we can grab the values from the objects that we want to compare and get the sorting we want.

function(x, y) {
  if (x.color < y.color) {
    return -1;
  }
  if (x.color > y.color) {
    return 1;
  }
  return 0;
 }

So by having the x and y variables drilling down to a property via the .color, you can now grab only that value to compare against for the object to be sorted.

Put it all together and you get:

var array = [
  { color: 'white', size: 'XXL' },
  { color: 'red', size: 'XL' },
  { color: 'black', size: 'M' }
]

 array.sort(function(x, y) {
  if (x.color < y.color) {
    return -1;
  }
  if (x.color > y.color) {
    return 1;
  }
  return 0;
 });

Outputs:

[
  {"color":"black","size":"M"},
  {"color":"red","size":"XL"}, 
  {"color":"white","size":"XXL"}
]

To do desc. you just switch the return values in the if statements.

Like so:

function(x, y) {
  if (x < y) {
    return 1;
  }
  if (x > y) {
    return -1;
  }
  return 0;
 }

Answer 2

You could implement your own sort function. I've used something like this in the past:

function sortByProperty(property, sortDirection) {

  sortDirection = typeof sortDirection !== 'undefined' ? sortDirection : 1;
    var sortOrder = sortDirection === 'desc' ? -1 : 1;
  
  return function (a, b) {
      var sortStatus = 0;
      if (a[property] < b[property]) {
          sortStatus = -1;
      } else if (a[property] > b[property]) {
          sortStatus = 1;
      }

      return sortStatus * sortOrder;
  };
}

This may not work too well for dates.

Usage

var sortedResults = list.Results.sort(sortByProperty("ModifiedDate"));

for (var i = 0; i < sortedResults.length ; i++ ) {