How to filter referenced field values

[Sourabh22]

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]

Hi @Sourabh22 

I guess you want to show all Locations value in the Variable choice which are not been referred in User Table, Right?

[AnubhavRitolia · Accepted solution]

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: new ExcludeLocation().getExcludedLocations();

[Sourabh22]

Thanks for your response
It is still showing all values in the location field.

[AnubhavRitolia]

@Sourabh22 

Could you please share screenshot of what you gave written.