var sQuery = 'sys_created_onONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()';
checkDupl('question_answer', 'question', 'table_sys_id', sQuery);
//checkDupl('question_answer', 'question', 'table_name', sQuery);
function checkDupl(table, fieldParam1, fieldParam2, customQuery) {
var ga = new GlideAggregate(table);
ga.addAggregate('COUNT', fieldParam1);
if (customQuery) {
ga.addQuery(customQuery);
}
ga.groupBy(fieldParam1);
ga.addHaving('COUNT', '>', 1);
ga.query();
var dupls = [];
while (ga.next()) {
if (!gs.nil(ga.getDisplayValue(fieldParam1))) {
//dupls.push('checking question:::: ' + ga.getDisplayValue(fieldParam1));
var ga2 = new GlideAggregate(table);
ga2.addAggregate('COUNT', fieldParam2);
if (customQuery) {
ga2.addQuery(customQuery);
}
ga2.addQuery('question', ga.getValue(fieldParam1));
ga2.groupBy(fieldParam2);
ga2.addHaving('COUNT', '>', 1);
ga2.query();
while (ga2.next()) {
dupls.push("The following records are duplicate on " + fieldParam2);
var DuplLine2 = ga2.getValue(fieldParam2) + " " + ga2.getAggregate('COUNT', fieldParam2) + " => " + ga.getDisplayValue(fieldParam1);
dupls.push(DuplLine2);
}
}
//gs.log(dupls,'duplWidget_arr');
}
return dupls;
}
The example demonstrates a practical way to identify duplicate records in ServiceNow by combining GlideAggregate, COUNT, groupBy, and addHaving. The additional filtering with a date-based encoded query makes the duplicate check more targeted instead of processing the entire table.
ReplyDeleteThe nested aggregate query is particularly useful because the first query identifies repeated values, while the second checks another field within those matching records. This kind of server-side scripting also shows how JavaScript can be used to build reusable data-validation logic, making a Javascript Online Course relevant for understanding the syntax and query patterns involved.
Another useful aspect is the reusable checkDupl() function, where the table, fields, and custom query are passed as parameters rather than hard-coded throughout the script. Developers working with ServiceNow can apply similar JavaScript techniques through Javascript Online Training when creating scripts for duplicate detection and other administrative data checks.
ReplyDelete