Handy script to get record producer variables

[SanjivMeher]

Hello all,

I have been working on several record producer requirements, where in lot of cases we need all the details entered by the end user in a text format. Sometimes in a description of the target record or in an email sent to user/approver or to a 3rd party integration.

As we know, all the response from the end user using record producers are stored in a table called 'question_answer'. We can easily query this table to get the response of the end user and put it in the ticket description or an email or can post to another system. The tricky part is when the questions are reference variable or choice variables.

Because question_answer table only stores the value, incase of reference variable, it will be a sys_id and in case of a dropdown/choice field, it will be the value.

While I was able to find few script to get the description, there was no perfect answer to get the display value of a reference field or to get the label of a dropdown. Which is why I decided to build a script, which would work for all my record producers and can help you to use in your projects.

You can use the below script in a before business rule of the target record(incident, policy exception etc) or in email script to get all the values. 

    var desc = '';    
    var qa = new GlideRecord('question_answer');
    qa.addQuery('table_sys_id', current.sys_id);
    qa.orderBy('order');
    qa.query();
    while (qa.next()) {
        var variableValue = qa.value.getDisplayValue();

        if (variableValue!='' && qa.question.type == '8') {
            //If question type is 8, it is a reference variable. Get the display value from target table
            var ref = new GlideRecord(qa.question.reference);
            if (ref.get(variableValue)) {
                variableValue = ref.getDisplayValue();
            }
        }

        if (variableValue != '' && (qa.question.type == '21')) {
            //check to see if 'value' is the sys_id for List collector
            var users = variableValue.split(',');
            var usersList = '';
            for (var i = 0; i < users.length; i++) {
                var ulist = new GlideRecord(qa.question.list_table);
                if (ulist.get(users[i])) {
                    if (!usersList)
                        usersList = ulist.getDisplayValue();
                    else
                        usersList = usersList + "," + ulist.getDisplayValue();
                }
            }
            variableValue = usersList
        }

        if (variableValue!='' && qa.question.type == '3') {
            //If question type is 3, it is a choice variable. Get the label from the question_choice table.
            var ch = new GlideRecord('question_choice');
            ch.addQuery('question',qa.getValue('question'));
	    ch.addQuery('value',qa.getValue('value'));
            ch.query();
            if (ch.next()) {
				variableValue = ch.getValue('text');
            }
        }
	if (variableValue=='false' && qa.question.type == '7') { //For checkbox, exclude values unchecked
		variableValue = '';
	}

	if (qa.question.type == '11') // Print Label
		desc += '<b>'+qa.question.getDisplayValue()+'</b> :<br>';
	else if (qa.question.type == '7' && variableValue!='') // Print Checkboxes
		desc += qa.question.getDisplayValue()+'<br>';
	else if (variableValue!='')
		desc += qa.question.getDisplayValue() + " :\n" + variableValue + "\n\n"; 
    }
    current.desciption = desc; // set the description field with all the question and response from end users

If you also have a Multi row variable set, you can use below logic to add it to your description. In my case, the description field is an HTML field, so I am creating an html table to show the MRVS in the description.

    var tableHeader = '';
    var mrvs = {};
    mrvs = JSON.parse(current.variables.vs_set);

    if (mrvs.length > 0) {
        tableHeader = '<b>MRVS Name:<br></b><table style="border-collapse: collapse; width: 100.037%;" border="1"><tbody>';
        var tableBody = '';
        // Get Labels
        for (i = 0; i < mrvs.length; i++) {
            var obj = mrvs[i];
            var keys = Object.keys(obj);
            tableHeader = tableHeader + '<tr>';
            tableBody = tableBody + '<tr>';
            for (var j = 0; j < keys.length; j++) {
                if (i == 0)
                    tableHeader = tableHeader + '<th>' + util.getMRVSLabels('vs_data_set', keys[j]) + '</th>';

                tableBody = tableBody + '<td>' + obj[keys[j]] + '</td>';
            }
            if (i == 0) {
                tableHeader = tableHeader + '</tr>';
            }
            tableBody = tableBody + '</tr>';
        }
        tableHeader = tableHeader + tableBody + '</tbody></table>';
    }
    current.u_description = desc + tableHeader;

I hope you find this article helpful and it saves you some time. Please dont hesitate to ask question or provide feedback.

#recordproducer #servicecatalog