Using workflow scratchpad with a Multi Row Variable Set

[Shane Johnson1]

Hello All,

I have a complicated requirement that I am working through that I hope someone will help point me in the right direction. 

Requirement: A user needs to be able to request several roles at a time. These roles are based on Application and location. Each application has a different Approver and the approval has to be linear to ensure separation of duties. Once all approvals are completed a single task is sent listing only the approved roles.

Done/Attempted: I have the form with a Multi Row Variable set. It has 3 reference Variables to 3 tables. Application, Entity, Responsibility. The Responsibility table references both Application and Entity. Application and Entity are not related. I have it set to fill them out in order stated. Cant pick a role with out Application and location.

I have started the workflow based on several other articles in the community. Mini-Lab: Workflows — For Loop and Scripting the Multi-row Variable Set to name a few. 

This is the First Run Script that seems to be working.

var identifier = context.name + '.' + activity.name;
var mrvs;
//Get the Variables from current Request Item
var grRitm = new GlideRecord('sc_req_item');
grRitm.get(current.sys_id);
mrvs = grRitm.variables.oracleroles;
//Get the row count
var rowCount = mrvs.getRowCount();
workflow.scratchpad.count = rowCount;
workflow.scratchpad.mrvs = mrvs;
workflow.scratchpad.counter = 0;
workflow.scratchpad.message ='';

gs.info('##### 1st Script This is in the scratchpad: ' + rowCount + ', ' + workflow.scratchpad.mrvs);
gs.info('##### [WF:{1}] Total number of records to loop: {0}', workflow.scratchpad.count, identifier);

Here is the gs.Info output:

##### 1st Script This is in the scratchpad: 3, [ {
"application" : "2f990e17dba444905df2dee5ce9619ad",
"entity" : "7934b553db6444905df2dee5ce961975",
"responsibility" : "011906afdb2484905df2dee5ce9619e9"
}, {
"application" : "24694e17dba444905df2dee5ce9619a0",
"entity" : "b134b553db6444905df2dee5ce961976",
"responsibility" : "011906afdb2484905df2dee5ce9619e5"
}, {
"application" : "5c39ce17dba444905df2dee5ce96191b",
"entity" : "b534b553db6444905df2dee5ce961978",
"responsibility" : "011946afdb2484905df2dee5ce961912"
} ]

So it shows as what I believe is expected. 

The next script which is the start of the loop is where my problem begins. Im not sure if its because the MRVS is json but the workflow.scratchpad.mrvs is empty. Please forgive the mess of the script as it is a work in progress.

var num = workflow.scratchpad.counter;
//var mrvs = workflow.scratchpad.mrvs[workflow.scratchpad.counter];
gs.info('##### 2nd Script This is in the scratchpad: ' + workflow.scratchpad.count + ', ' + workflow.scratchpad.mrvs);
//var rsp = row[num].responsibility;
//workflow.scratchpad.currentRec = row;
//var grRec = new GlideRecord('u_oracle_responsibilities');
//grRec.addQuery('sys_id', rsp);
//grRec.query();
//var app = grRec.u_application.getDisplayValue();
//var ent = grRec.u_entity.u_entity;
//var nam = grRec.getDisplayValue();


//gs.info('##### Current Row: ' + row + ', Current Count: ' + num);

//workflow.scratchpad.message += '[' + workflow.scratchpad.counter + ']' 
//	+ 'Entity: ' + ent
//    + ', Responsibility: ' + nam
//    + ', Application: ' + app;

Here is the gs.info output:

##### 2nd Script This is in the scratchpad: 3,

So the question is can the array from a Multi Row variable set be passed in the workflow scratchpad to other scripts? Or will I have to pull the variables from the request item in every run script activity?

My next phase question will be is it possible to get the info from a specific row as part of the loop? So on the first pass of the loop, I want to get the responsibility or application from [0] then next pass from[1] and so on so I can get the approver thats referenced on the application table.

Thanks in advanced for the help

Shane

[James Gragston]

Shane,

If you want to iterate through those JSON objects, you must first parse the JSON with the JSON.parse() JavaScript function. Once this is done, you can capture the length of the array to use as a counter. Use the following to accomplish this:

var MRVS = workflow.scratchpad.mrvs;
var mrvsArr = JSON.parse(MRVS);
var iterator = mrvsArr.length //should output the number of JSON objects in your mrvs

You can grab each value by doing something like this:

var MRVS = workflow.scratchpad.mrvs;

//the JSON key in second pair of brackets MUST be in quotes
var obj1_val = MRVS[0]['key_in_quotes'];

var obj2_val = MRVS[1]['key_in_quotes'];

gs.info(obj1_val); //outputs value(depending on key) in first JSON object
gs.info(obj2_val); //outputs value(depending on key) in second JSON object

Hope this helped!

[Shane Johnson1 · Accepted solution]

Thanks @James Gragston for the reply. I was able to use your suggestion to help build the loop through the mrvs array.

 I ended up not passing the mrvs array into the scrachpad. For some reason it was empty in the other run scripts so I skipped it.

This is what I set the first Run Script to. It gets the rowCount of mrvs and set the counter to 0 before the loop begins.

var identifier = context.name + '.' + activity.name;
var mrvs;
//Get the Variables from current Request Item
var grRitm = new GlideRecord('sc_req_item');
grRitm.get(current.sys_id);
mrvs = grRitm.variables.oracleroles;
//Get the row count
var rowCount = mrvs.getRowCount();

workflow.scratchpad.count = rowCount;
workflow.scratchpad.counter = 0;

The second run script starts the loop. I decided it was easier to just go get the variables again instead of using the scratchpad. It also gets the specific record from my responsibility table based on the counter. Since the counter starts a 0, it starts there. I am able to sent the list of approvers through the scratchpad to the Approval Activity.

var num = workflow.scratchpad.counter;
var mrvs;
//Get the Variables from current Request Item
var grRitm = new GlideRecord('sc_req_item');
grRitm.get(current.sys_id);
mrvs = grRitm.variables.oracleroles;
var bpo; // This will be the array of approvers
var obj_val = mrvs[num]['responsibility']; // This gets the sys_id of the responsibility from the mrvs array
//Get the approvers and responsibility name
var grRec = new GlideRecord('u_oracle_responsibilities');
grRec.addQuery('sys_id', obj_val);
grRec.query();
while (grRec.next()){
	var app = grRec.u_application.u_bpo.toString();
	var ent = grRec.u_entity.getDisplayValue('u_entity');
	var nam = grRec.u_name;
}
//Pass to scratchpad
workflow.scratchpad.resp = nam;
workflow.scratchpad.app = app;

Again, thanks for the help

Shane