Filter by users registered in the HR profile by referencing sys_user

NaoyaT
Tera Expert

I created a script to reference sys_user in a catalog item using a variable of type "reference" to filter by users registered in HR profile, but it doesn't work.

 

■Variable
Reference:User[sys_user]
Use reference qualifier:Advanced

Reference qualifier

javascript:
var query;
var grmem = new GlideRecord('sn_hr_core_profile');
grmem.query();

var usrIDs = [];

while (grmem.next()) {
usrIDs.push(grmem.employee_number.toString());
}
query = 'user_nameIN' + usrIDs.join(',');
query;

When a request item is created, we want to use catalog client scripts and script includes to link and display the user with the user ID, so we reference sys_user instead of sn_hr_core_profile.

9件の返信9

Ankur Bawiskar
Tera Patron
Tera Patron

@NaoyaT 

use this and it ensures it queries only those HR profile records where user is not empty

 

javascript:
var query;
var grmem = new GlideRecord('sn_hr_core_profile');
grmem.addEncodedQuery('userISNOTEMPTY');
grmem.query();

var usrIDs = [];

while (grmem.next()) {
usrIDs.push(grmem.user.toString());
}
query = 'sys_idIN' + usrIDs.join(',');
query;

 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@NaoyaT 

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Tai Vu
Kilo Patron
Kilo Patron

Hi @NaoyaT 

Your script seems look fine but should be packaged into a function within a Script Include and then called in the reference qualifier.

One thing to consider, there might be a potential issue with using IN query in this case if you have a huge number of HR Profile records. I’ve encountered one issue before when trying to retrieve hundreds of thousands of records with a sys_idIN query. It takes too long to display choices (sometimes the transaction gets canceled).

An alternative approach to consider:

  • Use a reference variable that directly points to the HR Profile (sn_hr_core_profile) table.
  • Add a single-line text variable for the User Name, which auto-populates when the HR Profile is selected.

This method is purely configuration-based, no script required. And if you need any information from User table just dot-walk to the User field and get it.

 

Cheers,
Tai Vu

Debasis Pati
Tera Guru

Hello @NaoyaT ,

Please try the below script

// Create an array to store the sys_ids of users with HR profiles
var usrIDs = [];

// Query the HR profiles to get associated user sys_ids
var grmem = new GlideRecord('sn_hr_core_profile');
grmem.query();

while (grmem.next()) {
if (grmem.user) {
usrIDs.push(grmem.user.sys_id);
}
}

// Create a filter string using the sys_ids of the users
var query = 'sys_idIN' + usrIDs.join(',');

// Return the filter string for the reference field
query;

Please mark it helpful/correct if this helps you.

Regards,
Debasis