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

[si21]

Hi @SanjivMeher , I have tried the BR code and all the details are coming to description. But when we use clone HR case UI action, these values are not being copied to new record.

original case screenshot :

cloned case screenshot below.

How can we fix this?

TIA

[Community Alums]

@si21 
You can check this article once:: 
https://steemancodes.blogspot.com/2024/06/retrieve-data-from-variables-for.html

[SanjivMeher]

In your BR, make sure to check only when the desc is not empty assign to description, otherwise use the description from the source record.

for ex

if (desc!='')

current.description = desc;

[Biddaum]

@SanjivMeher 

I'm trying to use your script in a before business rule to generate the description for an incident on a record producer, but nothing is being added to the description field, would love some assistance if you are able please?

Table - Incident

Active - True

Advanced - True

Application - Global

When to run

When - Before

Insert - True

Update - (have tried true and false)

Delete - False

Query - False

Filter conditions

Set to run when variables value on record producer is NOT empty 

Advanced:

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    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 == '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
})(current, previous);

I will also say that I have set some field values in the business rule that do take effect when a ticket is logged and yet the script still is not setting the description value

[Loudigi]

What if the question type is List Collector (12)? Which gives you a comma delimited list of sys_ids.

[SanjivMeher]

@Loudigi I have updated the code with List Collector

[Rahmat]

Hi @SanjivMeher ,

I have a Record Producer with several variables, including a checkbox-type variable. After submitting the form:

All variables are correctly displayed in the Record Producer submission summary.

An HR case is successfully created.

All variables are getting mapped to the HR Case's Description field—except the checkbox variable.

The checkbox value (expected to show true or false) does not appear in:

The submission summary after form submission.

The HR Case form (Description field).

How can I ensure that the checkbox variable value is captured and correctly mapped to the HR case description like the other variables?

I am attaching the image for your reference 

 

 Thanks in Advance!!!