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'
};

[vkachineni]

//Please try this

var CyberarkGroups2 = Class.create();
CyberarkGroups2.prototype = {
initialize: function() {
},
CyberarkGroups2: function() {

var filter
var groups_distinct = []; //array
var gr = new GlideRecord('u_cyberark_data_integration1');
gr.addNotNullQuery('u_ad_group');
gr.orderBy('u_ad_group');
gr.query();
while (gr.next()){
if(groups_distinct.toString().indexOf(gr.groupname.toString()) == -1) //please check the col name of the group. if it is not in the array add it.
{
groups_distinct.push(gr.groupname.toString());
}

} //end of while


// Please check the filter. it is not tested
var filter = 'nameIN' + groups_distinct.toString(); //will have comma seperated distinct group names


},

type: 'CyberarkGroups2'
};

[jmillwood]

Hello, This took awhile to search and then sent back all the items in the column.

[jmillwood]

Should I using a arrayUtil.unique instead?

[Nitin_NOW]

Have you called this script to the variable? You have to set the Advanced Reference Qualifier and call your script include. Please find below links which might be helpful.

https://www.servicenowguru.com/scripting/script-includes-scripting/advanced-reference-qualifier-scri...

Please hit correct based on impact of response.

Thanks

[lokeshreddy23]

Try this for lookup select boxes, also returning sys_id of thousands of records is not good thing. ex: 1000 records * 32 characters is a damn too long string.