How to: create an advanced reference qualifier for a Record Producer

[Danny Mortense1]

You can use a reference qualifier to restrict the data that is selectable for a reference field. Setting up a simple reference qualifier is quite straight forward, especially if you simply need to filter the results of the referenced field by a static value. However, creating an advanced reference qualifier for a record producer proved to be a lot trickier than I first imagined.

With the help of my legendary colleague and good friend Arlen Vartazarian (Kudos) I have created my own example based upon the example Constraining the Assignment Group Field found in the wiki. The script include below can be written in a number of ways, but this is just one that works for me.

Firstly we need to create our Record Producer with two variables, u_company and u_user.

1. Service Catalog > Record Producer
2. New
3. Name: Users with company example
   Table name: User [sys_user]

Now create the first variable, a reference type field referring to the core_company table

1. On the Variables related list tab, click New
2. Type: Reference
3. Select the Question tab and enter
   Question: Select a company.
   Name: u_company
4. Select theType Specifications tab and enter
   Reference: Company [core_company]
5. Click Submit

Now create the second variable, a reference type field referring to the sys_user table. This time we'll put on an advanced reference qualifier.

1. On the Variables related list tab, click New
2. Type: Reference
3. Select the Question tab and enter
   Question: Select a user.
   Name: u_user
4. Select theType Specifications tab and enter
   Reference: User [sys_user]
   Use reference qualifier: Advanced
   Reference qual: javascript: u_getCurrentCompanyUsers()
5. Click Submit

That has created the basic requirements for our example Record Producer with two variables requesting the user to select a company and a user. However, we are not finished yet. We now need to create a Script Include containing the function we are calling in our Record Producer. This function will return a list of users for the company selected from your first Record Producer variable.

1. Service Definition > Script Includes
2. New
3. Name: u_getCurrentCompanyUsers
   Script:

function u_getCurrentCompanyUsers(){

  var usrsList = ' ';

  var cmpny = current.variables.u_company;

  var usr = new GlideRecord('sys_user');

  usr.addQuery('company',cmpny);

  usr.query();

  while(usr.next()) {

  if (usrsList.length > 0) {

  //build a comma separated string of groups if there is more than one

  usrsList += (',' + usr.sys_id);

  }

  else {

  usrsList = usr.sys_id;

  }

  }

  // return a list of sus_user id's for the selected company

  return 'sys_idIN' + usrsList;

}

  4. Click Submit

Done!! Now go back to you Record Producer and Try It.

NOTE: I am by no means a scripting guru, so please feel free to contribute improvements or your own examples. This is simply me sharing knowledge acquired during my journey of learning the power of the ServiceNow platform.

[Danny Mortense1]

FURTHER NOTE: The example here is not a very realistic example as you would not want to create a sys_user record by selecting a user from the sys_user table, however you can just select a different table for field labelled Table name: in step 3 when creating a new Record Producer.

[northernb]

This appears to be a slight variation on Adding Dependent Reference Variables.   Differences appear to be that this version don't use a class and this version references the other variable on the form within the function whereas the wiki version passes the variable to the function as a parameter.

However is anyone able to explain to me why something like the following does not work as the reference qualifier:

company=javascript: current.variables.cr_rec_pro_company;

or

company=javascript: toString(current.variables.cr_rec_pro_company);

as both seem to return a nil/empty result as the resulting lookup only appears to show users without a company.   cr_rec_pro_company is the name of a reference variable in the form that refers to core_company table.

Hardwiring the reference qualifier with the sys_id of a company like Acme UK (I am using a personal dev instance) does work:

company=a66b1fb03710200044e0bfc8bcbe5d08

Many thanks

Mark

[lasse3]

Hi Mark,

You can do this, but you need to start with the "javascript:" part to keep it in pure javascript. So in your example you could set the advanced reference qualifier to:

javascript: 'company='+current.variables.cr_rec_pro_company.toString();

Kind regards

Lasse

[kulshreshtha]

Hi Lasse

In addition to company, if i also need to filter out users based on Department as well, is there something like & operator to filter based on multiple fields?

Regards

Naman