Query on list collector

[Mani60]

Hi ,

 I have one requirement ,

 I have one List collector Variable on Catalog item, that is reference to sys_user table , when the requestor field is selected and requestor is the member of XYZ group and ABC department then in list collector also i need to select only the members which are belongs to ABC department.
 for this I have written one Reference qualifier based on the requestor's company, but now I need based on the requestor's department. 

javascript: var gr= new GlideRecord('sys_user');gr.addQuery('company='+gs.getUser().getUserByID(current.variables.requestor).getCompanyID());gr.query();var users= '';while(gr.next()){if(gs.getUser().getUserByID(gr.sys_id).isMemberOf("Officers Group Approvals")){users+=gr.sys_id+',';}}"sys_idIN"+users;

can anyone help me how can I achieve this!!

Thanks,

[Sai Shravan]

Hi @Mani60 ,

Below is the updated reference qualifier to filter users based on their department as well as their membership in the "Officers Group Approvals" group. This is non tested script

javascript:var gr= new GlideRecord('sys_user');
gr.addQuery('company='+gs.getUser().getUserByID(current.variables.requestor).getCompanyID());
gr.addQuery('department='+gs.getUser().getUserByID(current.variables.requestor).getDepartment());
gr.query();
var users= '';
while(gr.next()){
  if(gs.getUser().getUserByID(gr.sys_id).isMemberOf("Officers Group Approvals")){
    users+=gr.sys_id+',';
  }
}
"sys_idIN"+users;

This will filter the list of users to only show users from the same company and department as the selected requestor, and who are members of the "Officers Group Approvals" group.

Regards,

Shravan.

Please mark it as helpful if this solves your requirement.

[Community Alums]

It's unwise to perform GlideRecord queries on the client side. This is not the recommended practice. 

It's best to create a new function in a script include and have it do the heavy lifting, then simply call the script include from reference qualifier. 

You can create a dynamic function which will take the input of the SYSID of the group in question, then only return users from that group. 

E.g. 

getUsersFromGroup: function(groupId) {
        if (groupId) {
            var glideGrMember = new GlideRecord('sys_user_grmember');
            glideGrMember.addEncodedQuery('group.sys_id=' + groupId);
            glideGrMember.query();
            var userArray = [];

            while (glideGrMember.next()) {
                userArray.push(glideGrMember.getValue('user'));
            }

            return 'sys_idIN' + userArray.toString() + '^active=true';
        }
    },

Then in the reference qualifier you'd have this:

javascript: new exampleScriptIncludeName().getUsersFromGroup('0fa44d38d5620a50169a2f0d608a1e29');

(This website replaces the colen with '&colen;'. Make sure you are simply using this: 

)

• [new] initiates the script include.
• [exampleScriptInclude()] is the script include name. Modify this to suit your setup. 
• [getUsersFromGroup] is the function name within the script include you wish to call. As per the example above, you can dot walk to this.
• [('0fa44d38d5620a50169a2f0d608a1e29')] this is the SYSID of the group you wish the function to check. This is inputted in the reference qualifier. 
  
  

So now you have a function which can be used over and over again for any group. 

*** Update ***
I just re read your initial post. Apologies, the solution above is simply updating a list collector to only show users who are members of a particular group. You're requirement is a tad different. 
The theory still remains, do not perform the heavy lifting on the client side. Do this work in a script include.