Data Certification and Scripted Dynamic Filter Setup

[Fay1]

Hi,

I am building a way for users to certify their mobile number is correct for contact purposes, for this I am using Data Certification. Users are to certify both their business and personal numbers every 6 months.

The Certification Filter is pointing to the HR Profile table as this is where the data is held and I am using a dynamic filter to select the appropriate users, based on the sys_user record linked to the HR Profile record.

My Dynamic Filter points to a script include within the Global scope that passes the certification schedule sys ID so that the script include can be used for both the business mobile and personal mobile certification. My dynamic filter references the User [sys_user] table. I have included my script include code below.

When I run this as a background script it works fine and returns the result that I am expecting at each stage. However when run using the filter and reviewing the filter condition count, it seems to only be running the first part of the query ('u_worker_type=Permanent^u_leave_type=^employment_end_dateISEMPTY';). Currently I have 13 HR profiles setup, 11 of those meet the permanent, not on leave criteria and out of those 7 should be returned for cert_task creation as 4 already have active tasks/closed tasks within the last 6 months. I am currently getting 11 back on the filter condition count.

I have managed to isolate the issue to splitList[i] within the for loop. If I hardcode a sys id (as shown) it works fine, if I pass in a comma separated string to loop through it works fine. I have tried to log out values as part of the filter run, but I cannot find anything in the logs. I have also reviewed the user system logs, but no errors are being generated from my session.

Dynamic Filter Script call:

new nwgRecertifyUserList().recertifyNumber(gs.getProperty('nwg.datacertification.certifybusinessmobile'));

Script Include:

recertifyNumber: function(schedule) {
/*We need to identify the users who currently have an open certification task or completed their certification within the last 6 months. We use this to discount them from the query.
Query: Schedule reference and State = Work In Progress (2) | New Query: Schedule reference and Closed at after 6 months ago and State = Closed Complete (3)
Certify Task is the where the individual tasks for completion are stored*/

var certifyTask = new GlideRecord('cert_task');
var users = [];
certifyTask.addEncodedQuery('cert_schedule=' + schedule + '^state=2^NQcert_schedule=' + schedule + '^state=3^closed_atRELATIVEGT@month@ago@6');
certifyTask.query();
while (certifyTask.next()) {
users.push(certifyTask.assigned_to.toString());
}

//We then need to take this list and change it around on the hr profile table to get all the users that we need to create certification tasks for
var answer = [];
var setLimit = gs.getProperty('nwg.datacertification.certifyQueryLimit');
var setString = users.toString();
var splitList = setString.split(",");
**Note I have tried this also as var setString = users.toString().split(","); **

var getUsers = new GlideRecord('sn_hr_core_profile');
var query = 'u_worker_type=Permanent^u_leave_type=^employment_end_dateISEMPTY'; //Generic query

//We dont want the users who already have active cert tasks or who completed this less than 6 months ago
for (var i = 0; i < splitList.length; i++) {
//query = query + '^user!=254a43ecdb051f448efe9eaadb9619b2'; //THIS WORKS, STRING IS THE ISSUE
query += '^user!=' + splitList[i];
}

getUsers.addEncodedQuery(query);

//Should limit be set, this is only for the initial runs to prevent lots of cert_tasks being created, when no longer needed property to be set to false
if (setLimit == 'true') {
getUsers.setLimit(5000);
}

getUsers.query();
while (getUsers.next()) {
answer.push(getUsers.user.toString());
}

return answer; //Answer will be a list of the users who are due a review which will then filter the HR profile as part of the Certification Filter
},

I am not getting any results back on my Data Certification forced run either even though the filter shows 11 records match my criteria, so I do not think I have set this up correctly. Any advice or insight would be appreciated on the dynamic filter setup and data certification setup for my scenario.

Please let me know if any further information would be helpful.

Many thanks.

Fay.

[Adam Stout]

Why are you creating an array just to make it a string then split it again?  Instead of looping, I would just use 

"userNOT IN" + users;

In any case, add some debugging.  Especially something like this to confirm the query:

gs.info(getUsers.getEncodedQuery());

On a side note, it is difficult to read all this unformatted code, the code formatter in the text editor can make it easier for others to read.

[Fay1]

Hi Adam,

Thanks for taking the time to respond. I tried 'NOT IN' as my first option and this didn't work, so I went down the route of looping, also tried (as per ServiceNow documentation):

getUsers.addQuery('user', 'NOT IN', users);

I have also tried logging the results out, however no log entries are generated. ServiceNow are investigating the case for this issue and they have also confirmed that log statements are not generating here.

I have moved the code snippet to the code formatter, hopefully this assists with readability.

Thanks.