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

Ankur Bawiskar · ‎08-16-2021

Hi,

then you can use script include based approach

dynamic ref qualifier

query sys_user_has_role with that role and get all the users

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Ankur Bawiskar · ‎08-17-2021

Hi,

example script

Advanced ref qualifier

javascript: getRoleUsers()

Script Include:

function getRoleUsers(){

	var arr = [];
	var gr = new GlideRecord("sys_user_has_role");
	gr.addQuery("role.name", "itil");
	gr.addQuery("user.active", true);
	gr.query();
	while(gr.next()) {
		arr.push(gr.getValue('user'));
	}
	return 'sys_idIN' + arr.toString();
}

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

muktha1 · ‎08-17-2021

Hi Ankur,

I want the reference field to show all the users with any role. The user should have one role at least whatever be the role. If I remove the line

gr.addQuery("role.name", "itil");

and execute the code it is not showing anything.

Best Regards

Muktha

Ankur Bawiskar · ‎08-17-2021

Hi,

so if user has at least 1 role then show that user then do this

commenting that line should work

function getRoleUsers(){

   var arr = [];
   var gr = new GlideRecord("sys_user_has_role");
   gr.addQuery("user.active", true);
   gr.query();
   while(gr.next()) {
       arr.push(gr.getValue('user'));
   }
   return 'sys_idIN' + arr.toString();
}

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader