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

[caiopontes]

I got this exact demand to finish and you just saved me some time. Thank you!

[SheltonThompson]

I am pretty new to email scripts and trying to get refreshed on java script. Could you help advise how we can put this into an email script? Our current email script is 

(function runMailScript(current, template, email, email_action, event) {

	// Add your code here

	template.print('Variables: <br/>');
	
	var variables = current.variables.getElements(); 
	for (var i=0;i<variables.length;i++) { 
		var question = variables[i].getQuestion();
		var label = question.getLabel();
		var varvalue = question.getDisplayValue();
		if(label != ''){
			template.space(4);
			template.print('  ' + label + " = " + varvalue + "<br/>");
		}
	} 

})(current, template, email, email_action, event);

[SanjivMeher]

@SheltonThompson Your script looks fine. Do you want to make improvements to it? Also is this for a record producer or catalog item?

[SheltonThompson]

@SanjivMeher  I was wanting to incorporate the script that was shown above. For example to get the get the actual value from the reference/choice field versus the back-end value. Its for a record producer. 

[SanjivMeher]

Got it. Try this @SheltonThompson 

(function runMailScript(current, template, email, email_action, event) {

	template.print('Variables: <br/>');
     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 == '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!='') {
			template.space(4);
			template.print('  ' + qa.question.getDisplayValue()  + " = " + variableValue + "<br/>");
        }
    }	
})(current, template, email, email_action, event);

[Community Alums]

I've written code in this way:
https://steemancodes.blogspot.com/2024/06/retrieve-data-from-variables-for.html

[Community Alums]

Hi @SanjivMeher , thanks for the detailed script.

It would be very helpful If you could also provide the code for populating multirow variables data from record producer to the description of a case.

TIA

[SanjivMeher]

@Community Alums I have added a code to populate the variable set. In my case the description is HTML field, so building a html table and populating the MRVS in there.

[Community Alums]

@SanjivMeher , thanks for updating the code.

Can we copy these MRVS variables to Multiline text description field?

We have a requirement to clone the HR case, and we are unable to copy Variable editor values, so thought of populating the variables  to description so that it gets cloned.

Any other idea or approach would you suggest? Or shall I create community post and tag you over there?

TIA

[SanjivMeher]

@Community Alums The challenge with text description is how you will display the MRVS.

One option is creating a HTML field or if there is an exisitng HTML field that you can utilize and then add all your variables to that HTML field. The variables are much more presentable in an HTML field.