Reference Qualifier Unique Values

[jmillwood]

Hello Community, 

I have a Catalog item that currently has 3 Lookup Select boxes. The table they reference has 90k records. This is causing a performance issue. I need to convert at least 2 of them to Reference variables. The table that is being referenced has many safe keys to one group name. So for the end users I want them to only see the unique (distinct) Group name. 

Here is the Script Include I've written, but it's not pulling back just the distinct sys_id. What am I missing? 

var CyberarkGroups2 = Class.create();
CyberarkGroups2.prototype = {
initialize: function() {
},
getGrps: function() {
var gr = new GlideRecord('u_cyberark_data_integration1');
gr.addNotNullQuery('u_ad_group');
gr.orderBy('u_ad_group');
gr.query();
var filter='sys_idIN';
while (gr.next()){

filter += gr.sys_id + ','; //Gets all sys_ids for table u_cyberark_data_integration1
}

return filter;

},

type: 'CyberarkGroups2'
};

[jmillwood]

I have 3 on the form that reference a table with 90k records. HI support told me I needed to convert to reference variables. 

[Abhinay Erra]

Shouldn't you be pulling the sys_id's of the groups instead of sys_id of 'u_cyberark_data_integration1'

[jmillwood]

Theses groups are loaded into this table and are not apart of Groups table in ServiceNow. There are many Safe names in safe name column to one AD group name, So I only need to show the distinct AD group name in the variable.

[Mike Patel]

try below

var CyberarkGroups2 = Class.create();
CyberarkGroups2.prototype = {
	initialize: function() {
	},
	CyberarkGroups2: function() {
		var gr = new GlideRecord('u_cyberark_data_integration1');
		gr.addNotNullQuery('u_ad_group');
		gr.orderBy('u_ad_group');
		gr.query();
		//var filter='sys_idIN';
		var sysid = [];
		while (gr.next()){
			sysid.push(gr.u_ad_group.toString());
		}
		return 'sys_idIN'+sysid.toString();
	},
	
	type: 'CyberarkGroups2'
};

[jmillwood]

This returned "No matching results" but at least it didn't error 🙂 I wonder if using ArryUtil unique would fix it?