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

[muktha1]

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

[muktha1]

Hi Ankur,

It works with both your script as well as Vikram's script. Thank you very much for your help.

Best Regards

Muktha

[Ankur Bawiskar]

Hi,

Glad to help.

Kindly mark the answer correct based on response given quickly and accurately.

Regards
Ankur

[Vikram19]

Hi Ankur,

Correct me if I'm wrong.

This might not be the best way to get users.

As user can have multiple roles and by removing itil condition the query will return multiple(redundant sys_id in query) users even though we will see only one in the reference field that is long query.

This is what i suggest would be best

the aggregate using user so we will query for a shorter time and query won't have redundant sys_id of user with multiple users.

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();
	},

Thanks,

Vikram

[Ankur Bawiskar]

Looks good.

[Vikram19 · Accepted solution]

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