Reference Qualifier on a User Reference Field to show only Managers

[nicksoltis]

I am having trouble finding a way to limit a User Reference field to only show managers when selecting a user.

Below are fields on the "sys_user" table that would relate them to a Manager:

****All of the colored out values are user referenced names****

Let me know if this is possible in a simple JavaScript condition statement or if it needs to be done externally from the fields dictionary in a Script Include.

Thank You,

[Chuck Tomasi]

Hi Nicholas,

There's no built in way to say "I am a manager" on the sys_user table. That being said, you can easily add a true/false field (e.g. u_manager) and then go through your records. If any record has a manager field filled in, you can then check that linked account (in the manager field) and update it so say u_manager=true. This can be done with a business rule.

The trickier part is unchecking that box. If the manager field on any user record changes, you will need to check the previous value then count how many other records reference that previous user and update it accordingly. This again is a business rule, but gets a little tricker with the previous value, and doing a GlideAggregate to count.

Example 1: Chuck's record gets update so it says Chuck's manager is Dave. Your BR can say "Oh, I see chuck's manager field changed empty to something. Now, go check the box on Dave's record to say Dave is a manager.

Example 2: Chuck gets a new manager, Jeff. Now you have to use the previous value (Dave) and go to Dave's record, then count all the other sys_user records where Dave is the manager. If the number is 0, then set Dave's u_manager field to false, otherwise leave it alone.

Reference:

Business Rules - ServiceNow Wiki

Business Rules Best Practices - ServiceNow Wiki  

[Deepak Kumar5]

You can write Script include and call the function in Reference qualifier.

Or you can add a field in dictionary "is manager" and then check it for true/false

[nicksoltis]

I have decided to try a script include with an idea of my own. If it doesn't work, however, i can always mutate it into a business rule like the one Chuck helpfully mentioned. Anyways, here is what I have come up with so far. When I reference it correctly in the Advanced Reference Qualifier field on my user field, it still does not limit anything. If you could provide me with any help as to why it isn't working it would be extremely helpful. Thank You.

var getUserByManager = Class.create();

getUserByManager.prototype = {

      initialize: function() {

      },

  BackfillUser:function(){

//****Create empty arrays****\\

  var iDArray = [];

  var nameArray = [];

//****Query sys_user Table based on condition manager field is not empty****\\

  var user = new GlideRecord('sys_user');

  user.addQuery('managerISNOTEMPTY');

  user.addActiveQuery();

  user.query();

  while(user.next()){

//****populate iDArray with manager ID's****\\

            var managerID = user.getValue('manager');

            iDArray.push(managerID);

  }

//****create new array based on eliminating values in iDArray****\\

  var uniqueArray = new ArrayUtil().unique(iDArray);

//****Grab all usernames with each corresponding sys_id value in the uniqueArray****\\

  for(var i=0; i < uniqueArray.length; i++){

            var user2 = new GlideRecord('sys_user');

            user2.addQuery('sys_id', uniqueArray[i]);

            user2.query();

            while(user2.next()){

                      //****Populate nameArray with display name of user listed as manager****\\

                      var managerName = user2.getValue('name');

                      nameArray.push(managerName);

            }

  }

//****Conver each array into a comma separated string value****\\

  var newID = uniqueArray.join();

  var newName = nameArray.join();

//****return the comma-separated value of Manager Display Names****\\

  return newName;

  },

      type: 'getUserByManager'

};

[nicksoltis · Accepted solution]

Here is the solution I have created. It works quite well.

BackfillUser:function(){

  var idList = ' ';

  var a = current.user;

  if(!a){

  return;

  }

  var user = new GlideRecord('sys_user');

  user.addQuery('managerISNOTEMPTY');

  user.addActiveQuery();

  user.query();

  while(user.next()){

  if(idList.length > 0){

  idList += (',' + user.manager.sys_id);

  }

  else{

  idList = user.manager.sys_id;

  }

  }

  return 'sys_idIN' + idList;

  },