Advanced Reference Qualifier not working

[Ryan S]

Hi all. Working in a domain separated environment and have a requirement for the 'Assigned_To' field to be filtered based on the user being in 2 sys_user_group--the selected 'Assignment_Group' as well as another Visibility group. I've tried:

1. background script with hardcoded sys_ids. It works. I get the expected result which returns 1 user

2. all code in reference qualifier: Doesn't work. I get a list of all users

3. reference qualifier calling script include. Doesn't work. I get no users returned.

Assuming #3 is best approach, I've put that code below for review.

Assigned_To reference qualifier:

javascript: new AssignedToReferenceQualifier().getAssignedTo(current.company.sys_id,current.company.sys_domain,current.assignment_group.sys_id)

Script Include:

var AssignedToReferenceQualifier = Class.create();
AssignedToReferenceQualifier.prototype = {
    initialize: function() {
    },
	getAssignedTo: function(myCompany, myDomain, myGroup) {
//query the Group Visibility Domain table to find the Visibility Group that grants access to the company's domain
    var visibilityGroup = new GlideRecord('sys_user_group_visibility');
    visibilityGroup.addQuery('sys_domain', myDomain);
	visibilityGroup.addQuery('group.name', 'CONTAINS', myCompany);
    visibilityGroup.query();
    while (visibilityGroup.next()) {
		var groupSysID = visibilityGroup.group;
		gs.info(groupSysID);
	}
//now that we have the Visibility, we can filter the group membership table for the members of this group
    var userVisArray = [];
    var groupMembers = new GlideRecord('sys_user_grmember');
    groupMembers.addQuery('group',groupSysID);
    groupMembers.query();
	while(groupMembers.next()) {
		userVisArray.push(groupMembers.user.toString());
	}
	var membershipResults = [];
	var strLength = userVisArray.length;
//now that we have members that are part of the Visibility group, confirm they are also part of the selected assignment group
	var userAssignArray = [];
	for(var i = 0; i < strLength; i++){
		var userID = userVisArray[i];
		gs.info(userID);
		var grMember = new GlideRecord('sys_user_grmember');
		grMember.addQuery('user',userID);
		grMember.addQuery('group',myGroup);
		grMember.query();
		while(grMember.next()){
			userAssignArray.push(grMember.user.toString());
		}
	}
	return 'sys_idIN' + userAssignArray.join(',');
    },
    type: 'AssignedToReferenceQualifier'
};

[Harsh_Deep]

Hello @Ryan S 

In the below line you are giving CONTAINS and you are passing myCompany but checking group.name, it should be company, myCompany

visibilityGroup.addQuery('group.name', 'CONTAINS', myCompany);

But in the reference qualifier you are passing company.sys_id

javascript: new AssignedToReferenceQualifier().getAssignedTo(current.company.sys_id,current.company.sys_domain,current.assignment_group.sys_id)

So please use below code-

visibilityGroup.addQuery('company', myCompany);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.

[Ankur Bawiskar]

@Ryan S 

please share how are you testing in background scripts and what values you are passing.

when advanced ref qualifier is called what comes in log?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Reus]

@Ryan S 

Can you please check the reference qualifier you giving. Shouldn't it be "current.variables.abc" ?

[Ryan S]

So @GopikaP's response fixed the issue and allows me to get the proper results. However, now that I get into testing I can find the requirement wasn't defined quite right. This whole logic needs to be applied only to certain Assignment_Groups. That's easy enough logically but I'm getting a compile error. Here's the updated code if folks don't mind taking one more pass. I'm thinking I've just got a bracket or semicolon or comma off somewhere. ServiceNow says error is at line 8 which is my commented line?!

var AssignedToReferenceQualifier = Class.create();
AssignedToReferenceQualifier.prototype = {
    initialize: function() {
    },
	getAssignedTo: function(myCompany, myDomain, myGroup) {
	//only apply to specific groups, for now just going based on certain value in Group Name field
	var gr = new GlideRecord('sys_user_group');
	gr.addQuery('sys_id',myGroup);
	gr.addQuery('name','CONTAINS',<search text goes here in single quotes>);
	gr.query();
	if(gr.next()) {
		//query the Group Visibility Domain table to find the Visibility Group that grants access to the company's domain
		var visibilityGroup = new GlideRecord('sys_user_group_visibility');
		visibilityGroup.addQuery('sys_domain', myDomain);
		visibilityGroup.addQuery('group.name', 'CONTAINS', myCompany);
		visibilityGroup.query();
		while (visibilityGroup.next()) {
			var groupSysID = visibilityGroup.group;
		}
		//now that we have the Visibility, we can filter the group membership table for the members of this group
		var userVisArray = [];
		var groupMembers = new GlideRecord('sys_user_grmember');
		groupMembers.addQuery('group',groupSysID);
		groupMembers.query();
		while(groupMembers.next()) {
			userVisArray.push(groupMembers.user.toString());
		}
		var membershipResults = [];
		var strLength = userVisArray.length;
		//now that we have members that are part of the Visibility group, confirm they are also part of the selected assignment group
		var userAssignArray = [];
		for(var i = 0; i < strLength; i++){
			var userID = userVisArray[i];
			var grMember = new GlideRecord('sys_user_grmember');
			grMember.addQuery('user',userID);
			grMember.addQuery('group',myGroup);
			grMember.query();
			while(grMember.next()){
				userAssignArray.push(grMember.user.toString());
			}
		}
		return 'sys_idIN' + userAssignArray.join(',');	
			else {
			var groupMembersArray = [];
			var grMembers = new GlideRecord('sys_user_grmember');
			grMembers.addQuery('group',myGroup);
			grMembers.query();
			while(grMembers.next()){
				groupMembersArray.push(grMembers.user.toString());
			}
			return 'sys_idIN' + groupMembersArray.join(',');
		}
	}
    },
    type: 'AssignedToReferenceQualifier'
};

[GopikaP · Accepted solution]

Hi @Ryan S Please find the updated code - 

var AssignedToReferenceQualifier = Class.create();
AssignedToReferenceQualifier.prototype = {
    initialize: function() {},
    getAssignedTo: function(myCompany, myDomain, myGroup) {
        //only apply to specific groups, for now just going based on certain value in Group Name field
        var gr = new GlideRecord('sys_user_group');
        gr.addQuery('sys_id', myGroup);
        gr.addQuery('name', 'CONTAINS', '');
        gr.query();
        if (gr.next()) {
            //query the Group Visibility Domain table to find the Visibility Group that grants access to the company's domain
            var visibilityGroup = new GlideRecord('sys_user_group_visibility');
            visibilityGroup.addQuery('sys_domain', myDomain);
            visibilityGroup.addQuery('group.name', 'CONTAINS', myCompany);
            visibilityGroup.query();
            while (visibilityGroup.next()) {
                var groupSysID = visibilityGroup.group;
            }
            //now that we have the Visibility, we can filter the group membership table for the members of this group
            var userVisArray = [];
            var groupMembers = new GlideRecord('sys_user_grmember');
            groupMembers.addQuery('group', groupSysID);
            groupMembers.query();
            while (groupMembers.next()) {
                userVisArray.push(groupMembers.user.toString());
            }
            var membershipResults = [];
            var strLength = userVisArray.length;
            //now that we have members that are part of the Visibility group, confirm they are also part of the selected assignment group
            var userAssignArray = [];
            for (var i = 0; i < strLength; i++) {
                var userID = userVisArray[i];
                var grMember = new GlideRecord('sys_user_grmember');
                grMember.addQuery('user', userID);
                grMember.addQuery('group', myGroup);
                grMember.query();
                while (grMember.next()) {
                    userAssignArray.push(grMember.user.toString());
                }
            }
            return 'sys_idIN' + userAssignArray.join(',');
        } else {
            var groupMembersArray = [];
            var grMembers = new GlideRecord('sys_user_grmember');
            grMembers.addQuery('group', myGroup);
            grMembers.query();
            while (grMembers.next()) {
                groupMembersArray.push(grMembers.user.toString());
            }
            return 'sys_idIN' + groupMembersArray.join(',');
        }

    },
    type: 'AssignedToReferenceQualifier'
};

 first return statement should be inside the 'if' block.