How to retrieve multi row variable set data using Business rule

[Are Kaveri]

Hi ,

I have following requirement for outbound integration.

We have ABC Record producer ,

details - Multi row variable set

the below are variables under multi row variable set

Requestor, Department, Hiring Date

We need to send the Data of multi row variable set.

for this i have written following Business to get the data for multi row variable set but not working as expected

 try { 
 var r = new sn_ws.RESTMessageV2('my rest message');

var DetailsArray = JSON.parse(current.variables.details);
		var Arr = [];
		for (var i = 0;i<DetailsArray.length ;i++){
		 Arr.push(DetailsArray[i]['requestor','department','hiring_date']);
		}

		r.setStringParameterNoEscape('details', Arr); // not getting the data of multi row variable set
}

Please help me how to pull multi row variable data.

[Brad Bowman]

I'm not sure why that syntax isn't working on a record producer.  Here's an alternate approach - getting the MRVS data from the table it's stored on:

var Arr = [];
	var mrvs = new GlideRecord('sc_multi_row_question_answer');
	mrvs.addQuery('parent_id', current.sys_id);
	mrvs.orderBy('row_index')
	mrvs.query();
	while (mrvs.next()) {
		Arr.push(JSON.stringify({
			"question": mrvs.item_option_new.name.toString(),
			"value": mrvs.getValue('value'),
		}));
	}
	gs.addInfoMessage(Arr.join(','))

[Are Kaveri]

@Brad Bowman     My script there is some error in getting the multi row variable set variables.

from the script you shared i am not understanding what is question and value in my scenario.

var Arr = [];
	var mrvs = new GlideRecord('sc_multi_row_question_answer');
	mrvs.addQuery('parent_id', current.sys_id);
	mrvs.orderBy('row_index')
	mrvs.query();
	while (mrvs.next()) {
		Arr.push(JSON.stringify({
			"question": mrvs.item_option_new.name.toString(),
			"value": mrvs.getValue('details'),
		}));
	}
	gs.addInfoMessage(Arr.join(','))

Not understanding how my Multi Row variables are taken . Not working when i tried as above

Tried another way also as below

 var DetailsArray = JSON.parse(current.variables.details);
        var Arr = [];
        var fieldNames = ['requestor','department', 'hiring_date'];
        for (var fname in fieldNames) {
            for (var i = 0; i < DetailsArray.length; i++) {
                Arr.push(DetailsArray[i][fieldNames[fname]]);
                gs.addInfoMessage(Arr.join(','));
            }
         r.setStringParameterNoEscape('details',Arr);  // Not getting the data from multi row variable set
        }

[Brad Bowman]

This is a way to re-create the MRVS contents in an array of objects.  The 'Question' field on the table is a reference to the variable name, and the Value field is the variable value in the MRVS.  Here is a more concise way of building that, which I couldn't get to work properly yesterday.

(function executeRule(current, previous /*null when async*/) {
	var Arr = [];
	var obj = {};
	var idx = '';
	var mrvs = new GlideRecord('sc_multi_row_question_answer');
	mrvs.addQuery('parent_id', current.sys_id);
	mrvs.addQuery('value', '!=', '');
	mrvs.orderBy('row_index')
	mrvs.query();
	while (mrvs.next()) {
		if (idx == '') {
			idx = mrvs.row_index.toString();
			obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('value'); 
		} else if (idx == mrvs.row_index) {
			obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('value'); 
		} else {
			Arr.push(JSON.stringify(obj));
			idx = mrvs.row_index.toString();
			obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('value'); 
		}
		if (!mrvs.hasNext()){
			Arr.push(JSON.stringify(obj));
		}
	}
	gs.addInfoMessage(Arr.join(','))
})(current, previous);

I have two rows of four variables (named string, v_user, year, and select) that are populated in my MRVS.  This is the result.

{"string":"OK","v_user":"62826bf03710200044e0bfc8bcbe5df1","year":"2023","select":"choice1"},{"string":"NO","v_user":"a8f98bb0eb32010045e1a5115206fe3a","year":"2022","select":"choice2"}

An alternative to all of this in lieu of figuring out why the Business Rule isn't retrieving variable values is to dump the MRVS contents into a String field on the record that is about to be created with a line in the producer script like this:

current.field_name = producer.mrvs_internal_name;

Then your API Business Rule can just access current.field_name

[Are Kaveri]

@Brad Bowman  

 var Arr = [];
        var obj = {};
        var idx = '';
        var mrvs = new GlideRecord('sc_multi_row_question_answer');
        mrvs.addQuery('parent_id', current.sys_id);
        mrvs.addQuery('value', '!=', '');
        mrvs.orderBy('row_index');
        mrvs.query();
        while (mrvs.next()) {
            if (idx == '') {
                idx = mrvs.row_index.toString();
                obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('department');
            } else if (idx == mrvs.row_index) {
                obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('requested_for');
            } else if (idx == mrvs.row_index) {
                obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('hiring_date');
            }else {
                Arr.push(JSON.stringify(obj));
                idx = mrvs.row_index.toString();
                obj[mrvs.item_option_new.name.toString()] = mrvs.getValue('value'); // what to add here
            }
            if (!mrvs.hasNext()) {
                Arr.push(JSON.stringify(obj));
            }
        }
        gs.addInfoMessage(Arr.join(','));

 written exact code in my Business rule still not working and i have a doubt in the else condition what value to be added as i have 3 fields (multi row variable)