Quick and Dirty Glide Aggregate Flow Designer action

[kevclark]

I just wanted to post a quick and dirty GlideAggregate Flow Designer Action I threw together.

The goal:

To run a GlideAggregate query against a table to retrieve all the unique values that are found for a given field value.  Say I have a bunch of records, and I want to find out how many unique assignees I might have, so that I can send a bunch of notifications to them, I would feed this action the name of the table, an Encoded Query, and the field I'm interested in seeing the values for.

The Execution:

Start by creating a new action.  I've called mine "FlowAggregate".

For inputs, I want a Table Name (type table name), a Field to retrieve (Field List or String), and a non-mandatory "Encoded Query" string: 

Once I have the inputs, then I want to pass them through a Glide Aggregate query, and produce a stringified array:

(function execute(inputs, outputs) {
  
  var tableName = inputs.table_name;
  var fieldName = inputs.field_name;
  var encQuery = inputs.encoded_query;
  var resultArr = [];

  var count = new GlideAggregate(tableName);

  if (encQuery != '') {
    count.addEncodedQuery(encQuery);
  }
  
  count.addAggregate('COUNT',fieldName);
  count.query();
  
  while(count.next()){
    var field = count[fieldName];
    var fieldCount = count.getAggregate('COUNT',fieldName);
    gs.log("there are currently "+ fieldCount +" records with a "+fieldName+" of "+ field);
    resultArr.push(field.getValue());
  }
  
  outputs.value_array = resultArr.toString();
  
})(inputs, outputs);

(NOTE: the "getValue()" against the resultsArr object may have some problems with application scopes.  You might be able to replace with "toString()" instead.)

I'll also need to make sure I declare that output of "value_array":

Finally, I need that action output:

With all this in place, I'm able to query a table, then get all the unique values for that query as a handy-dandy comma-separated string, against which I can perform additional Flow operations.

[kevclark]

Just FYI, if you're building this in an application scope like a good little dev, you might find that the "getValue()" in the original script has scoping problems.  I've been able to get around this by replacing with "toString()" instead.

[Sumit Pandey1]

Can I then do for-each on output(value_array) ?

[kevclark]

Hi Sumit, 

I've been able to use the value_array response to do a "look up records" action, where "sys_id is one of <value_array>" which is useful for creating a list of records based on sys_id.  Once the "look up records" action has completed, then it outputs an array of records which can be used in a for-each loop.

I'm not sure exactly what you'd need to do (if anything) to allow the comma-separated string to be converted to an array of objects available to a for-each loop.  There's probably a more clever output type that would allow this, but I tried some of the array objects and gave up - this was a quick and dirty solution, so there's probably a more elegant way to return a "loopable" output.

[Adam Stout]

We covered integrating analytics into your flows in a Performance Analytics Academy last November.  To build on this, check out the recording a deck from that session:  https://community.servicenow.com/community?id=community_blog&sys_id=6132b7afdbcc24103daa1ea66896198f

[carleen]

@kevclark, I was able to get the For-Each in my subsequent flow to work when I used an Array.String variable. I did try pushing to the outputs.value_array outright, but that did not work. Also, make sure that your final Action output is Array.String type as well.

I might have just been lucky, but this seems to work in my Orlando instance.

(function execute(inputs, outputs) {

    var tempArray = []; //<-- Setup the local variable
    var ga = new GlideAggregate(inputs.table_name);
    if (inputs.encoded_query != '') {
        ga.addEncodedQuery(inputs.encoded_query);
    }
    ga.addAggregate('COUNT', inputs.field_name);
    ga.query();
    while (ga.next()) {
        tempArray.push(ga[inputs.field_name] + ''); //<-- Push to the local variable
    }

    outputs.value_array = tempArray; //<-- Copy the local variable to the output

})(inputs, outputs);

[kevclark]

Thanks for the comment @carleen .  That looks solid.  I didn't spend too much time on the format of the output because I was passing it back to a GlideRecord query (sys ID is one of ... csv IDs), but when I revisit I'll look at making the output type a bit more correct.

I find Flow Designer to be a bit tricky when it comes to Objects, Arrays and JSON inputs and outputs, so I tend to just stringify/parse my JSON once I'm in a script action.

[Renita]

Kevin,

I really like this!  If I wanted to add a filter going into this action for "state", how hard would that be?  I tried doing it myself, but I am getting a syntax error. StateName is defined the same way as fieldName.  Any advice?

(function execute(inputs, outputs) {

var tableName = inputs.table_name;

var fieldName = inputs.field_name;

var stateName = inputs.state_name;

var encQuery = inputs.encoded_query;

var resultArr = [];

var count = new GlideAggregate(tableName);

count.addQuery('stateINstateName');

if (encQuery != '') {

count.addEncodedQuery(encQuery);

}

count.addAggregate('COUNT',fieldName);

count.query();

while(count.next()){

var field = count[fieldName];

var fieldCount = count.getAggregate('COUNT',fieldName);

gs.log("there are currently "+ fieldCount +" records with a "+fieldName+" of "+ field);

resultArr.push(field.toString());

}

outputs.value_array = resultArr.toString();

})(inputs, outputs);