Show users that are not part of specific group in reference field in service catalog

[Sowmya549]

Hi All, 

i have a reference field where i need to show all users list except users from particular Group.

if we use grmember and set reference qualifier to group is not xyz group then its showing all the members multiple time from different groups.

can someone help on it. Thanks in Advance.

[swathisarang98 · Accepted solution]

Hi @Sowmya549 ,

You can create a reference type field and reference it to sys_user table, and in advanced reference qualifier call the script include,

javascript: new global.getNotGroupMem().getUsers();

Here i have used script include api name

Script include:

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

	getUsers: function(){
		var gr = new GlideRecord('sys_user_grmember');//group member table
		gr.addQuery('group.name','Database');//query your group if there are are more than one group change qury according or add encoded query
		var arr=[];
		gr.query();
		while (gr.next()){
			arr.push(gr.getValue('user')); //getting the sys id of all the user
		}
		gs.info('arr ' + arr);

		var user = new GlideRecord('sys_user');//gliderecord to user table
		user.addEncodedQuery('sys_idNOT IN' + arr); //query without group member 
		var userArr =[];
		user.query();
		while(user.next()){
			userArr.push(user.getValue('sys_id'));//get the other users apart of from group member sysid 
		}
		return 'sys_idIN' + userArr; //return the array
	},

    type: 'getNotGroupMem'
};

Please mark this comment as Correct Answer/Helpful if it helped you.

Regards,

Swathi Sarang

[swathisarang98]

Thanks for marking my answer as helpful. If it helped you in any way please accept the solution so that it will be beneficial to the future readers with the same query.

Regards,

Swathi Sarang

[Ram050670]

This Solution is workable! but there are some performance issues with this, i did slight modification which resolves the performance issue.

//Advanced Reference Qualifier
javascript: new Script_include_name().function_name();
javascript: new UserList().getUsers();

//Script include Function
getUsers: function() {
        var arr = [];
        var encQry = '';
        var Grp = gs.getProperty('Group sys_id'); //create a system property to add sys id of the group
        var gr = new GlideRecord('sys_user_grmember');
        gr.addQuery('group', Grp);
        gr.query();
        while (gr.next()) {
            arr.push(gr.getValue('user'));
        }
        for (var i = 0; i < arr.length; i++) {
            if (encQry == '') {
                encQry = 'sys_id!=' + arr[i].toString();
            } else {
                encQry = encQry + '^sys_id!=' + arr[i].toString();
            }
        }
        encQry = encQry + '^active=true^u_employee_type=Employee'; // if any other encoded query's needed.
        return encQry;
    },