Request Items from one Multi Row Variable Set

This one has a lot of moving parts and some of it may need tweaking to fit your instance, but...

The business case I was presented was to be able to trigger a series of emails when an employee is off-boarded from the company.  My first stab at this was to create a catalog item that allowed the user to select an employee,submit a form and a simple workflow would trigger notifications based on parameters provided by the submitter.  Upon demonstrating this, the requirements changed (as oft they do) and they wanted to be able to enter multiple people to off-board at one time.  With each name listed, they wanted to be able to track each email that was sent out individually...

So, this sounded like an instance for a Multi Row Variable Set and a many-to-one relationship between requests (sc_request) and requested items (sc_req_item).  To achieve this, i used the following steps:

Step 1:  Who is submitting the request.

To gather this information, i built a single row variable set to capture who is sitting at the keyboard entering the request.  The variables simply identify a few attributes about the person submitting the request.

Notice that only one value is a reference field.  By default, this value will be the user sitting at the keyboard (gs.getUser()).  The rest of the fields are populated via client script / script include (these will be used again later on as well !)

SCRIPT INCLUDE:

//dont forget to make it client callable !
var GetUserDetailsAjax = Class.create();
GetUserDetailsAjax.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	getInfo: function(){

		var obj={};
		obj.name='';
		obj.department='';
		obj.email='';
		obj.phone='';
		obj.manager = '';
		obj.title = '';
		obj.location = '';
		obj.company = '';
		obj.employee_number = '';
		var id=this.getParameter('sysparm_user_id');
		var gr= new GlideRecord('sys_user');
		if(gr.get(id)){

			obj.name = gr.name.toString();
			obj.department = gr.department.name.toString();
			obj.phone = gr.phone.toString();
			obj.location = gr.location.name.toString();
			obj.company = gr.company.name.toString();
			obj.email=gr.email.toString();
			obj.manager = gr.manager.name.toString();
			obj.title = gr.title.toString();
			obj.employee_number = gr.employee_number.toString();

		}

		return JSON.stringify(obj);

	},

	type: 'GetUserDetailsAjax'
});

I structured this to capture more data than i really needed just in case I needed it later.  That way i wouldn't have to keep updating the script include

CLIENT SCRIPT:

function onChange(control, oldValue, newValue, isLoading) {

	if (newValue == '') {

		return;

	}

	var id = g_form.getValue('requested_by');
	var ga = new GlideAjax('GetUserDetailsAjax');
	ga.addParam('sysparm_name','getInfo');
	ga.addParam('sysparm_user_id',id);
	ga.getXML(CallBack);

	function CallBack(response)
	{
		var answer = response.responseXML.documentElement.getAttribute("answer");
		var user=JSON.parse(answer);

		g_form.setValue('requested_by_email',user.email);
		g_form.setValue('requested_by_phone',user.phone);
		g_form.setValue('requested_by_department',user.department);
		g_form.setValue('requested_by_manager',user.manager);
		g_form.setValue('requested_by_employee_number',user.employee_number);
		
		g_form.setReadOnly('requested_by_email',true);
		g_form.setReadOnly('requested_by_phone',true);
		g_form.setReadOnly('requested_by_department',true);
		g_form.setReadOnly('requested_by_manager',true);
		g_form.setReadOnly('requested_by_employee_number',true);

	
	}

}

Notice that i render the string fields read-only as well... you could also do this with a UI Policy if that is your preference.

Additionally, i build another client script that hides most of the requester's data when they are entering the form.  I left the "email address" field on the form so that they can validate that they know who they are :).  Removing these fields from the initial data entry form keeps the form trim and sleek and yet still captures important data that may be required during the fulfillment process (thereby available on the Variables Editor section of the task form).

Step 2:  Who is getting off-boarded

Now that I can record who is submitting the request, its time to build the second variable set...the Multi Row Variable Set!  This will list the employees that will be off-boarded. For this, I again chose one field as a reference and a few others as a string that will be populated via client script (calling on the same script include built previously.)

The client script is very similar to the one used to identify the submitter.  It is called onChange of the "user" field on the multi row variable set and updates the fields associated with the reference value chosen.

function onChange(control, oldValue, newValue, isLoading) {

	if (newValue == '') {

		return;

	}

	var id = g_form.getValue('user');
	var ga = new GlideAjax('GetUserDetailsAjax');
	ga.addParam('sysparm_name','getInfo');
	ga.addParam('sysparm_user_id',id);
	ga.getXML(CallBack);

	function CallBack(response)
	{
		var answer = response.responseXML.documentElement.getAttribute("answer");
		var user=JSON.parse(answer);

		g_form.setValue('full_name',user.name);
		g_form.setValue('manager',user.manager);
		g_form.setValue('employee_number',user.employee_number);
	
		g_form.setReadOnly('full_name',true);
		g_form.setReadOnly('manager',true);
		g_form.setReadOnly('employee_number',true);

	
	}

}

Now the user has the ability to select multiple employees to off-board at the same time

Step 3:  Creating the Requested Items

Now, there are probably a dozen ways to do this, but I chose to do this via the workflow.  When the off-boarding catalog item is submitted, it kicks off a workflow identified on the item record like all "normal" requests.  The key to this workflow is what it does once it kicks off

As you can see, the workflow is quite small and only contains a few activities:

Begin:  you cant start without a beginning

Run Script (Set Parent RITM Value):  this updates the RITM record that is created when the user initially submits the record.  You can add anything you want to this activity as it doesn't really matter what is added to this record as you will see later.

Run Script (Create Multi Row RITMS):  This is where the magic happens.  This Run Script is what creates each RITM according to the rows on the multi row variable set.  The script used:

//get  row count for each MRVS row
var rowCount = current.variables.offboarding_user_list.getRowCount(); //This includes the internal name of the multi row variable set which is "offboarding_user_list"
for (var i = 0; i < rowCount; i++) {

	//Define variables that will be the same on each RITM.  Add more as required
	var req = current.request;
	var reqBy = current.variables.requested_by;
	
	//Grab each row of variables that will be different on each RITM
	var row = current.variables.offboarding_user_list.getRow(i);
	var user = row.user;
	var nam = row.full_name;
	var num = row.employee_number;
	var mgr = row.manager;
	
	//Crete a RITM for each row
	var rec = new GlideRecord ('sc_req_item');
	rec.cat_item = 'f06761511b582010e8addd3bdc4bcbfe';//catalog item for offboarding line item  NOTE:  This is REQUIRED to kick off the individual workflow created by each MRVS line item
	rec.request = req;
	rec.request.requested_for = reqBy;
	rec.approval = 'approved';
	rec.state = 1;
	rec.stage = 'request_approved';
	rec.assignment_group = 'ea42046613059200a01f3ea32244b0a0'; //sys_id of the Human Resources group
	rec.short_description = "Off-boarding for user "+nam;
	rec.description = "Off-board this user: \n - Name: "+nam+"\n - Employee Number: "+num+"\n - Manager: "+mgr;

	rec.insert();
}

An important part of this workflow activity is the fact that I identified a catalog item when creating the MRVS request items.  This will allow each of the RITMs created to kick off their own workflow individually.  It is THAT workflow that will create any tasks, approvals, notifications, etc... for each given Request Item.

Timer (wait 5 seconds):  To let it all run and stuff

Run Script (Set Value and Delete):  This activity simply deletes the "parent" request item since it is pretty useless after all.  We only needed it to trigger a workflow that created the other RITM records.  That bit of script:

current.deleteRecord();

Step 4 - Updates to the portal

When I first tested this, I noticed that due to timing when the resulting request record was displayed on the portal, it showed my "parent" (that was actually deleted via the workflow).  Additionally, since my other workflows only trigger notifications and then should be automatically closed, it was displaying them as open as well.  If I refreshed the portal "Ticket" page (id=sc_request), it displayed properly.  To overcome this for the user, i set up a refresh timer on the widget

CAUTION:  Using auto-refresh on portal widgets can and WILL cause eventual semaphore over usage, so make sure you have a way to destroy them when not in use.

To provide this, i cloned the "Requested Items" widget and updated the client script by adding a refresh timer to it.

function($scope,$timeout,$interval,spUtil) {

var c = this;
$interval(function(){
console.log("------working------")
spUtil.update($scope);
}, 3000);
}

With this in place, the requested item widget updates as the user is viewing it thereby displaying the records as they are updated.

SPECIAL NOTE:  I put this together in a few hours and am looking into using record watcher instead of spUtil.update().  This should help keep semaphore overuse down.  Alternatively, i really only need the update to run 3-4 times and then stop, so I may end up putting this into a counted execution container of sorts...i haven't decided just yet.

I do not intend to promote this to production without addressing the poor use of spUtil.update().  If anyone has any suggestions on how to run the spUtil.update() a given number of times, feel free to respond.  Otherwise, i will be looking at Record Watcher as a solution though i am not sure it will watch for a deleted record...

The resulting portal user experience after submission:

.