How to filter referenced field values

Sourabh22 · ‎11-14-2023

I have a catalog form which contains location variable referenced to location table.
I want to show only unused values of location table in form.
I don't want to show the values used by user table.

How to achieve this????

AnubhavRitolia · ‎11-14-2023

Hi @Sourabh22

You can write an Script Include , assume 'ExcludeLocation' and a function called 'getExcludedLocations()' and you can write below code in it:

var usrLoc= [];
var usr = new GlideAggregate('sys_user');
usr.addAggregate('COUNT', 'location');
usr.addEncodedQuery("location!=NULL");
usr.groupBy('location');
usr.query();

while(usr.next())
{
usrLoc.push(usr.getValue('location'));
}

var locArr = [];
var loc = new GlideRecord('cmn_location');
loc.addEncodedQuery("sys_idNOT IN"+locArr);
loc.query();

while(loc.next())
{
locArr.push(loc.getUniqueValue());
}

return "sys_idIN"+locArr;

You can now call this Script Include in your Reference Qualifier of Variable using below link:

javascript&colon; new ExcludeLocation().getExcludedLocations();

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

Thanks
Anubhav Ritolia
ServiceNow Rising Star 2023

LinkedIn Profile

View solution in original post

Anand Kumar P · ‎11-14-2023

Hi @Sourabh22 ,

I have tried in my PDI its working fine use below script.

var getlocation = Class.create();
getlocation.prototype = {
    initialize: function() {
    },
  getUserLocations: function () {
       var userLocations = [];
    var userGr = new GlideRecord('sys_user');
    userGr.query();
    while (userGr.next()) {
        var location = userGr.location.toString();
        if (location) {
            userLocations.push(location);
        }
    }
    if (userLocations.length > 0) {
        var locationGr = new GlideRecord('cmn_location');
        locationGr.addQuery('sys_id', 'NOT IN', userLocations);
        locationGr.query();
        var condition = '';
        while (locationGr.next()) {
            if (condition !== '') {
                condition += '^OR';
            }
            condition += 'sys_id=' + locationGr.sys_id;
        }
		return 'sys_idIN' + condition;
	}
    },
    type: 'getlocation'
};

javascript&colon; new getlocation().getUserLocations()

Please mark it as helpful and solution proposed if it serves your purpose.

Thanks,

Anand