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'
}

[jas101]

Thanks for the follow-ups. I have now replaced the script include with your above post (although I have actually created a new SI called 'BackfillAssignmentGroupByType') and changed the Ref qual on the Assignment group to:

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

However now when doing a look-up on Assignment group, all groups are being returned, so neither the back-fill or the Group Type filter appears to be working? To confirm I currently have the SI as the below:

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

var BackfillAssignmentGroupByType = Class.create();
BackfillAssignmentGroupByType.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: "BackfillAssignmentGroupByType"
};

Thanks again for your help.

[Oleg]

I reproduced the problem in developer instance and fond one error:

groups.push(grp.group);

line should be fixed to

groups.push(grp.getValue("group"));

I created new global Script Include BackfillAssignmentGroupByType with the code, which you posted and mode only one line changed (see above). After that I set Ref qual on the Assignment group like one can see on the picture

(I used "catalog" type instead of "assignment" because there are exist no group with "assignment" type on my developer instance) and I get the correct results

Nevertheless I'm not sure why you get all groups. In my tests I get one group before I made the above fix. I'd recommend you to examine System Log > System Log > All to see some errors. You can include some debug output in your code (gs.info("some text")) to verify that your code will be really called.

[jas101]

Many thanks, it is working great now! Can I ask how you were able to identify the error?

Also, not crucial but out of intrigue is it possible to use more than one group type e.g. I have tried with Ref qual:

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

but only the 'assignment' type groups seem to show - presumably this is expected?

[Oleg · Accepted solution]

One can change the code very easy to support array of types (like ["assignment", "library"]) instead of the string (like "assignment"). First of all one have to change the call of filterGroupsonUser method to

javascript:new BackfillAssignmentGroupByType().filterGroupsonUser(current.assigned_to, ["assignment", "library"])

The modified code of filterGroupsonUser method of BackfillAssignmentGroupByType class could be the following:

/* Usage:
javascript:new BackfillAssignmentGroupByType().filterGroupsonUser(current.assigned_to,
       ["catalog", "itil"]);
*/

var BackfillAssignmentGroupByType = Class.create();
BackfillAssignmentGroupByType.prototype = {
    initialize: function() {
    },
    // Filter out groups that are not the provided type
    filterGroupsonUser: function (userSysId, groupTypeNames) {
       
        //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);
       
        var condition;
        var groupType = new GlideRecord("sys_user_group_type");
        groupTypeNames.forEach(function (groupTypeName) {
            if (groupType.get("name", groupTypeName)) {
                if (condition === undefined) {
                    condition = grp.addQuery("group.type", "CONTAINS",
                                             groupType.getUniqueValue());
                } else {
                    condition.addOrCondition("group.type", "CONTAINS",
                                             groupType.getUniqueValue());
                }
            }
        });
        grp.query();

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

I used addQuery for the first type and addOrCondition for the rest types.

[jas101]

Hi @olegki, hope you can help again please.

I have realised that while the Assignment group filter is working great when an Assigned to value is present on the Change form it does not if there is no Assigned to value.

I.e. if I do a look-up on Assignment group with no Assigned to value, all groups are returned in the list, and we require this to be only the groups which have the relevant group 'Type' values assigned to them - in the example above this would be 'catalog' and 'itil' group Types.

I have tried the most recent script include and advanced ref qual above in my developer instance and am seeing same result.

I've just realised the below part of the script include must require modification - how can we amend this so if 'Assigned to' is empty the 'Assignment group' look-up on group type is still performed?

 //return everything if the assigned_to value is empty
        if (!userSysId) {
            return;
        }

Any help much appreciated. Thank-you.