Search cmn_department that has users

UIH_SN_dev01 · ‎05-02-2024

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?

Sandeep Rajput · ‎05-02-2024

@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&colon;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.

View solution in original post

UIH_SN_dev01 · ‎05-02-2024

running the script that you provided, it returned departments with at least one user in it. however the qualifier code returned departments that did not have any user in them.

UIH_SN_dev01 · ‎05-02-2024

javascript&colon;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; } - needed to remove &colon; from your example and changed it to ":" and the code worked. thanks alot.