Search cmn_department that has users

[UIH_SN_dev01]

catalog item --> reference field --> cmn_department --> how to only show departments where the users tab have member(s) in it?  is there a way to filter the cmn_department so that it only shows department where there are user in the users tab?

[AndersBGS]

Hi @UIH_SN_dev01 ,

Go to the sys_user table and investigate if there isn't a reference field relating to cmn_department.

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

Best regards

Anders 

[Sandeep Rajput · Accepted solution]

@UIH_SN_dev01 You need to use the following reference qualifier on your Department variable on the catalog item.

Here is the reference qualifier code.

javascript:getDepartmentWithUsers(); function getDepartmentWithUsers(){ var userDept = new GlideRecord('cmn_department'); userDept.addJoinQuery('sys_user','sys_id','department'); userDept.query(); var refQual = 'sys_idIN'; while(userDept.next()){ refQual = refQual+userDept.sys_id+','; } return refQual; }

Please consider marking the answer correct and helpful if it manages to address your question.

[UIH_SN_dev01]

thank you for the help.  i tried the code but it is still bringing back departments where there are no users in them.

[Sandeep Rajput]

@UIH_SN_dev01 I already tested this code on my instance and it only fetches the department with the users.

Could you please run the following code in a background script on your instance and check it it prints the name of those departments where there is at least one user.

getDepartmentWithUsers(); 
function getDepartmentWithUsers()
{ 
var userDept = new GlideRecord('cmn_department'); 
userDept.addJoinQuery('sys_user','sys_id','department'); 
userDept.query(); 
while(userDept.next())
{ 
gs.info(userDept.name);
} 
}