How to display only users with roles in a reference field ?

muktha1 · ‎08-16-2021

Hi all,

Can anyone please suggest how to display only the users with roles in a reference field pointing to User table? I find a field called Roles in User table. I tried using the condition "Active is true and Roles is anything" but I feel that some users with roles are listed and some users with roles are not listed. Users with admin role are also not listed. I find only three options (is, is not , is anything) in the filter used. Can anyone please help out ? I was looking at some community threads but I could not get it working.

Thanks

Muktha

Vikram19 · ‎08-17-2021

Hi,

Create a script include as below, name it getUserUtil.

Script Include:

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

	getUserWIthoutRole: function(){
		var userWithoutRole = [];
		var grUser = new GlideRecord('sys_user');
		grUser.addEncodedQuery('active=true');
		grUser.query();
		while(grUser.next()){
			var grRole = new GlideRecord('sys_user_has_role');
			grRole.addQuery("user",grUser.sys_id);
			grRole.query();
			if(!grRole.next())
				userWithoutRole.push(grUser.getValue('sys_id'));
		}
		return 'sys_idIN' + userWithoutRole.toString();
	},

	type: 'getUserUtil'
};

Now update dictionary reference qualifier of reference field to advance and put below code in Reference Qul

javascript: new getUserUtil().getUserWIthoutRole();

Dictionary update:

If my answer replied to your question please mark an appropriate response as correct so that the question will appear as resolved for other users who may have a similar question in the future.

Thanks,

Vikram

View solution in original post

muktha1 · ‎08-17-2021

Hi Vikram,

I want to show only the users who have roles in the reference field. Users without any role should not appear in the list.

Thanks

Vikram19 · ‎08-17-2021

Hi,

Please replace script include code with below one

It is queried to take only active users who have any role

Script includes:

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

	getUserWithRole: function(){
		var myQuery = 'user.active=true'; // Add encoded query here
		var userWithRole = [];
		var gr = new GlideAggregate("sys_user_has_role");
		gr.addEncodedQuery(myQuery);
		gr.addAggregate('count'); //Count aggregate 
		gr.orderByAggregate('count'); //Count aggregate ordering
		gr.groupBy('user');
		gr.query();
		while(gr.next()){
			userWithRole.push(gr.getValue('user'));
		}

		return 'sys_idIN' + userWithRole.toString();
	},

	type: 'getUserUtil'
};

Dictionary with below

javascript: new getUserUtil().getUserWithRole();

Mark as Correct & Helpful, if applicable.

Thanks,

Vikram

muktha1 · ‎08-17-2021

Hi Vikram,

It is working fine. Thanks a lot

Thanks