Filter Edit List Reference List Results (m2m lists)

Most admins are familiar with reference qualifiers and advanced qualifiers that can leverage data from the current record to filter a reference field lookup. If you want to qualify or filter a list of records that display when using the edit button on a form's many-to-many related list, things are a bit trickier.

Let's say we're on the incident form with the related list of affected CIs. By default, when we click the edit button to add new CIs all records are displayed and we have to manually filter the list or search for records. If we wanted to limit the list of CIs displayed in the slushbucket to only the CIs that are assigned to the incident's caller, we need to add a script and a couple dictionary attributes to pull it off.

 

Required dictionary attributes

The Affected CI table is 'task_ci', this links any task(s) to any CI(s). We need to add some control on the task_ci.ci_item field since this is the field we want to filter. Open the dictionary record for this field. Depending on your version you can either populate the attribute field or use the related list to add the following attributes:

  • mtmquerygenerator=com.glide.misc.ScriptedListGenerator
  • mtmqueryscript=incidentAffectedCIFilter

 

Now for a little explanation of these attributes.

The mtmquerygenerator attribute tells the system to look for a script to process before we execute the many-to-many query. There are other options but they usually aren't helpful for things other than what they were designed for. Feel free to look around the dictionary for other cases if you're curious.

The mtmqueryscript attribute defines what script we are going to run. You can call it whatever you want but it needs to match the business rule we are going to create next.

 

Required script

Get ready to get totally lost. This won't make any sense, but you'll just have to trust me on this one. There's special handling for the ScriptedListGenerator that's been around since the ancient days of ServiceNow, before there were script includes and all the options we have now on business rules. The generator reads the mtmqueryscript attribute and looks up a business rule with that name. It has nothing to do with a function name, it's all on the name of the rule. Once the rule is found, the script is simply evaluated, meaning it has to be self-executing, which is a big no-no typically since it would end up executing on every transaction. We prevent this by disabling the business rule. You read right, we're going to execute a disabled rule.

See, I told you it was going to be confusing. Don't try to make sense of it, just carry on.

Create a new business rule with the following properties.

  • Name: incidentAffectedCIFilter (or whatever you used in the attribute)
  • Table: global (usually not a good thing, but ok here since it's disabled)
  • Active: disabled (really make sure this is not enabled)
  • When to run: none
  • Script:
incidentAffectedCIFilter();

function incidentAffectedCIFilter() {
  //only run for incident affected CIs
  //you could add custom filters for each task table if you needed
  if (!current.getTableName == "incident")
        return;
 
  //'current' object is the parent form GlideRecord
  var caller = current.getValue("caller_id");
  //if we don't have a caller, don't filter
  if (caller.nil())
    return;
  
  //the 'lookup' object is a GlideRecord query
  lookup.addQuery('assigned_to', caller);
}

As I mentioned, this script block will be evaluated from the generator and not executed as a typical business rule. You have access to the following objects:

  • current - GlideRecord of the parent form record
  • lookup - GlideRecord to query the other side of the m2m table, just add your addQuery statements as needed

 

Depending on your instance version, you may have trouble with script validation because the script is not wrapped in a function, which would cause it to run all the time. If this is the case, just open a legacy script and use the insert or insert and stay actions to make a copy. This will allow you to use a less structured script and work around the newer validation rules.

 

Testing

Now create an incident with a caller that has CIs assigned to them, I'll use Beth since she has CIs in the demo data. Save the incident and click the affected CIs edit button to add some CIs. The list should be filtered on just the CIs assigned to that caller.