Catalog Item Variable to Dynamically List Members of a Group

[fcaruso123]

We have a group defined that contains users who can approve the purchase of certain devices. The catalog item uses the requesting users organization value (from user record) to further filter the group members.

The Group Members variable is a reference type defined to pull from sys_user_grmember:

I see the following after selecting an Organization:

The number of records getting listed in Group Members is correct but I cannot figure out how to get the user name to display.

Thank you

[Brian Sorensen]

i think you have your Variable attribute backwards

Looking at all the ones I do something like this its 

ref_auto_completer=AJAXTableCompleter,ref_ac_columns=VARIABLES

So I would assume yours should be

ref_auto_completer=AJAXTableCompleter,ref_ac_columns=user.name 

[fcaruso123]

After reversing I get this:

And when I select the one item I get this:

[Brian Sorensen]

! Ok

I did this and looks like it worked

All > tables

Find the Sys_user_Grmember table

I noticed all are set to Display = false

Changed User to True

After doing that, I removed the attribute and then I see this now

I have not put an advance function in to filter, just wanted to see if I could get just the name

[Brad Bowman · Accepted solution]

A reference variable on the sys_user_grmember table is going to select a record on this table - which is just the user and group relationship.  You can change the Display column, but that will affect every appearance of the group member table system-wide, so you really don't want to do this as showing the value of the user column will not make sense outside of this reference variable.  What you really want to do is make this a reference to the sys_user table, then you will be selecting an actual user record which will display correctly, and you will be able to use the value in an approval, etc. - something you will not be able to do with a group member table record.

To filter the records to only show users who are members of a certain group, and your organization variable you will need a reference qualifier that looks more like this:

Where groupUtils (or whatever you want to name it) is the name of a Script Include, and getGrpMbr a function, passing in the name of a group.  If your organization variable is a reference, and the u_organization field on your user table is a reference, there's no need to dot-walk to name on each in the qualifier.  The Script Include will look like this:

var groupUtils = Class.create();
groupUtils.prototype = {
	initialize: function() {
	},
	
	getGrpMbr : function(grpname){
		var usrArr = [];
		var grp = new GlideRecord('sys_user_grmember');
		grp.addQuery('group.name', grpname)
		grp.query();
		while (grp.next()) {
			usrArr.push(grp.user.toString());
		}
				
		return 'sys_idIN' + usrArr.join(',');
	},

    type: 'groupUtils'
};