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 || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Ankur Bawiskar · ‎03-14-2023

@Deepika61

sorry didn't get.

your field refers cmn_department table.

which records you want to show and what you want to restrict?

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

Deepika61 · ‎03-14-2023

Hi @Ankur Bawiskar

Basically my requirement was that i need a reference qualifier that need to pull up all unique departments from user table (group by department column which are not empty)

Thanks

Deepika

Ankur Bawiskar · ‎03-14-2023

@Deepika61

you cannot group by in reference qualifier

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

Deepika61 · ‎03-14-2023

Yes, we can use advanced reference qualifier and i think we can achieve it though script include by calling a function

Can you please provide me a script for that if possible ?

Thanks

Deepika