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

Sourabh22 · ‎11-14-2023

@AnubhavRitolia

still showing all values in location field in the form.

AnubhavRitolia · ‎11-14-2023

@Sourabh22

Do share updated Script.

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

Anand Kumar P · ‎11-14-2023

Hi @Sourabh22 ,

Can you please try the script which i have shared it working fine for me i have shared screenshots with output.

Thanks,

Anand

AnubhavRitolia · ‎11-14-2023

Hi @Anand Kumar P

Your code will create performance issue as your are GlideRecord whole User table which may have thousands or record without any filter condition or grouping. Also, you userLocations array will have hundreds of Duplicates which will make the EncodedQuery more time consuming and performance issue.

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

Anand Kumar P · ‎11-15-2023

hi @AnubhavRitolia ,

Yes its my bad yes he can use addQuery method location is not null and remove duplicates from array with below script

   if (uniqueArray.indexOf(array[i]) === -1) {
                uniqueArray.push(array[i]);
}

Thanks,

Anand