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

[hammad ul aziz2]

so replicated your structure on my side and your script seems to be working fine.

table fields

list view of table records

script 

var gr = new GlideRecord('u_application_database');
gr.addEncodedQuery('u_application=A');
gr.query();
var db = [];
while(gr.next()){
db.push(gr.getDisplayValue('u_database').toString());
}
var arrUtil = new ArrayUtil();
var dbUnique = [];
dbUnique = arrUtil.unique(db);
gs.info('Database rows (unique) == ' + dbUnique);
gs.print(dbUnique);

here is the output of your script executed in the background script executer

NOTE: all fields are string here just for testing purpose

thanks

Hammad

[vkachineni]

try

db.push(gr.u_database.name.toString());

[Archisman]

I had similar scenario where i would like to show column values unique in the variable.

Below are two ways achieved the same. 

With passing a parameter: in my case it was dependent on "Country"

Script - 

getUniqueValuesBasedOnParamters: function(country) {      

        // Initializing the arrays

        var sysIdList = [];

        var nameMap = {};

        // Single GlideRecord query to fetch relevant records

        var gr = new GlideRecord('your table name');

        gr.addQuery('your parameter', country);

        gr.addQuery('active', true); // it can be gr.addActiveQuery();

        gr.query();

        while (gr.next()) {

            var name = gr.name.toString(); //Replace it with the referenced table column.

            if (!nameMap[name]) {

                nameMap[name] = true;

                sysIdList.push(gr.sys_id.toString());

            }

        }   

        var result = 'sys_idIN' + sysIdList.join(',');

        return result;

    },

Advanced Qualifier - javascript: new script_include().getUniqueValuesBasedOnParamters(variable_name); //Replace or adjust as per your requirement.

Without a paramter just by referenced column name find unique:

   getUniqueValuesBasedOnReferencedFieldName: function() {

        var sysIdList = [];

        var nameMap = {};

        // GlideRecord query without country filter

        var gr = new GlideRecord('x_ubsa3_hr_custom_legal_employer');

        gr.addActiveQuery();

        gr.query();

        while (gr.next()) {

            var name = gr.getValue('referenced_coulmn_name'); //Based on the column unique will be found.

            if (!nameMap[name]) {

                nameMap[name] = true;

                sysIdList.push(gr.getValue('sys_id'));

            }

        }

        if (sysIdList.length === 0) {

            gs.info('No unique ids found.');

        }

        return 'sys_idIN' + sysIdList.join(',');

    },

Advanced Qualifier - javascript: new script_include().getUniqueValuesBasedOnReferencedFieldName(); //Replace or adjust as per your requirement.

Mark the solution helpful. Hope it helps.