Hi ServiceNow Enthusiast,
Being a ServiceNow Developer, we regularly use Filter Conditions like 'equal to', 'is not', 'is one of', 'greater than', etc and aware of how to use them in our GlideRecord script.
But sometimes we may have a situation where we may need to use filter condition 'is not one of ' in our GlideRecord/GlideAggregate script. Let me explain you with a scenario and solution:
Scenario: Assume we have a Custom field called "New Location" which refers to Location [cmn_location] table. We have requirement that "New Location" field should show only those Locations as choice which is not being referred in Location field of User table.
Solution:
Step 1: In Script Include, we have to get all the Referred Location records from User table and store it in an Array.
Step 2: We have to GlideRecord Location [cmn_location] table and we have to apply filter to exclude all the locations stored in above array.
Step 3: We have to return the filtered Locations sys ID.
Step 4: We have to call the Script Include and function in Advanced Reference Qualifier of 'New Location' field.
You can write an Script Include , name '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"+usrLoc);
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();
Please Bookmark this Article 🔖, Share with your friends / colleagues and Mark it as Helpful 👍 if you thought it was helpful.
Also feel free to provide your comments/feedback on this Article.
Regards,
Anubhav Ritolia
Community Rising Star 2023
LinkedIn Profile
