Show or hide the names in a sys_user reference field based on group membership

[alexlazo]

I'm trying to create a reference qualifier or script that will allow me to filter out users in the sys_user table by the group membership related list. So if they belong to a specific group, remove them from the available options in the reference field. I see its easy to do it by role but I can't seem to do it by group.

[Kyryl Petruk1]

Hi Alex,

You need to write a script include with a function like this:

filterByGroup: function(group_id) {
   var found_users = [];

   var gr = new GlideRecord("sys_user_grmember");
   gr.addQuery("group", group_id);
   gr.addNotNullQuery("user");
   gr.query();
   while (gr.next()) {
           found_users.push(gr.user.sys_id.toString());
   }
   var ref_qual = "sys_idIN" + found_users.join(",");
   return ref_qual;
}

Then call this script include function in the reference qualifier.

[alexlazo]

Hi Kyryl,

Thanks for your answer! Which elements in the script should I edit to identify the group to filter out? I get errors when i attempt to add the group information as I did below.

1. filterByGroup: function(1884e9a06fad9a009afb5965bd3ee496) {  
2.   var found_users = [];  
3.  
4.   var gr = new GlideRecord("sys_user_grmember");  
5.   gr.addQuery("Equity Partners", 1884e9a06fad9a009afb5965bd3ee496);  
6.   gr.addNotNullQuery("user");  
7.   gr.query();  
8.   while (gr.next()) {  
9.           found_users.push(gr.user.sys_id.toString());  
10.   }  
11.   var ref_qual = "sys_idIN" + found_users.join(",");  
12.   return ref_qual;  
13. }

What am I missing?

[Kyryl Petruk1 · Accepted solution]

Here is a description of how to script reference qualifiers, just use script include instead of business rule:

Reference Qualifiers - ServiceNow Wiki

Okay, so to show the full example, this is how it might look.

Script include:

var UserFilterUtils = Class.create();
UserFilterUtils.prototype = {
   initialize: function() {},

   filterByGroup: function(group_id) {
   //Will show only users who are members of the specified group
   var found_users = [];

   var gr = new GlideRecord("sys_user_grmember");
   gr.addQuery("group", group_id);
   gr.addNotNullQuery("user");
   gr.query();
   while (gr.next()) {
   found_users.push(gr.user.sys_id.toString());
   }
   var ref_qual = "sys_idIN" + found_users.join(",");
   return ref_qual;
   },

   filterOutByGroup: function(group_id) {
   //Will remove users of the specified group from the list
   var found_users = [];

   var gr = new GlideRecord("sys_user_grmember");
   gr.addQuery("group", group_id);
   gr.addNotNullQuery("user");
   gr.query();
   while (gr.next()) {
   found_users.push(gr.user.sys_id.toString());
   }
   var ref_qual = "sys_idNOT IN" + found_users.join(",");
   return ref_qual;
   },

   type: 'UserFilterUtils'
};

Reference qualifier on the user reference field:

javascript:(new UserFilterUtils()).filterOutByGroup("group_sys_id");

Or if you need to filter by the group that is selected on the form:

javascript:(new UserFilterUtils()).filterOutByGroup(current.field_on_the_form);

[rlehmann]

Thanks very much for this Kyryl.

I've implemented your suggested script include and reference qualifier that calls it.

Using the sys_id of one of the groups I'm looking to retrieve the members from, it does return all 3 members.

Unfortunately, it also returns one entry from my application CMDB, in this case "Outlook" with a sys_id of 1, but only for 1 out of the 8 group sys_ids I'm using in the reference qualifier(separate ones for each group field on the form).

I've gone thru and verified the scripts numerous times and do not see any errors.

Here is what firebug is showing:

Here is my reference qualifier:

javascript:(new UserFilterUtils()).filterByGroup('20479c176f897100f14d6d6eae3ee486');

I've also tried it with double quotes instead of single, but no difference.

We are on Fuji patch 11.

Any suggestions?

Thanks in advance for any assistance you can offer.

Cheers

Ron