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.
So a different problem presented itself awhile ago, and I thought I would share the solution.
How do I do a DISTINCT (a.k.a. unique) on multiple fields inside of a GlideRecord?
As you are aware a GlideRecord does what amounts to a SQL "SELECT * FROM...". So the problem was how do you do something like the following:
SELECT DISTINCT(*) FROM incident WHERE active=1
Well, you can't exactly, but you can come close:
var incidentRecords = new GlideRecord('incident');
incidentRecords.addNotNullQuery('cmdb_ci');
incidentRecords.addNotNullQuery('assigned_to');
incidentRecords.addActiveQuery();
incidentRecords.setLimit(1000); // we want enough to get some dups
incidentRecords.query();
gs.info('---> Length of resultset before dedup: ' + incidentRecords.getRowCount());
var incidentList = [];
while (incidentRecords.next()) {
// combine the fields into a single record and push onto the one-dimensional array
var key = gs.getMessage('{0}|{1}',
[incidentRecords.cmdb_ci.getDisplayValue(),
incidentRecords.assigned_to.getDisplayValue()]);
incidentList.push(key);
}
gs.info('---> Length of result before dedup: ' + incidentList.length);
// now we can run the distinct
incidentList = new ArrayUtil().unique(incidentList);
gs.info('---> Length of result after dedup: ' + incidentList.length);
// we have the result set with all dups removed and can now break it apart to do work on it.
for (var i=0; i < incidentList.length; i++) {
// now you can reconstitute the fields
var incidentSplit = incidentList[i].split('|');
var cmdb_ci = incidentSplit[0];
var assigned_to = incidentSplit[1];
// do more work
gs.info('---> \n\t- cmdb: {0}\n\t- assigned_to: {1}',
[cmdb_ci, assigned_to]);
}
Running this in Scripts - Background gives me (on my PDI) the following result:
As you can see this removes three duplicates that trigger on the "key" value and reduces the repetition I might have to deal with (a.k.a. performance issue) later in my script. I don't want to re-execute duplicates!
I hash this out in more detail in an article I wrote here: ServiceNow Admin 101: You Too Can Do DISTINCT Queries Using GlideRecord
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 08-23-2015 02:48 PM
I updated the code and brought the article into alignment with my new formatting standard.
2 Comments
