Getting Distinct Records

Every once in a while, I run into a situation where I need a list of distinct values from a table. The latest situation I ran into for this, I needed to get a distinct list of every user that had an open Project Task.

The easiest way to do this of course is to go to a list view, and sort by the field you care about. For example:

https://demo013.service-now.com/nav_to.do?uri=pm_project_task_list.do?sysparm_query=active=trueGROUPBYassigned_to

Project Tasks by User

Grouping Project Tasks by Assigned To

 

For my purposes though, I wanted to send out scripted email notifications to everyone that had an open task. As a result, I needed this information available to me through scripting, which caused me to look for a different solution.

The answer came in the form of “GlideAggregate”. This is a Service-Now custom JavaScript object that functions very similar to the popular “GlideRecord”, with a few key differences.

GlideAggregate is built to provide the functionality of gathering basic statistics — counts, averages, maximums, and minimums. However, by employing a little creativity, we can use this power to grab all the unique values of a certain field on a table.

Consider this example below that I recently used:

var ga = new GlideAggregate(“pm_project_task”);
ga.addQuery(“active”, “true”);
ga.addQuery(“assigned_to”, “!=“, “”);
ga.addAggregate(“count”);
ga.orderByAggregate(“count”);
ga.groupBy(“assigned_to”);
ga.query();

while(ga.next()) {
     var taskCount = ga.getAggregate(“count”);
     gs.print(“User: “+ga.assigned_to.name+” - Number of Project Tasks: “+taskCount);
}

The above code does the following things:

  1. Counts how many project tasks are assigned to each user.
  2. Filters out project tasks that aren’t assigned to anyone.
  3. Prints out to the screen the user’s name, and how many project tasks are assigned to them.

Running that code as a Background Script, gives me the following output:

Background Script Output

Background Script Output

Hopefully you find this useful! In the near future I’ll be posting a few other related “How-Tos”:

  1. Using Background Scripts
  2. Dynamic Weekly Digest Emails
  3. More GlideAggregate Examples


Categories: Developers, Scripting Tips

Tags: , , , ,

4 replies

  1. Hi Garrett,
    This is very useful, thanks! Have a quick question.

    Say, I need to pull up a list of incidents that were opened today, on an hourly basis i.e. x incidents opened between 1:00 AM – 02:00 AM, etc, addTrend(‘field_name’, ‘hour’) gets me the results. The question is how do I get the hour slot in the result set. Tried ga.getAggregate(‘trend’, ‘hour’) but it did not work.

    I’d appreciate any pointers.

    incidentsPerHour();

    function incidentsPerHour() {
    var ga = new GlideAggregate(‘incident’);
    var query=”sys_created_onBETWEENjavascript:gs.dateGenerate(‘2014-03-30′,’00:00:00’)@javascript:gs.dateGenerate(‘2014-03-30’,’23:59:59’)”;

    ga.addEncodedQuery(query);
    ga.addAggregate(‘COUNT’);
    ga.groupBy(‘mailbox’);
    ga.query();
    var arr = [];

    while(ga.next()){
    arr.push(“trendField = ” + ga.getAggregate(‘COUNT’) + ‘ mailbox=‘ + ga.mailbox);
    }
    gs.print(“test”);
    gs.print(arr.join(‘\n’));
    }

    Thanks,
    Teja.

    Like

    • Hi Teja,

      Very intriguing problem there. I’ve never used the ‘addTrend’ function in the past — so I don’t have an outright solution for you, but I can at least give a hint:

      After playing around with it for about 30 minutes, I’ve managed to determine that if I limit my query to a single day (like you have above), and then I use “addTrend(‘sys_created_on’, ‘hour’)”, each time I use “ga.next()” — I get the count total for the next hour, and using “getValue”, I can figure out what hour it’s reporting.

      The magic word was “timeref” — I figured this out by doing a “getEncodedQuery()”, and noticing that it had “order_by=timeref”. Note that you *must* use “getValue” to grab timeref. Otherwise it will give you undefined.

      Example:


      incidentsPerHour();

      function incidentsPerHour() {
      var ga = new GlideAggregate('incident');
      var query="sys_created_onBETWEENjavascript:gs.dateGenerate('2014-03-30','00:00:00')@javascript:gs.dateGenerate('2014-03-30','23:59:59')";

      ga.addEncodedQuery(query);
      ga.addAggregate('COUNT');
      ga.groupBy('mailbox');
      ga.addTrend('sys_created_on', 'hour');
      ga.query();
      var arr = [];

      while(ga.next()){
      gs.print("trendField = " + ga.getAggregate('COUNT') + ' - mailbox=' + ga.mailbox + " - hour: "+ga.getValue("timeref"));
      }
      }

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: