How to add ref qualifier to backfill assignment group script include e.g. to include / filter by group type also?

[jas101]

Hi guys, there are a few historic posts on this but I cannot see one marked with a 'Correct answer' so I hope someone can deliver the goods (help)!

Basically we utilise the 'BackfillAssignmentGroup' script include so our Task table 'Assignment group' field has the Reference qual: javascript:new BackfillAssignmentGroup().filterGroupsonUser(current.assigned_to);

We have recently started utilising the Group 'Type' field (table: sys_user_group_type) functionality so we want to include this within this Ref Qual also i.e. keep the backfill behaviour but also only ensure that Groups with Type value 'assignment' appear.

How can we best achieve this please? I am assuming the existing script include will need to be modified (as opposed to being able to update the Ref Qual itself) but I have not had much like with the couple of aforementioned posts (which might explain why none have been marked as 'Correct answer'). Current script include below.

Thanks as always.

//Usage javascript:new BackfillAssignmentGroup().filterGroupsonUser(current.assigned_to);

var BackfillAssignmentGroup = Class.create();
BackfillAssignmentGroup.prototype = {
    initialize: function() {
    },
	  // Filter out groups that are not the provided type
   filterGroupsonUser: function(cUser) {
	   
		var gp = ' ';
		var a = cUser;
		//return everything if the assigned_to value is empty
		if(!cUser)
		return;
		 
		//sys_user_grmember has the user to group relationship
		var grp = new GlideRecord('sys_user_grmember');
		grp.addQuery('user',cUser);
		grp.query();
	   
		while(grp.next()) {
			
			if (gp.length > 0) {
					//build a comma separated string of groups if there is more than one
					gp += (',' + grp.group);
					
			}
			else {
					gp = grp.group;
					
			}
		}
	return 'sys_idIN' + gp;
	// return Groups where assigned to is in those groups we use IN for lists
   
	   
   },

	type: 'BackfillAssignmentGroup'
}

[Oleg]

If I correctly understand your question then you can add one more method, for example, with the name filterUsersOnGroup, which parameter is the sys_id of the group, chosen in assignment_group. The Reference qual of "Assigned to" field could be:

javascript:new BackfillAssignmentGroup().filterUsersOnGroup(current.assignment_group)

The code of filterUsersOnGroup could be the following

var BackfillAssignmentGroup = Class.create();
BackfillAssignmentGroup.prototype = {
    initialize: function() {
    },
    // Filter out groups that are not the provided type
    filterGroupsonUser: function(cUser) {
        ... // your old code
    },
    filterUsersOnGroup: function(group) {
        if (!group) {
            return;
        }

        var gr = new GlideRecord('sys_user_grmember');
        gr.addQuery('group', group);
        gr.query();

        var users = [];
        while (gr.next()) {
            users.push(gr.user);
        }

        return users.length > 0 ? 'sys_idIN' + users.join() : undefined;
    },
    type: 'BackfillAssignmentGroup'
}

[jas101]

Hi, thanks for the reply but I am not following  unfortunately. Where you mention:

javascript:new BackfillAssignmentGroup().filterUsersOnGroup(current.assignment_group)

is this instead of:

javascript:new BackfillAssignmentGroup().filterGroupsonUser(current.assigned_to);

referred to in my post? I.e. you mention GroupsonUser instead of my UsersOnGroup?

How does the Group Type filter figure into this filter sorry? Thanks for your help.

[Oleg]

Sorry, I misunderstood your question at the first reading. What you can do is to modify the code of filterGroupsonUser to support the second parameter - the group type, which you want to filter (for example "assignment"). The code of the modified version of filterGroupsonUser could be

//Usage javascript:new BackfillAssignmentGroup().filterGroupsonUser(current.assigned_to, "assignment");

var BackfillAssignmentGroup = Class.create();
BackfillAssignmentGroup.prototype = {
    initialize: function() {
    },
	// Filter out groups that are not the provided type
    filterGroupsonUser: function (userSysId, groupTypeName) {
	   
		//return everything if the assigned_to value is empty
		if (!userSysId) {
			return;
		}
		 
		//sys_user_grmember has the user to group relationship
		var grp = new GlideRecord("sys_user_grmember");
		grp.addQuery("user", userSysId);
	   
		if (groupTypeName) {
			var groupType = new GlideRecord("sys_user_group_type");
			if (groupType.get("name", groupTypeName)) {
				grp.addQuery("group.type", "CONTAINS", groupType.getUniqueValue());
			}
		}
		grp.query();

		var groups = [];
		while (grp.next()) {
			groups.push(grp.group);
		}
	    return "sys_idIN" + groups.join();
	    // return Groups where assigned to is in those groups we use IN for lists
    },
	type: "BackfillAssignmentGroup"
}

and the usage like

javascript:new BackfillAssignmentGroup().filterGroupsonUser(current.assigned_to, "assignment");

[Oleg]

The correct filter operation on "group.type" field should be "CONTAINS" instead of "LIKE" if one uses addQuery method. I made the corresponding modification in the code, which I posted previously.