NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE
Assumes good intermediate level knowledge and/or familiarity of Scripting in ServiceNow.
I teach several different ServiceNow classes, and, invariably, I get a lot of questions about how to do DISTINCT queries. De-duplicating data in a result set is important, and there is no feature in GlideRecord that covers this purpose. So, the big question is: I can do a DISTINCT in SQL, but I can't do one with GlideRecord? What gives?
GlideRecords are designed to bring back all fields for each record. SQL has the flexibility to bring back one or more fields and to make any one of those fields unique.
For example, in SQL a simple query could be written thus:
SELECT DISTINCT caller_id
FROM incident
WHERE active = true
AND caller_id IS NOT NULL
ORDER BY caller_id
This would produce a list of unique caller sys_id's that are active and that are not empty from the Incident table.
In this article, I will show a few methods I use to accomplish this using GlideRecord.
Starting with the SQL example, let's render the query into a GlideRecord script.
var incidents = new GlideRecord('incident');
incidents.addQuery('active', true);
incidents.addNotNullQuery('caller_id.name');
incidents.orderBy('caller_id.name');
incidents.query();
gs.info('---> ' + incidents.getRowCount());
while(incidents.next()) {
gs.info('---> ' + incidents.caller_id.name + ''); // get the real english name
}
When run from Scripts — Background, this produces the following list (for me 194 records):
You can see the need for some sort of method to get these down to one entry per name. By introducing an array in which to store the names, we can make use of the ArrayUtil library in ServiceNow. ArrayUtil contains several really great array utilities. Check it out here.
One of these array utilities is .unique(..), which allows us to take a simple, one-dimensional array and remove all the duplicate entries.
So, revisiting our original example, the .unique(..) array utility reduces the original list by removing the duplicates.
var incidents = new GlideRecord('incident');
incidents.addQuery('active', true);
incidents.addNotNullQuery('caller_id.name');
incidents.orderBy('caller_id.name');
incidents.query();
gs.info('---> ' + incidents.getRowCount());
var callerIdList = [];
while(incidents.next()) {
callerIdList.push(incidents.caller_id.name.trim());
}
var arrayUtil = new ArrayUtil();
callerIdList = arrayUtil.unique(callerIdList);
gs.info('---> ' + callerIdList.length);
for (var i = 0; i < callerIdList.length; i++) {
gs.info('---> ' + callerIdList[i]);
}
So, I started with 194, but with this code doing the .unique produces 58 records. So many duplicates!
You now have your DISTINCT GlideRecord query. All done, right? Well, no. Actually, this solution is only good for simple, one-dimensional arrays. So what if you want to get a unique list based on several values in the record?
Let's say we want the caller id name AND category to be unique. If we go back to the original query and expand it a little, you'll see the problem.
var incidents = new GlideRecord('incident');
incidents.addQuery('active', true);
incidents.addNotNullQuery('caller_id.name');
incidents.orderBy('caller_id.name');
incidents.query();
gs.info('---> ' + incidents.getRowCount());
while(incidents.next()) {
gs.info('---> ' + incidents.caller_id.name.trim() + ' - ' + incidents.category.trim());
}
So, we are back to 194 records (no .unique so not unexpected):
Turns out there is a pretty easy way to clean this up: create a "key" field out of the various fields you want de-dup'd. This key is created simply by concatenating two (or more) of the fields together with a separator like the bar ("|"), caret ("^") or something that would not normally be used in a description or name. I would avoid ampersands, commas, hyphens, etc. as they can appear in names and descriptions. You want something that, when you bust it apart, will give you back exactly what you started with. So, how does it work?
- In the while concatenate your two fields together with your separator of choice.
- Push the key onto your array.
- Do the unique against the array.
This creates a unique list that you can bust apart using the .split function against the bar to retrieve your values:
var incidents = new GlideRecord('incident');
incidents.addQuery('active', true);
incidents.addNotNullQuery('caller_id.name');
incidents.orderBy('caller_id.name');
incidents.orderBy('category');
incidents.query();
gs.info('---> ' + incidents.getRowCount());
var callerIdList = [];
while(incidents.next()) {
var key = incidents.caller_id.name.trim() + '|' + incidents.category.trim();
callerIdList.push(key);
}
var arrayUtil = new ArrayUtil();
callerIdList = arrayUtil.unique(callerIdList);
gs.info('---> ' + callerIdList.length);
for (var i = 0; i < callerIdList.length; i++) {
var callerIdListSplit = callerIdList[i].split('|');
var callerId = callerIdListSplit[0];
var category = callerIdListSplit[1];
gs.info('---> ' + callerId + ' - ' + category);
}
Starting with 194 we end up with 95. Pretty good.
You can expand this to include as many fields as you want.
So now we have pushed our ArrayUtil unique function to its limit. Well, what if you want to bring along values that you don't want uniqued, but would like made available? You have to move into the realm of objects to accomplish this, as it requires a different way of finding and removing duplicates. You don't want all the fields, just specific ones. Further, you want to remove any duplicates based on one or more of those fields, and you want to bring along fields that will give you information and won't be used in the duplication check.
The best mechanism for this is an Object. You need to place all of your desired fields from a record into an object and then store that object onto an array. The final result is a list of objects.
I didn't want to have to reinvent the wheel, so I fished around on the web and found several examples that gave me an idea of what I wanted: A function that would remove duplicated entries from a list of objects. The best examples used the JavaScript array function "splice" to remove an array value.
Using the key field idea from the previous example allows us to "tag" each object with a value. That allows us to do a DISTINCT and bring along extra fields too.
So here is the process for de-duping an object array:
- In the while concatenate your two fields together with your separator of choice (I used the bar and stored it in a field named "key" in the object).
- Push the object onto the array.
- With the new unique function, loop through all of the records in the array. Using each object's key field, compare against all of the other records in the array and remove any duplicates (adjusting the array size down each time one is removed).
- Loop through what is left of the original array and you will see that all of the duplicates are gone and you have your extra fields too.
var incidents = new GlideRecord('incident');
incidents.addQuery('active', true);
incidents.addNotNullQuery('caller_id.name');
incidents.orderBy('caller_id.name');
incidents.orderBy('category');
incidents.query();
gs.info('---> Before unique: ' + incidents.getRowCount());
var callerIdList = [];
while(incidents.next()) {
var incident = {};
incident.caller_id = incidents.caller_id.name.trim();
incident.category = incidents.category + '';
incident.priority = incidents.priority.getDisplayValue();
incident.description = incidents.short_description + '';
incident.key = incident.caller_id + '|' + incident.category + '|' + incident.priority;
callerIdList.push(incident);
}
callerIdList = uniqueObjectList(callerIdList);
gs.info('---> After unique: ' + callerIdList.length);
for (var caller in callerIdList) {
var callerId = callerIdList[caller].caller_id;
var category = callerIdList[caller].category;
var priority = callerIdList[caller].priority;
var description = callerIdList[caller].description;
gs.info(callerId + ' - ' + category + ' - ' + priority + ' - ' + description);
}
function uniqueObjectList(callerList) {
for( var i = 0; i < callerList.length; i++){
for( var j = i + 1; j < callerList.length; j++){
if( callerList[j].key == callerList[i].key ) {
callerList.splice(j, 1); // delete the duplicate
--j; // reduce the array length by one
}
}
}
return callerList;
}
In the code we populated an object array. We needed to deduplicate that array. The uniqueObjectList function does just that utilizing the "key" field. We had to add that to our object to provide the way for deduplication:
You can add as many fields as you want to the key, but usually it only takes a couple of fields to get the desired results.
That is pretty much the sum of the techniques you will find me using to do a DISTINCT with GlideRecord. Now it's your turn!
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS. AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.
THIS IS THE RE-PUBLICATION OF MY ARTICLE FROM November 11th, 2014 ON THE CLOUDSHERPAS SERVICENOW ADMIN 101 BLOG.
EVEN THOUGH THIS WAS ORIGINALLY CREATED FOR THE EUREKA RELEASE I THOUGHT THAT IT WAS STILL RELEVANT AS THE TECHNIQUES USED HERE WILL STILL WORK IN THE LATEST RELEASE.
Originally published on: 05-17-2016 12:19 PM
I updated the code and brought the article into alignment with my new formatting standard.
5 Comments
