How to prevent duplicate entries in List Collector Reference Variable in a Catalog Item?

[Raza156]

Hey guys,

I made a catalog item which is as follows. I added two List Collector Reference Variables. One for Add Users called "Select Users to Add" and one for Remove Users. My goal is to make it such that when a someone fills out the form and selects one or more users to add, those specific users are to be stored in a table and cannot be added again until they are removed from the table (for which I have a Remove Users List Collector Reference Variable)

Side Details:

I have a table called  "Office Table" in which I added a reference column called "Add Users" where I want the users to be added to. 

I have a script include as follows:

var GetOffice365Users = Class.create();
GetOffice365Users.prototype = Object.extendsObject(AbstractAjaxProcessor, {

//think about handeling NULL values; array != '', return;


addUsers : function(){
var found = false;
var result = '';
var list = gs.getParameter('sysparm_users');
var array = (list+'').split(','); //parse list collector
var len = array.length;
for(var i = 0; i < len; i++){
var gr = new GlideRecord("u_office_365_users");
if(gr.get('u_add_user', arr[i])){
found = true;
result += gr.u_add_user.name +' ';
//alert('This user has already been added to the list');
//remove user in client script callback
}
}
return result;
},


officeAdd: function(current){
//var filter = "active=true^nameISNOTEMPTY^managerISNOTEMPTY^u_upgrade_date!=NULL";
var filter = "active=true^name!=NULL^manager!=NULL";//^u_upgrade_date=NULL";
return filter;
},

officeRem: function(current){
var remove = "active=true^name!=NULL^manager!=NULL";//^u_upgrade_date!=NULL";
return remove;
},

type: 'GetOffice365Users'
});

I have a Catalog Client Script as Follows:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}
//Type appropriate comment here, and begin script below
var ga = new GlideAjax('GetOffice365Users');
ga.addParam('sysparm_name', 'addUsers');
ga.addParam('sysparm_users', newValue);
ga.getXML(callBack);

function callBack(response){
var isTaken = response.responseXML.documentElement.getAttribute('answer')+'';

if(isTaken != ''){
alert('Sorry but the user ' + newValue.getDisplayValue() + ' is already in the list');
//alert("Sorry but the user '"+newValue+"' has already been added");
//g_form.clearValue("officeListCAddUsers");
g_form.setValue('officeListCAddUsers', '');
}
}

}

And the form is as follows:

[ChrisBurks]

I would add a reference qualifier on the Add User list collector to only display users that don't exist on the Office Table. This way the user can't pick them twice.

And the Remove User list collector should just be pulling from the Office Table which by default would only display users that can be removed from that table. 

[Raza156]

Hey Chris,

I'm new to ServiceNow so can you explain your answer a bit more? 

I have already added the following reference Qualifier in the Add User List Collector:

javascript: new GetOffice365Users().officeAdd(current)

The script include function that the reference qualifier calls is:

officeAdd: function(current){
//var filter = "active=true^nameISNOTEMPTY^managerISNOTEMPTY^u_upgrade_date!=NULL";
var filter = "active=true^name!=NULL^manager!=NULL";//^u_upgrade_date=NULL";
return filter;
},

I'm assuming I make changes to the filter? What do I change? Like what do I write? 
Would the following work: ^name!=u_office_365_users    

u_office_365_users is the back-end name of my table.

Also I have a workflow which contains two separate run scripts for when a User is Added and When a user is removed.

When I user is added, the following runs:

var array = (current.variables.officeListCAddUsers+'').split(','); //parse list collector
var len = array.length;
for(var i = 0; i < len; i++){
var grAddUser = new GlideRecord('u_office_365_users');
grAddUser.initialize();
grAddUser.setValue('u_add_user',array[i]);
grAddUser.setValue('u_requested_items',current.sys_id);
grAddUser.insert();
}

When I user is removed, the following runs:

var array = (current.variables.officeListRemoveUsers+'').split(','); //parse list collector
var len = array.length;
for(var i = 0; i < len; i++){
var grUser = new GlideRecord('u_office_365_users');
if(grUser.get(array[i])){
grUser.deleteRecord();
}
}

What do I need to change?

Thank you!

[ChrisBurks · Accepted solution]

From my previous post I'm assuming that the add user list collector is referencing a different table than the table that the user needs to be added. 

For example, if I need to pull users from the sys_user table to add to the u_office_365_user table my setup would be as follows.

Variable item Add User list collector would reference the sys_user table.

The reference qualifier would be javascript: new GetUsersToAdd().filterOfficeUsers();

Script Include would like this:

var GetUsersToAdd = Class.create();
GetUsersToAdd.prototype = {
    initialize: function() {
    },
	
	filterOfficeUsers: function(){
                //Gets the list of users already added to the u_office_365_user table
		var officeUsers = new GlideRecord('u_office_365_user');
		officeUsers.query();
		var officeUsersList = [];
		while(officeUsers.next()){
			officeUsersList.push(officeUsers.getValue('u_user'));
		}
		
                //Returns an encoded query to NOT include any users matching from
                //u_office_365_user table
		return "sys_idNOT IN" + officeUsersList.join(",");
	},

    type: 'GetUsersToAdd'
};

The Remove List collector would simply reference the u_office_365_user table because this list would only contain users that can be removed from that table; no reference qualifier needed.

[Raza156]

Thank you so much Chris!

I got it to work!

Have a great day!