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
    }
}

[Community Alums]

change the line below

var encodedQuery = this.getParameter('sysparm_query_encodedQuery');

to 

var encodedQuery = this.getParameter('sysparm_query_encodeQuery');

there was an extra "d" in encodeQuery

[litchick10]

Still getting the long list of wrong names. Here is the code as is to make sure I didn't copy incorreclty:

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);
    }
}

Script Include:

var TableDataQuery = Class.create();
TableDataQuery.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getList: function (){
        var table = this.getParameter('sysparm_query_table');
        var encodedQuery = this.getParameter('sysparm_query_encodeQuery');
        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'
});

[Brad Bowman]

When a GlideRecord encounters an error or unknown value in an addQuery type line it just ignores it, so you get every value, which is fun.  Is your info message on encodedQuery showing something like this?  If so, try removing the single quotes from the string you are creating in the client script.

'sys_idIN62826bf03710200044e0bfc8bcbe5df1,a8f98bb0eb32010045e1a5115206fe3a'

[litchick10]

SUCCESS!  Thank so much for all your help

[Brad Bowman]

You are welcome.  Here's the working version closer to your original intent - passing more sysparms from the client so that you can more easily re-use, and more robust by returning multiple values even though only one is needed in this case.  I'm sure there's a better way of deciphering the array of objects on the client, but this what I got to work.

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    var liststr = g_form.getValue('stakeholders').toString();
    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');
    ga.addParam('sysparm_query_encodedQuery', "sys_idIN" + liststr);
	ga.addParam('sysparm_query_order', 'name');
    ga.getXML(populateValues);
    
    function populateValues(response) {
		var names = [];
        var answer = response.responseXML.documentElement.getAttribute("answer");
		answer = answer.replace(/},{/g, '}},{{');
		var answerArr = answer.split('},{');
		for(var i=0;i<answerArr.length;i++){
			var answerObj = JSON.parse(answerArr[i]);
			names.push(answerObj.name.toString());
		}
		g_form.setValue('stakeholders_list', names.join(','));
    }
}

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 = [];
		var json = new JSON;
		var data = '';
        while (request.next()) {
            if (propertiesString != null && propertiesString != "") {
                var responseObject = {};
                var properties = propertiesString.split(",");
				for (var i = 0; i < properties.length; i++) {
					responseObject[properties[i]] = request[properties[i]].toString();
                }
				data = json.encode(responseObject);
                list.push(data);
			} else {
				data = json.encode(request);
                list.push(data);
            }          
        }
				
        return list.join(',');
    },
    type: 'TableDataQuery'
});