How to get all department list from user table for reference field in catalog item?

Deepika61 · ‎03-14-2023

Hi All,

Actually i have one reference field "Business", it refers to Department table, now i need a reference qualifier that i need Department list from user table (from group by department column which are not empty )?

So please help me to achieve this

Thanks

Deepika

Ankur Bawiskar · ‎03-14-2023

@Deepika61

got it.

so you want to see only those departments where there is at least some user.

then use this advanced ref qualifier

and invoke it like this

javascript&colon; fetchDepartments()

function fetchDepartments(){
	var arr = [];

	var gr = new GlideRecord("cmn_department");
	gr.query();
	while (gr.next()) {

		var user = new GlideRecord('sys_user');
		user.addQuery('department', gr.getUniqueValue());
		user.setLimit(1);
		user.query();
		if(user.hasNext())
			arr.push(gr.getUniqueValue());
	}

	return 'sys_idIN' + arr.toString();

}

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

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

View solution in original post

Ankur Bawiskar · ‎03-14-2023

@Deepika61

if you already have a script include then just create a function in it and add the code.

then call that script include function

I hope I have answered your question.

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

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

Deepika61 · ‎03-14-2023

@Ankur Bawiskar

Actually its not working for me , basically when i open the list from reference field , its shows 4000n records but in user table when i group by department it shows only 3140

Thanks

Deepika

Ankur Bawiskar · ‎03-14-2023

@Deepika61

I think issue is with the script include call in the ref qualifier.

your syntax is wrong

the function name is _getDepartment() but you are calling it without underscore.

Also it should be like this

javascript&colon; new IPAMUtils()._getDepartment();

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

Deepika61 · ‎03-14-2023

@Ankur Bawiskar

Still its not working

Thanks

Deepika

Ankur Bawiskar · ‎03-14-2023

@Deepika61

logic I shared is correct.

please debug it from your end.

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