Ensuring Unique Values in ServiceNow Reference Variables

ServiceNow reference fields (e.g. in forms or catalog items) often provide dropdown selections for users. In some cases, duplicate display values can appear, leading to confusion and inefficiency. For example, if a table contains multiple records with the same name (but different sys_ids), a user searching the reference field might see the same name listed multiple times. This article explores how to ensure only unique values are displayed in a reference field while maintaining performance and usability.

Understanding the Issue

Reference fields pull data from tables that may contain duplicate names or values. This commonly occurs in tables like Assignment Group or Cost Center, where multiple records can share an identical Name even though their sys_ids differ. As a result, users may see indistinguishable options such as the same site or group name repeated several times. This makes it difficult to know which entry to select, undermining the user experience and potentially causing mistakes.

Best Practices for Displaying Unique Values

To address duplicate values in reference lookups, consider the following best practices:

1. Prevent Duplicates at the Source (Table/Data Level) – The most effective solution is to enforce uniqueness at the database level. Mark the relevant field (e.g. the Name of a group or cost center) as unique in the dictionary. When a field is set to require unique values, the system will not let two records share the same value. In other words, no duplicate names can be created for that field. This is the recommended approach for fields like group names. If duplicates already exist in the table, you should first identify and clean them up (merging or renaming as appropriate) so that a unique constraint can be applied without conflicts.

   
   

   Alternate Approach – Unique Option in Lookup Select Box: If you are using a Service Catalog variable for user selection, consider using a Lookup Select Box instead of a standard Reference variable. The Lookup Select Box type includes a Unique values only configuration that automatically filters out duplicate display values. Enabling this option (available under the variable's Type Specifications) ensures that each name appears only once in the dropdown, even if the underlying table has duplicates. This is a quick win to improve usability when you cannot easily remove duplicates from the data source.

   
   

2. Filtering Unique Values Using GlideAggregate – When fetching reference choices via script, use GlideAggregate to retrieve unique values efficiently. GlideAggregate is optimized for grouped queries and can return one record per unique field value, which is much faster than processing all records in script. For example, the script below uses GlideAggregate to get a distinct list of cost center names:

var ga = new GlideAggregate('cmn_cost_center'); 
ga.addAggregate('COUNT', 'name'); 
ga.query(); 
while (ga.next()) { 
	gs.print(ga.getValue('name')); 
}

In this snippet, the GlideAggregate groups results by the name field. Each unique name will be printed only once, because the aggregate query returns one row per distinct name. This approach is ideal for large tables since the database does the heavy lifting of filtering out duplicates, reducing the amount of data transferred and processed in memory.

3. Using Advanced Reference Qualifiers – ServiceNow allows advanced reference qualifiers (for form fields or catalog variables) to dynamically filter the options. You can leverage this to show only unique values. The idea is to call a server-side script (such as a Script Include) that returns a filtered set of sys_ids, and use those in an IN query for the reference field. For example, an advanced reference qualifier might use the javascript: prefix to call a function that provides unique results:

javascript: 'sys_idIN' + new ScriptInclude('UniqueReferenceHelper').getUniqueCostCenters()

In the above qualifier, 'UniqueReferenceHelper' is a Script Include that returns a comma-separated list of sys_ids (in this case, for cost center records with unique names). The reference field will then only show records whose sys_id is in that list. This ensures that if a name appears in multiple records, only one of those records (the first found) is included in the choices. The result for the end-user is that each name appears just once in the dropdown.

4. Leveraging Script Includes for Custom Filtering – The heavy lifting in the advanced qualifier approach is done by a Script Include, which queries the table and compiles unique results. Below is an example Script Include (client-callable) that collects unique Cost Center names and returns their sys_ids:

var UniqueReferenceHelper = Class.create();
UniqueReferenceHelper.prototype = { 
	initialize: function() {
	}, 
	getUniqueCostCenters: function() { 
		var names = []; 
		var sysIds = [];
		var gr = new GlideRecord('cmn_cost_center');
		gr.query(); 
		while (gr.next()) { 
			if (names.indexOf(gr.name.toString()) === -1) { 					 
				names.push(gr.name.toString()); 
				sysIds.push(gr.sys_id.toString()); 
			} 
		} 
		return sysIds.join(','); 
	}, 
	type: 'UniqueReferenceHelper' 
};

This Script Include iterates through all cost center records and builds an array of names and a parallel array of sys_ids. It only adds a record's sys_id if that name hasn’t been encountered before, thereby ensuring each name is collected once. The function returns a comma-separated string of sys_ids, which the reference qualifier uses in the 'sys_idIN' query. Remember to mark such a Script Include as Client callable so that it can be invoked from the client side qualifier. Also, ensure the Script Include name and function name align properly if you're using a direct function format. With this in place, the reference field will effectively show only the first occurrence of each name from the table.

Conclusion

Displaying only unique values in a reference field is essential for improving usability and preventing user confusion. The best strategy is to prevent duplicate entries at the source – for instance, by enforcing unique field values at the database level so that duplicates cannot exist. When source-level prevention isn’t feasible (or for quick wins in the catalog UI), techniques like using GlideAggregate for distinct queries or advanced reference qualifiers with Script Includes can effectively filter out duplicates on the fly. These solutions ensure users see a clean list of options without repetition, streamlining the selection process and enhancing the overall user experience. Keep in mind that script-based filters add complexity and overhead, so use them judiciously and favor data integrity as a long-term solution. Each of the approaches above can be adapted or combined based on your specific requirements – feel free to explore alternative variations of the scripts to best suit your needs.