Ensuring unique values in a table

[Mark251]

So I have been trying to do this for some time now without success, can someone please help me.

I have created a custom application and table called "Approval Settings" which has many columns but the one I am focused on is the one called "Group". This column is a reference to the sys_user_group table and has a few filters based on tags associated to the groups.

What I am trying to achieve is when a person adds a new entry in to this table (see attached screenshot) it only shows the selection from the group table minus what is already in the custom table. E.g. If there is group A, B and C in the group table and Group B is already in the custom table, the pulldown list only shows Group A and C.

I have tried using reference qualifiers but could not get it working.

[Shane41 · Accepted solution]

Hi Mark,

I would recommend creating a script include that would query all the records in your custom approval table and grab all the assignment group values, you could then query the groups table to find all groups that don't include the values you grabbed from the custom table, see example below

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

getAvailableGroups: function(){

groupsInUse = [];
availableGroups = [];
	
var approval_groups = new GlideRecord('incident');
	approval_groups.query();
	
	while(approval_groups.next()){
	groupsInUse.push(approval_groups.assignment_group.toString());
	}
	
	var grp = new GlideRecord('sys_user_group');
	grp.addEncodedQuery('sys_idNOT IN' + groupsInUse);
	grp.query();
	
	while(grp.next()){
	availableGroups.push(grp.sys_id.toString());	
	}
	return 'sys_idIN' + availableGroups.toString();	
},
	
    type: 'checkApprovalSettingsGroup'
};

You will need to replace incident with your table name and the assignment_group field with the name of your field (if different)

You would call this in your reference qualifier like this

You may have to click advanced view under related link to enter this

Hope this helps

Shane

[Mark251]

Thanks Shane, this makes sense but when I implement it, it shows all the active groups, not just the ones that are not in the table.

Custom Tablename = x_pjtp_pjt_auto_approval_settings

Custom Table column = group (reference to sys_usr_group table)

I added one additional query to ensure that it only shows the groups with a tag

              "Approval Group" with a sys_id of 1b8fd1a81b7e0d500521baebcc4bcb37

Here is the code I added:

var checkApprovalSettingsGroup = Class.create();
checkApprovalSettingsGroup.prototype = {
initialize: function() {
},
getAvailableGroups: function(){
groupsInUse = [];
availableGroups = [];

var approval_groups = new GlideRecord('x_pjtp_pjt_auto_approval_settings');
approval_groups.query();

while(approval_groups.next()){
groupsInUse.push(approval_groups.group.toString());
}

var grp = new GlideRecord('sys_user_group');
grp.addEncodedQuery('typeLIKE1b8fd1a81b7e0d500521baebcc4bcb37');
grp.addEncodedQuery('sys_idNOT IN' + groupsInUse);
grp.query();

while(grp.next()){
availableGroups.push(grp.sys_id.toString());
}
return 'sys_idIN' + availableGroups.toString();
},
type: 'checkApprovalSettingsGroup'
};

And I added this reference qualifier

javascript: new checkApprovalSettingsGroup().getAvaliableGroups()

[Mark251]

Could not get it to work initially but with some slight modifications, it works like a treat, thanks