How to Filter Reference Field Based on Other Table's Field?

[E555]

Hi Community,

I'm trying to filter the options in a reference field (target_user) in a ServiceNow catalog item. Here are the requirements and what I've tried so far:

Requirements:

1. The sys_user.object_id must not be empty.
2. The sys_user must be active (Active = true).
3. Users with u_status set to 1, 2, 3, or 4 in the u_history_table must be excluded. (u_status is a choice field with 4 options, and a record is created for each user with one column referring to the sys_user table).

What I Have Tried:

1. Script Include:

   • Created a Script Include named Filter_user_bu_Status to retrieve users with u_status set to 1, 2, 3, or 4.
   • The Script Include works correctly in the Log and retrieves the correct user`s sys_id to be excluded.

var Filter_user_bu_Status = Class.create();
Filter_user_bu_Status.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getExcludedUsers: function() {
        var excludedUserList = [];
        var gr = new GlideRecord('u_history_table');
        
        gr.addQuery('u_status', 'IN', '1,2,3,4');
        gr.query();
        while (gr.next()) {
            var userSysId = gr.u_user.toString();
            if (userSysId) {
                excludedUserList.push(userSysId);
            }
        }

        return new JSON().encode(excludedUserList);
    }
});

• Reference Qualifier:

  • Used a Reference Qualifier to filter out the users based on the conditions.
  • Attempted to use both g_form.setDependentFieldFilter and a script directly in the Reference Qualifier, but neither approach worked as expected.

  Reference Qualifier Script:

(function() {
    var excludedUsers = [];
    var ga = new GlideAjax('Filter_user_bu_Status');
    ga.addParam('sysparm_name', 'getExcludedUsers');
    var response = ga.getXMLWait();  // Use synchronous call to get the response
    var answer = response.documentElement.getAttribute("answer");
    excludedUsers = JSON.parse(answer);

    var filterString = 'active=true^object_idISNOTEMPTY';
    if (excludedUsers.length > 0) {
        filterString += '^sys_idNOT IN' + excludedUsers.join(',');
    }

    return filterString;
})();

•  

  Client Script:

  • Also tried using a Client Script to filter the options, but it didn't work as expected either.

function onLoad() {
    var ga = new GlideAjax('Filter_user_bu_Status');
    ga.addParam('sysparm_name', 'getExcludedUsers');
    ga.getXMLAnswer(function(response) {
        var excludedUserList = JSON.parse(response);
        g_form.clearOptions('target_user'); // Clear existing options
        g_form.addOption('target_user', '', '--Select a user--'); // Add default option

        var gr = new GlideRecord('sys_user');
        gr.addQuery('active', true);  // Only active users
        gr.addQuery('object_id', '!=', '');  // Users with non-empty object_id
        gr.query();
        var addedUsers = [];  // For debug
        while (gr.next()) {
            var userId = gr.sys_id.toString();
            if (!excludedUserList.includes(userId)) {
                g_form.addOption('target_user', userId, gr.name.toString());
                addedUsers.push(userId);  // For debug
            } else {
                g_form.addInfoMessage('Excluded user not added: ' + userId); // For debug
            }
        }

        g_form.addInfoMessage('Excluded users: ' + JSON.stringify(excludedUserList)); // For debug
        g_form.addInfoMessage('Added users: ' + JSON.stringify(addedUsers)); // For debug
    });
}
​

Table Structure: The u_history_table has the following columns:

• u_user: A reference to the sys_user table.
• u_status: A choice field with values 1, 2, 3, and 4.

A record is created in u_history_table for each user (u_user).

Issues:

• The Reference Qualifier is not filtering the users as expected.
• All users are still being displayed in the target_user field despite the filters.

Request:

• I need new ideas to achieve the requirements or help to fix the scripts I have tried to meet the requirements.
• Any suggestions or best practices for achieving this filtering in ServiceNow.

Thank you in advance for your help!

[Jake Sadler]

Hi @E555 ,

The reference qualifier should do this.

You are treating it as a client side but it is a server side script.

Create a server side script include and use this script 

 var excludedUserList = [];
        var gr = new GlideRecord('u_history_table');
        
        gr.addQuery('u_status', 'IN', '1,2,3,4');
        gr.query();
        while (gr.next()) {
            var userSysId = gr.u_user.,sys_id.toString();
            if (userSysId) {
                excludedUserList.push(userSysId);
            }
        }
var query = "sys_idNOTIN"+excludedUserList.toString();
return query;

In the ref qual call the script include new global.scriotIncludeName().functionName(current);

Use current dot walking to push any values you want to the script include

[E555]

Thank you for your response.

I tried it, but the filtering is still not working for the "target_user" options. The API Name for the Script Include is correctly set to "global.Filter_user_bu_Status".

Additionally, I received the following log waring:
"Invalid query detected, please check logs for details [Unknown field null in table sys_user]".

Script Include:

var Filter_user_bu_Status = Class.create();
Filter_user_bu_Status.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getExcludedUsers: function() {
        var excludedUserList = [];
        var gr = new GlideRecord('u_history_table');
        
        gr.addQuery('u_status', 'IN', '1,2,3,4');
        gr.query();
        while (gr.next()) {
            var userSysId = gr.u_user.sys_id.toString();
            if (userSysId) {
                excludedUserList.push(userSysId);
            }
        }

        var query = "sys_idNOTIN" + excludedUserList.toString();
        return query;
    }
});

Reference Qualifier:

new global.Filter_user_bu_Status().getExcludedUsers(current);

I hope this helps to clarify the issue. Please let me know if there are any other suggestions or corrections. Thank you!

[Jake Sadler]

@E555 The query NOTIN is invalid you will need to do this:

var query;

for(var i = 0; i <excludedUserList.length; i++){

query += "sys_id!="+ excludedUserList[i] +"^"

}