Preventing duplicate when creating records in sys_user_grmember

[sethhumphrey]

I have a catalog item to request additions and deletions to ServiceNow groups.  Flow Designer handles the adds and deletes after group manager approval perfectly; however, duplicate additions pass are accepted.

Option 1) Remove existing group member user records from selection reference field (person_add). I wasn't sure where to start with this option.  This would be ideal if you have a solution.

Option 2) Create a Catalog Client Script to prevent duplicates from being submitted.  Attempt of this (below) is not working.  What am I missing?

Catalog Item

Variables

• u_group is a reference to [sys_user_group]
• person_add is a reference to [sys_user]

Catalog Client Script

function onSubmit() {

    g_form.clearMessages();
	
    var grp = g_form.getValue('u_group');	
    var usr = g_form.getValue('person_add');
	
    // IF adding a group member
    if (g_form.getValue('type_of_group_change') == 'add') {
        var gr = new GlideRecord('sys_user_grmember');
        gr.addQuery("group", grp);
        gr.addQuery("user", usr);
        gr.query();
        while (gr.next()) {
            g_form.addErrorMessage('Person is already in the group.');
            return false;
        }
    }
}

[Pradeep Sharma]

Hello Sethhumphrey,

1. This can be done via a Reference qualifier.

2. Modified CS below. Could  you please put alert statement and share the results here.

var grp = g_form.getValue('u_group');

alert(grp);

var usr = g_form.getValue('person_add');

alert(usr);

- Pradeep Sharma

[sethhumphrey]

Updated Client Script

Group Alert

This does correspond to the sys_id of the "Web Support" group that was selected.

User Alert

This does correspond to the sys_id of the User selected.

[Jim Coyne · Accepted solution]

Pradeep beat me to it with the Reference Qualifier idea.  Basically, this will allow you to remove the current group members from the "person_add" variable so you cannot have duplicates and only show group members in the "person_remove" variable so you can't select someone who is not already in the group.

Follow these steps:

1. create a new Script Include
Name: uSCRefQualifiers (or whatever you want to call it)
Client callable: NOT checked
Accessible from: All application scopes
Script:

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

    groupMembers: function(groupId, joiner) {
        var member = [];

        var gr = new GlideRecord("sys_user_grmember");
        gr.addEncodedQuery("group=" + groupId);
        gr.query();
        while (gr.next()) {
            member.push(gr.getValue("user"));
        }

        return member.join(joiner);
    },

    type: 'uSCRefQualifiers'
};

2. for the "person_remove" variable, set the  "Use reference qualifier" field to "Advanced" and put the following in the "Script" field:

javascript:"sys_idIN" + new global.uSCRefQualifiers().groupMembers(current.variables.group.toString(), ",");

3. for the "person_add" variable, same thing, but use the following script:

javascript:"sys_id!=" + new global.uSCRefQualifiers().groupMembers(current.variables.group.toString(), "^sys_id!=");

[sethhumphrey]

Thanks Jim.  I am using a Ref Qualifier for person_remove that references [sys_user_grmember]:

javascript:'group='+current.variables.u_group

Would you recommend using your method more than this approach?

Reference qual to Script Include is certainly the method I'd like to use and will probably go with but the fact the Catalog Client Script isn't working is driving me nuts.