Only show assignment group if one or more members are active users

[mattmm]

HI All, I've had an issue where my Change Model is skipping a peer approval step because some of Peer approver groups have all inactive users (I have a "Peer approval group" field that references the group table).

How do I do 1 of the following, and what is best practice 

1. Deactivate any Group in the group table that also contains all inactive users (every user is inactive in that group) and then how do I ensure those groups do not then show in the assignment group field, and my "Peer approval group" field?

2. Not show any group that has inactive users as a choice in my "Peer approval group" field?

[AnirudhKumar]

Option 2 is optimal.

And to do it, update the reference qualifier on your Peer approval group field. You will need an advanced reference qualifier, call a script include to return only groups with atleast one active user.

[mattmm]

Thanks @AnirudhKumar , any idea what my script would look like in order to only do this if all users were inactive within that group, not just some?

[AnirudhKumar]

Hey @mattmm ,

Here you go:

Script Include:

var GroupUtils = Class.create();
GroupUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getGroupsWithActiveMembers: function() {

        var result = [];

        var groupObj = new GlideRecord("sys_user_group");
        groupObj.query();
        while (groupObj.next()) {

            var grMemberObj = new GlideRecord('sys_user_grmember');
            grMemberObj.addQuery('group', groupObj.sys_id.toString());
            grMemberObj.addQuery('user.active', 'true');
            grMemberObj.query();
            if (!grMemberObj.next())
                result.push(groupObj.sys_id.toString());
        }

        var activeGroupQuery = '';
        for (var i = 0; i < result.length; i++) {
            activeGroupQuery = activeGroupQuery + 'sys_id!=' + result[i] + '^';
        }
        return activeGroupQuery;
    }
});

Advanced Reference Qualifier on your Dictionary entry

javascript: new GroupUtils().getGroupsWithActiveMembers();

(just remember to set the script include 'client callable') 

The script include returns a query that returns all groups that have atleast 1 active user.

The query filters out groups with no active users and also filters out groups with no users

Edit: I've made a small update to the function name, and the same in the qualifier

[Vrushali Kolte]

Hello @mattmm ,

use case 1 :

You can use the script below to deactivate any group that contains only inactive users. Depending on your needs, you can implement this script within a scheduled job or a business rule to trigger it for any future data updates.

var result = [];
var groupGr = new GlideAggregate('sys_user_grmember');
groupGr.groupBy('group');
groupGr.addAggregate('COUNT', 'group');
groupGr.query();
while (groupGr.next()) {
    var totaluser = groupGr.getAggregate('COUNT','group');
    gs.info(groupGr.group.name +  ' user : '+ totaluser);
    var gr = new GlideAggregate('sys_user_grmember');
    gr.addQuery('group', groupGr.group);
    gr.query();
    var count = 0;
      while(gr.next()){
         if(gr.user.active+'' == 'false'){
            count = count + 1;
         }
      }

     if(totaluser == count){
        result.push(groupGr.group.sys_id); // push group sys_id which has only inactive users.
     }

 }
gs.info('sys Id '+ result);


var grupdate = new GlideRecord('sys_user_group');
grupdate.addEncodedQuery('sys_idIN'+result);
grupdate.query();
while(grupdate.next()){
grupdate.active = 'false'; // make group active false
grupdate.update();
}

  

I have tried this script in my PDI and it worked perfectly.

user case2:

Once you have your updated data in the [sys_user_group] table, you can create reference qualifiers for the "assignment_group" and "Peer Approval Group" fields as below -

Groups that contain only inactive users will automatically be marked as inactive (as per use case 1). Therefore, this will return only groups that have at least one active user.

If my answer was useful to you, please mark it as Accepted ✅ and Helpful 👍.