Dot walking a reference qualifier for a variable lookup

[tcorniels]

I am working on a form that has a variable that needs to be able to pull a choice list from a group within a table.

I figure I need to use an advanced reference qualifier but not finding a correct way to get what I need.  I have tried doing it by making the variable both a reference field and a lookup with no luck.

Variable: Approving Manager

Type: Lookup Select Box

Lookup From Table: sys_user_group

Lookup Field Value: Name {but I don't want the group name, I want the members within a specific group called TS Managers}

Reference Qual: {trying to figure out what I need here to get the names from a specific group within the table}

Or am I going about this all wrong?  

[ryanlitwiller · Accepted solution]

You will have to change the lookup table to sys_user, then you can either:

1. create a script include to query from, as shown in this thread
2. Use below in your reference qualifier

javascript:var gr = new GlideRecord('sys_user_grmember');gr.addQuery('group',"<TS Managers sys_id>");gr.query();var users='';while(gr.next()){users+=gr.user.sys_id + ",";}"sys_idIN" + users;

^^This solution was pulled from the thread linked also, however, its not the accepted solution. I've used this solution myself since it doesn't require creating a script include.

[tcorniels]

Worked like a champ!  Thank you!

[Tony DiRienzo]

You need to use a scripted reference qualifier in order to do this, because you want to be able to select a User reference based on Group Membership, and these are separate tables.

Begin by creating a Script Include that you can call in the reference qualifier.  Here is an example that should work for you:

var GetMembers = Class.create();
GetMembers.prototype = {
    initialize: function() {	
    },
	
	asQueryString: function(group) {
		var ga = new GlideAggregate('sys_user_grmember'); // GlideAggregate is faster than GildeRecord
		ga.groupBy('user'); // This will de-duplicate results
		ga.addQuery('group', group);
		ga.query();
		
		var users = [];
		while (ga.next()) {
			users.push(ga.user.toString());
		}
		return 'sys_idIN' + users.toString();
	},
	
    type: 'GetMembers'
};

Now you will need to edit the dictionary entry for your Approving Manager field.  Use these settings:

Reference: User (sys_user)

User reference qualifier: Advanced

Reference qual: javascript:new GetMembers().asQueryString('sys_id of your group goes here')

Save the changes, reload the form, and your field should work as desired.