Get User Names from List Collector using Script Includes

[litchick10]

I have a list collector on my service catalog form that references the sys_user table. I want to pass the user names from the list collecter (stakeholders) to a string field (stakeholders_list). 

I am trying to use a script includes to query the sys_user table for name field but need help writing the catalog client script to call the script includes:

Table data query script include
//Get Data from a service now table.

//parameters:
//sysparm_query_table : name of tabel
//sysparm_query_encodedQuery : encoded query to run on the table
//sysparm_query_fields : fields to return from table
//sysparm_query_order: field to order data by


var TableDataQuery = Class.create();
TableDataQuery.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getList: function () {
        var table = this.getParameter('sysparm_query_table');
        var encodedQuery = this.getParameter('sysparm_query_encodedQuery');
        var propertiesString = this.getParameter('sysparm_query_fields');
		var orderBy = this.getParameter('sysparm_query_order');
        var request = new GlideRecord(table);
        request.addEncodedQuery(encodedQuery);
		request.orderBy(orderBy);
        request.query();
        var list = [];
        while (request.next()) {
            if (propertiesString != null && propertiesString != "") {
                var responseObject = {};
                var properties = propertiesString.split(",");
                for (var i = 0; i < properties.length; i++) {
                    gs.print(properties[i]);
                    responseObject[properties[i]] = request[properties[i]].toString();
                }
                list.push(responseObject);
            } else {
                list.push(request);
            }          
        }
        return JSON.stringify(list);
    },
    type: 'TableDataQuery'
});

Here is what I have so far - wrote comments where I am asking for help:

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    var liststr = g_form.getValue('stakeholders').toString();
    var listarray = liststr.split(',');
    for (index = 0; index < listarray.length; index++) {
        //query script includes Table Data Query
        var ga = new GlideAjax('TableDataQuery');
        ga.addParam('sysparm_name', 'getList');
        ga.addParam('sysparm_query_table', 'sys_user');
        ga.addParam('sysparm_query_fields', 'sys_id,name'); //not sure if this is supposed to be seperated by comma or something else
        ga.addParam('sysparm_query_encodeQuery', "'sys_id'=" + listarray); //not sure if this right
        ga.getXML(populateValues);
    }
    function populateValues(response) {
        var answer = response.responseXML.documentElement.getAttribute("answer");
        answer = JSON.parse(answer);
        g_form.setValue('stakeholders_list', g_form.getValue('stakeholders_list') + answer.list.name + ', '); //not sure how to pull out the field value I wanted example: name
    }
}

[Anil Lande]

Hi,

code and glideAjax syntax looks good. There are few observations:

1. In your script include var orderBy = this.getParameter('sysparm_query_order');  you have to pass this 'sysparm_query_order' attribute from your client script as well like ga.addParam('sysparm_query_order', 'name');

2. As you are only interested in Name of users the no need to create object in your script include. Just declare variable  var responseObject = []; in the beginning and replace this line responseObject[properties[i]] = request[properties[i]].toString(); with responseObject.push(request[properties[i]].toString());   

3. In your client script at this line ga.addParam('sysparm_query_fields', 'sys_id,name'); //not sure if this is supposed to be seperated by comma or something else  No need to pass sys_id as you need user names only.   ga.addParam('sysparm_query_fields', 'name'); //this is also fine

4. Please correct this line  ga.addParam('sysparm_query_encodeQuery', "'sys_id'=" + listarray); //not sure if this right it should be  ga.addParam('sysparm_query_encodeQuery', "'sys_idIN'=" + listarray); //sys_idIN

5. in your script include code user return list.toString(); to return your result.

6. In your callback function in client script the code should be:

var answer = response.responseXML.documentElement.getAttribute("answer");

g_form.setValue('stakeholders_list',answer);

You have written very complex code for very simple task, hope making above changes will resolve your issue.

Please mark helpful/correct answer if this helps. 

Thanks,

Anil Lande

[litchick10]

For your #2 above - I want this script include to be usable for any table in the future so I'd like to keep in the code that lets me pick a field. I changed everything else and the results are [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object  ... continues forever

To be sure I didn't mess up somewhere Here is script include & client script as they are now

Script include:

//Get Data from a service now table.

//parameters:
//sysparm_query_table : name of tabel
//sysparm_query_encodedQuery : encoded query to run on the table
//sysparm_query_fields : fields to return from table
//sysparm_query_order: field to order data by


var TableDataQuery_one = Class.create();
TableDataQuery_one.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getList: function () {
        var table = this.getParameter('sysparm_query_table');
        var encodedQuery = this.getParameter('sysparm_query_encodedQuery');
        var propertiesString = this.getParameter('sysparm_query_fields');
		var orderBy = this.getParameter('sysparm_query_order');
        var request = new GlideRecord(table);
        request.addEncodedQuery(encodedQuery);
		request.orderBy(orderBy);
        request.query();
        var list = [];
        while (request.next()) {
            if (propertiesString != null && propertiesString != "") {
                var responseObject = {};
                var properties = propertiesString.split(",");
                for (var i = 0; i < properties.length; i++) {
                    gs.print(properties[i]);
                    responseObject[properties[i]] = request[properties[i]].toString();
                }
                list.push(responseObject);
            } else {
                list.push(request);
            }          
        }
//       return JSON.stringify(list);
         return list.toString();
    },
    type: 'TableDataQuery_one'
});

Client Script:

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    var liststr = g_form.getValue('stakeholders').toString();
    var listarray = liststr.split(',');
    for (index = 0; index < listarray.length; index++) {
        //query script includes Table Data Query
        var ga = new GlideAjax('TableDataQuery_one');
        ga.addParam('sysparm_name', 'getList');
        ga.addParam('sysparm_query_table', 'sys_user');
        ga.addParam('sysparm_query_fields', 'name');
        ga.addParam('sysparm_query_encodeQuery', "'sys_idIN'=" + listarray); //sys_idIN
        ga.addParam('sysparm_query_order', 'name');
        ga.getXML(populateValues);
    }

    function populateValues(response) {
        var answer = response.responseXML.documentElement.getAttribute("answer");
        g_form.setValue('stakeholders_list', answer);

    }
} 

[Brad Bowman · Accepted solution]

You want to minimize your trips to the server and back, just as you would trips to the grocery store.  Send liststr as a parameter, and forget the array and for loop in the client script.  You can send table, query etc. to make this reusable, or just hard-code these values in the SI.  Your SI is also overcomplicated, as you really just need an array of names since you already have sys_ids, then your setValue is just answer.  So here's how I would simplify it (untested).

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    var liststr = g_form.getValue('stakeholders').toString();
    //query script includes Table Data Query
    var ga = new GlideAjax('TableDataQuery');
    ga.addParam('sysparm_name', 'getList');
    ga.addParam('sysparm_query_table', 'sys_user');
    ga.addParam('sysparm_query_encodeQuery', "'sys_idIN" + liststr + "'");
    ga.getXML(populateValues);

    function populateValues(response) {
        var answer = response.responseXML.documentElement.getAttribute("answer");
        g_form.setValue('stakeholders_list', answer);
    }
}

var TableDataQuery = Class.create();
TableDataQuery.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getList: function (){
        var table = this.getParameter('sysparm_query_table');
        var encodedQuery = this.getParameter('sysparm_query_encodedQuery');
        gs.addInfoMessage(encodedQuery);//temporary, to confirm correct query is received
        var request = new GlideRecord(table);
        request.addEncodedQuery(encodedQuery);
        request.query();
        var list = [];
        while(request.next()){
          list.push(request.name.toString());
        }
        return list.join(',');
    },
    type: 'TableDataQuery'
});

If something isn't working right, add more infoMessages and/or client alerts to find out what is stored, which steps are reached, etc.

[litchick10]

This returned values but they are wrong: