How to filter reference field based on a group that's selected in a choice field?

[iDevilsdocry]

Hello,

I have a catalog item with a choice field that loads a list of group/s based on logged in user group presence. (This isn't a reference field referencing groups table)

I have another item that IS a reference field that needs to point to the group that's selected in the first choice field and show all group members for that group. Each groups from the first field has an approval group and I'm referencing group members of that group.

I have looked at the advanced reference qualifier options and examples in community but I don't see how it'll fit my example because I need to be able to reference all groups (28 to be exact) just from that one field.

I know that changing the first field type to reference field make this a lot simpler but I cannot change anything about that.

Thanks for any info.

EDIT:

So I have found an alternative for this. Here is how it works but has a small problem....

So instead of having second field as a reference field, I made another field of same type as the first 'Select Box' with choices that auto populate with an OnChange client script I wrote below...

function onChange(control, oldValue, newValue, isLoading){
     if (isLoading || newValue == ''){
          return;
     }

var groupapproval = g_form.getValue('variables.approval_route');
//here approval_route is the name of the first select box.
//choice selected here isn't an actual group. So its just a randomly named choice.

if(groupapproval == 'approval_route choice')
{
     var users = new GlideRecord('sys_user_grmember');
     users.addQuery('group', 'sys_id_for_corresponding_group'); //sys_id_for_corresponding_group is the group that should be queried based on choice in first field
     users.query();

     while (users.next()){
//below 'approver' is a new field that I mentioned that I created as alternative to reference field
          g_form.addOption('variables.approver', users.user, users.user);
     }
}
}

So the problem with this is that it is grabbing sys_id from users table for each member in the group. I need their names. I'm pretty sure it has something to do inside what I have in the while loop but not sure what to change and what to change it to.

[Tim Provin]

The reference qualifier will be triggered whenever you interact with the reference field.  Meaning whenever you click the magnifying glass to select values, or start using the type ahead search, the reference qualifier is validating the values that are being entered.  That said, if you change the group the reference qualifier does not automatically clear the value from the reference field.  That would be handled in an onChange Client Script.  You script include would look something like the following (keep in mind you will need to populate your specifics into it)

var GroupMembershipUtils = Class.create();
GroupMembershipUtils.prototype = {
    initialize: function() {
    },

	approvalGroupMemberRefQual : function (grp) {
		var memArr = [];
		
		var users = new GlideRecord('sys_user_grmember');
		users.addQuery('group', grp);
		users.query();
		
		while (users.next()) {
			memArr.push(users.getValue('user'));
		}
		
		return 'sys_idIN'+memArr.join(',');
	},
	
    type: 'GroupMembershipUtils'
};

This is assuming a couple things.  

1. You are passing in the sys_id of the group that you are looking for the members of.
2. Your reference field is set to the User table

Your reference qualifier would look something like this.

new global.GroupMembershipUtils().approvalGroupMemberRefQual(group_sys_id);

The part that I do not know is how you are getting the sys_id of the group that you need.  If you have to do a look up from the group table based on the value of the choice variable, you could pass that variable to the script include, and then add another query to find the correct group.

[iDevilsdocry]

Is this what my client script would look like? 

function onChange(control, oldValue, newValue, isLoading) {
	if (isLoading || newValue == '') {
		return;
	}
	
	var ga = new GlideAjax('GroupMembershipUtils');
	var gs = g_form.getValue('variables.approval_route');
	
	if (gs == 'Legal - Litigation'){
		ga.addParam('sysparm_name', 'approvalGroupMemberRefQual'); //function name for script include
		ga.addParam('grp', 'acb45872db9d2b00542784eb0b96197b');
		ga.getXML(populateApprovers);
	}
	
	function populateApprovers(response){
		var answer = response.responseXML.documentElement.getAttribute("answer");
		//g_form.addOption('variables');
	}
}

I'm thinking about running that 'if' snippet for every choice in that dropdown.

I'm passing groupid on this line: 'ga.addParam('grp', 'acb45872db9d2b00542784eb0b96197b');' 

is that the correct way to do it?

[SanjivMeher]

Your 2nd field should reference sys_user_grmember with a reference qualifier 'group.name='+<name of the approval group>

[iDevilsdocry]

I'm not sure if this would work for my case because I have multiple approval groups. Currently I'm using an if condition that is checking value on the choice field and passing appropriate group id based on it. you can check my replies with Tim Provin

[iDevilsdocry]

So I have found an alternative for this. Here is how it works but has a small problem....

So instead of having second field as a reference field, I made another field of same type as the first 'Select Box' with choices that auto populate with an OnChange client script I wrote below...

function onChange(control, oldValue, newValue, isLoading){
     if (isLoading || newValue == ''){
          return;
     }

var groupapproval = g_form.getValue('variables.approval_route');
//here approval_route is the name of the first select box.
//choice selected here isn't an actual group. So its just a randomly named choice.

if(groupapproval == 'approval_route choice')
{
     var users = new GlideRecord('sys_user_grmember');
     users.addQuery('group', 'sys_id_for_corresponding_group'); //sys_id_for_corresponding_group is the group that should be queried based on choice in first field
     users.query();

     while (users.next()){
//below 'approver' is a new field that I mentioned that I created as alternative to reference field
          g_form.addOption('variables.approver', users.user, users.user);
     }
}
}

So the problem with this is that it is grabbing sys_id from users table for each member in the group. I need their names. I'm pretty sure it has something to do inside what I have in the while loop but not sure what to change and what to change it to.