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

Deepika61 · ‎03-14-2023

@Ankur Bawiskar

Thanks for the response

I debug it and working fine, but its take much time to load the records, So as my lead suggests me use GlideAggregate instead

So can you please provide me code based on GlideAggregate , if possible?

Thanks

Deepika

Ankur Bawiskar · ‎03-14-2023

@Deepika61

Glad to know that my script worked.

Can you mark my response as correct to close the thread?

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

Deepika61 · ‎03-14-2023

@Ankur Bawiskar

i am not achieving the correct response , can you please provide me the code using GlideAggregate,?, so it will fulfill my requirement

Thanks

Deepika

Joe Taylor · ‎12-18-2023

Hi. I have a similar requirement.

I want to use a pulldown on a form to select a Department.

However, I only want to list those departments only if there is at least 1 ACTIVE employee in it.

How can I do this?

Ankur Bawiskar · ‎12-18-2023

@Joe Taylor

Could you please post this as a separate question and tag me there as this is an old thread?

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