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-15-2023

Thank you @AnubhavRitolia.

Ankur Bawiskar · ‎11-14-2023

@Sourabh22

what do you mean by unused values?

please explain your business requirement in details.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Sourabh22 · ‎11-14-2023

Hi @Ankur Bawiskar ,

User table contains records each record contains location field one location value selected.
When a user request the catalog form in service portal, the location variable should only show the locations not used in the user table.

Ankur Bawiskar · ‎11-14-2023

@Sourabh22

so you want to show only those locations which are currently not allocated to any user?

what script did you start with and where are you stuck?

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Ankur Bawiskar · ‎11-15-2023

@Sourabh22

you need not query cmn_location again. You already know which locations are used by users and simply negate it

return locations which are not one of these

update as this

	var usrLoc= [];
		var usr = new GlideRecord('sys_user');
		usr.addEncodedQuery('locationISNOTEMPTY');
		usr.query();
		while(usr.next())
		{
			usrLoc.push(usr.getValue('location'));
		}
		return 'sys_idNOT IN' + usrLoc.toString();
	},

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader