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

[Vishal Jaswal]

Hello @Ryan S 

updated ref qual:

javascript: new AssignedToReferenceQualifier().getAssignedTo('company_sys_id', 'domain_sys_id', 'group_sys_id');

Updated Script Include:

var AssignedToReferenceQualifier = Class.create();
AssignedToReferenceQualifier.prototype = {
initialize: function() {},
getAssignedTo: function(myCompany, myDomain, myGroup) {
var userAssignArray = [];

// Query Group Visibility Domain table
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.toString();

// Query Group Membership
var groupMembers = new GlideRecord('sys_user_grmember');
groupMembers.addQuery('group', groupSysID);
groupMembers.query();

while (groupMembers.next()) {
var userID = groupMembers.user.toString();

// validate user is part of assignment group
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'
};

[GopikaP]

Hi @Ryan S , I see that you are passing company sys id, from the reference qualifier call and, in the script include - 

visibilityGroup.addQuery('group.name', 'CONTAINS', myCompany); // here myCompany variable contains sys_id of the company, but you are quering name here

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); // here myCompany variable contains sys_id, but you are quering name here
    visibilityGroup.query();
    while (visibilityGroup.next()) {
		var groupSysID = visibilityGroup.group;

[Ryan S]

That was it! Thank you.

[GopikaP]

If you found my answer helpful, please consider marking it as correct.