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

[Deepika61]

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

[Aniket Bhanse]

Hey,

Here is the script include for the same:

And put this in your reference qualifier:

javascript:(new global.GetUserDepartments().departmentName())

[Ankur Bawiskar]

@Deepika61 

reference field is referring to cmn_department.

Each record in cmn_department will have unique department.

I didn't get your complete requirement.

Can you share with screenshots?

[Deepika61]

@Ankur Bawiskar 

Actually Department table has many department list, but i want that the department list which are existing in  the user table only ?

Like in the department table it has 1000 departments, but in the user table (under department column), users are assigned to 100 departments only that few users are assigned to same deparment, so i want this list to be pull up for that field

Thanks

Deepika

[Ankur Bawiskar · Accepted solution]

@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: 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.

[Deepika61]

@Ankur Bawiskar 

Is this a script include?, actually we have one script include, so do i need to add this code in that?

Thanks

Deepika