Filter by users registered in the HR profile by referencing sys_user

[NaoyaT]

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.

[Ankur Bawiskar]

@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.

[Ankur Bawiskar]

@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.

[Tai Vu]

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]

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