How can I set listCollector options based on reference field selection?

nicolemccray · ‎09-06-2017

I have a table named 'u_database_roles'. There are 3 columns in this table that correspond to fields in my Catalog form:

Column name in table = 'u_system' and field name = 'server_name'

Column name in table = 'u_database' and field name = 'db_name'

Column name in table = 'u_role' and field name = 'role_names'

Server name is a reference field. Depending upon what is selected there, I want the other 2 fields (both list collectors) to only display database names and role names that are associated with the server name in the table.

Would it be best to do this as a reference qual on the listCollector variables? Or as an onChange client script? Can anyone provide me with some help with how to script this?

Thank you.

nicolemccray · ‎09-07-2017

Now, all I want to do is populate the 2nd list collector (role_names) based on the selection(s) in the first list collector (db_name). Both of these reference a table (u_database_roles). I have an onChange script, but not sure if I modified correctly for my fields. Can anyone help?

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}

//Apply a default filter to the list collector variable

var filterString;

var collectorName = 'role_names';

var dbchosen = [];

var db= new GlideRecord('u_database_roles');

dbchosen = g_form.getValue('db_name').split(',');

//alert(dbchosen); // this returns sys ids of the chosen db

filterString = 'db_name.sys_id=' + dbchosen[0];

if(dbchosen.length>1){

for (var i=1;i<dbchosen.length;i++){

filterString += '^ORdb_name.sys_id=' + dbchosen[i];

}

//Hide the list collector until we've set the filter

g_form.setDisplay(collectorName, false);

setCollectorFilter();

function setCollectorFilter(){

//Test if the g_filter property is defined on our list collector.

//If it hasn't rendered yet, wait 100ms and try again.

if(typeof(window[collectorName + 'g_filter']) == 'undefined'){

setTimeout(setCollectorFilter, 100);

return;

}

//Find the filter elements

var fil = gel('ep');

//Hide the filter elements by un-commenting the following lines

//Reset the filter query

window[collectorName + 'g_filter'].reset();

window[collectorName + 'g_filter'].setQuery(filterString);

window[collectorName + 'acRequest'](null);

//Redisplay the list collector variable

g_form.setDisplay(collectorName, true);

}

tiyasa · ‎09-07-2017

hi Nicole,

I have done something similar for my item.

Based on the value of reference field , the List collector will filter related values only.

I have two catalog client scripts

1. OnLoad : just loads all active records.

function onLoad() {

//g_form.setDisplay('delegate_users_not_selected',false);

var collectorName = 'delegate_roles_user';

var filterString = 'active=true'; // removed roles = itil to allow all users even without a role.

setCollectorFilter();


function setCollectorFilter(){

if(typeof(window[collectorName + 'g_filter']) == 'undefined'){

setTimeout(setCollectorFilter, 500);

return;

}


//Reset the filter query

window[collectorName + 'g_filter'].reset();

window[collectorName + 'g_filter'].setQuery(filterString);

window[collectorName + 'acRequest'](null);

//Redisplay the list collector variable

//g_form.setDisplay(collectorName, true);

}

}

2. OnChange of the Group reference field - Loads related records on change of the group. It also sorts the left and right bucket values based on which users in that group have specific role on the right and the ones that dont on the left. May be a little too much for what you need.

function onChange(control, oldValue, newValue, isLoading) {

if (newValue == '' || oldValue == newValue || isLoading) {

return;

}


var group = g_form.getReference('gm_poc_group_to_modify');

g_form.setValue('gm_poc_group_manager', group.manager);

g_form.setValue('gm_poc_group_name', group.name);


var collectorName = 'gm_poc_group_members_list';

var leftBucket = gel(collectorName + '_select_0');

var rightBucket = gel(collectorName + '_select_1');


setCollectorFilter(collectorName,'active=true',1000); // Removed roles = itil to allow users with all or no roles.


//Values currently found in database

var ids = [];

var names = [];

var gr = new GlideRecord('sys_user_grmember');

gr.addQuery('group', newValue);

gr.query();

while (gr.next()) {

var users = new GlideRecord('sys_user');

users.addQuery('sys_id', gr.user);

users.query();

while(users.next()){

ids.push(gr.user);

names.push(users.name);

}

}

//It is possible that some/all group members are not present on the left side because ServiceNow will only show the first 100 users

var checked;

//Go through all group members found in your database

for (var i = 0; i < ids.length; i++){

checked = false;

//Go through all users available on the left side

for (var h = 0; h < leftBucket.length; h++){

//When equal mark the leftside user as selected and the group member as found

if(ids[i] == leftBucket.options[h].value) {

checked = true;

leftBucket.options[h].selected = true;

break;

}

}

//When group member is not found add it as an option to the left side and mark it as selected

//Here is where we need the display value for, we don't want to do an extra server side request

if (!checked) {

h++;

leftBucket[h] = new Option(names[i], ids[i]);

leftBucket.options[h].selected = true;

}

}

rightBucket.options.length = 0;

//Now move the selected users from left to right and sort

moveOptionAndSort(leftBucket, rightBucket, '—None—', [], '—None—');

//Deselect all users now available on the right side to show a clean start

for (i = 0; i < rightBucket.length; i++){

rightBucket.options[i].selected = false;

}

}



function setCollectorFilter(collectorName, filterString, timeout) {

//Test if the g_filter property is defined on our list collector.

//If it hasn't rendered yet, wait and try again.

if (eval('typeof(' + collectorName + 'g_filter)') == 'undefined' ) {

setTimeout(function() { setCollectorFilter(collectorName, filterString, timeout); }, timeout);

return;

}

//Set the filter query

window[collectorName + 'g_filter'].reset();

window[collectorName + 'g_filter'].setQuery(filterString);

window[collectorName + 'acRequest'](null);

}

nicolemccray · ‎09-07-2017

Thank you for the reply. I'm not a proficient scripter; can you provide the onChange script you would use for my fields? I don't have a reference field, just the 2 list collectors.

nicolemccray · ‎09-08-2017

I made an attempt at modifying to fit my fields, but not working. Table being referenced in listCollector - u_database_roles

Need to load related records on change of - field name 'db_name' column name in table 'u_database'

Need to return list of roles related to DB selection - field name 'role_names' column name in table 'u_role'

Can you help me modify to get the results I need?:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}

//Type appropriate comment here, and begin script below
var collectorName = 'role_names';
var leftBucket = gel(collectorName + '_select_0');
var rightBucket = gel(collectorName + '_select_1');

setCollectorFilter(collectorName,'active=true',1000); // Removed roles = itil to allow users with all or no roles.

//Values currently found in database
var ids = [];
var names = [];
var gr = new GlideRecord('u_database_roles');
gr.addQuery('db_name', newValue);
gr.query();
while (gr.next()) {
var roles = new GlideRecord('u_role');
roles.addQuery('sys_id', gr.role);
roles.query();
while(roles.next()){
ids.push(gr.role);
names.push(roles.name);
}
}

//Here is where we need the display value for, we don't want to do an extra server side request
if (!checked) {
h++;
leftBucket[h] = new Option(names[i], ids[i]);
leftBucket.options[h].selected = true;
}
}
rightBucket.options.length = 0;
//Now move the selected users from left to right and sort
moveOptionAndSort(leftBucket, rightBucket, '—None—', [], '—None—');
//Deselect all users now available on the right side to show a clean start
for (i = 0; i < rightBucket.length; i++){
rightBucket.options[i].selected = false;
}

function setCollectorFilter(collectorName, filterString, timeout) {
//Test if the g_filter property is defined on our list collector.
//If it hasn't rendered yet, wait and try again.
if (eval('typeof(' + collectorName + 'g_filter)') == 'undefined' ) {
setTimeout(function() { setCollectorFilter(collectorName, filterString, timeout); }, timeout);
return;
}
//Set the filter query
window[collectorName + 'g_filter'].reset();
window[collectorName + 'g_filter'].setQuery(filterString);
window[collectorName + 'acRequest'](null);

}