How to populate a "total" field from different amount rows in a MVRS?

[Shao]

Hi! I am trying to achieve this outcome, which is where I have a MVRS to capture line items for a procurement request. I would like to sum total the rows as they are added, and then use that sum figure to populate another field on the record producer. I tried the following but it doesn't work for me

Would like to get some suggestions from the community here, thank you in advance!

[Amit Verma]

Hi @Shao 

Did you got a chance to look on the below posts which might be helpful ?

https://www.servicenow.com/community/itsm-forum/how-to-calculate-the-sum-of-the-value-in-mrvs-multi-...

https://www.servicenow.com/community/developer-forum/how-to-get-mrvs-variable-value-summation/m-p/24...

Thanks & Regards

Amit Verma

[Tai Vu]

Hi @Shao 

It's because the line 14 in your script. It appears that there's no getVariable method within the g_form object.

Let's try to change it from

mrvsValues = g_form.getVariable(mrvsName);

to

mrvsValues = JSON.parse(g_form.getValue(mrvsName));
//or
mrvsValues = JSON.parse(g_form.getValue('budget_row'));

Cheers,

Tai Vu

[Nick Parsons · Accepted solution]

Hi @Shao 

MRVS are limited in their abilities, which makes doing what you're trying to do difficult (for a "live" total update that is). Most solutions you'll find online talk about how to calculate the "total" when the parent record producer is submitted, not when a new row is added to the MRVS so that the user can see the total as they change the rows in the MRVS. There are quite a few reasons why this is difficult to do, two of the main ones are:

1. There is no easy way to detect when a row in a MRVS is deleted. ServiceNow provides a way to detect when a row is added (via the onSubmit client script within the MRVS), but not when it has deleted. This makes it difficult to know when to recalculate the total for the parent record producer. You can hack your own event listener that watches the MRVS by adding a macro to the page, but this is messy. Disabling deletion of rows in an MRVS is not an easy task either, and involves DOM manipulation.
2. When in an MRVS script, g_form represents the MRVS popup form, not the parent record producer form. So how do you write to fields on the parent form then (such as total)? From Quebec, ServiceNow provides an API to read values from the parent record producer when in an MRVS script by using g_service_catalog.parent.getValue(), but there is no way to set a field value (such as the total field) from within the MRVS script without resorting to doing some hacks.

So, if you want "live" updates of the total field as your MRVS data changes, you'll need to do some "hacky" things to get things to work. Below are two options that come to mind.

Use a custom variable

One option is the use a custom variable that you add to your catalog item/record producer. The custom variable references a widget under the Type Specifications tab that you can create. I recommend trying to create the widget in a generic way by passing in "options" that defines the behavior of the widget (eg: what field to sum on within a row, what field to write the sum to, etc.). The widget uses the AngularJS method to $watch() the scope of the MRVS to detect changes. This allows you to react to edits, creates, updates and deletes for each row. Below is a sample client controller portion of a widget:

api.controller=function($scope) {
	var options = this.options;
	var g_form = $scope.page.g_form;

	// These options are passed to this widget from the "Default value" on the Custom variable
	var totalField = options.total_field;
	var sumField = options.sum_field;
	var mrvsName = options.mrvs_name;

	$scope.$watch(function() {
		return g_form.getValue(mrvsName);
	}, function(jsonData) {
		if(jsonData) {
			var rows = JSON.parse(jsonData);
			// my logic to perform a sum, you can use a regular for loop though
			var total = rows.reduce(function(acc, row) { 
				return acc + (parseFloat(row[sumField]) || 0);
			}, 0);
			g_form.setValue(totalField, total);
		} else {
			g_form.setValue(totalField, "0");
		}
	});
};

The other fields on the widget (ie: the HTML template, the server script, etc. can be left as the default values).
On the Custom variable, you can specify a JSON string in the "Default value" tab, such as this:

{"total_field": "total", "sum_field": "price", "mrvs_name": "items"}

 These values are then read by the widget by accessing the .options property. As mentioned, this keeps the widget generic. If you end up creating another catalog item or record producer down the line that has a MRVS and requires the rows to be summed, you can just add this widget in again but change the options by passing in a different default value. 

Use a loop

Another option is to use an interval that triggers a function every X amount of milliseconds. This isn't too dissimilar to the $watch() method above (as that technically uses AngularJS's internal loop). You can create this interval in the parent record producer / catalog item using an onLoad client script. The idea is to read the MRVS every X seconds using setInterval() and then update the total accordingly by summing the values in the MRVS.

function onLoad() {
	var prev;
	setInterval(function() {
		var mrvsJSON = g_form.getValue("items") || "[]"; // items is the internal name of your MRVS
		if(mrvsJSON !== prev) { // if the MRVS has changed since the last run of the interval, then...
			var rows = JSON.parse(mrvsJSON); // read the MRVS data
			// My version of summing. You can use a regular for loop if that's clearer to you
			var total = rows.reduce(function(acc, row) {
				return acc + (parseFloat(row.price) || 0);
			}, 0);

			g_form.setValue("total", total);
			prev = mrvsJSON; // update prev to be the new MRVS data we set.
		}
	}, 1000); // execute the interval every 1000ms (1s)
}

Note that if you are in a scoped application, the setInterval method is part of the global window object used in browsers. If you have the system property <scope_name>.glide.script.block.client.globals set to true then this option will not work. You will need to either set this property to false or use another method.

Regardless of the method you use, both are more or less hacks in my opinion. If you can, calculate the sum on submission of your parent form rather than trying to make it a "live" calculation.

[Shao]

Thank you very much for the detailed reply Nick. Let me have a go at this.