How to Auto-calculate Total from MRVS

[divyashah]

Hello Community,

I'm working on a Record Producer and need some help with a client script. My goal is to automatically calculate a total from a Multi-Row Variable Set (MRVS) and populate another, separate variable on the same form.

My Setup:

1. A Record Producer for creating a record.

2. On this producer, I have an MRVS called ornament_details where users can add multiple ornaments. This MRVS has several columns, including a weight and number of items column.

3. Also on the same producer, I have a separate String variable called gross_weight.

Here is a screenshot of the form layout:

What I Want To Achieve:

When a user adds, removes, or modifies rows in the ornament_details MRVS, I want the gross_weight field to automatically update with the sum of all the values from the answer column in the MRVS.

Example:

• User adds Row 1 with weight = 10 and No of Items = 5. The gross_weight field should update to 50 (5*10).

• User adds Row 2 with weight = 5 and No of Items = 2. The gross_weight field should update to 60((5 * 10)+(5 * 2)).

• User removes Row 1. The gross_weight field should update back to 10.

My Question:

I believe this requires an onChange Catalog Client Script that runs on the ornament_details MRVS. However, I'm not sure how to:

1. Properly access the entire MRVS data (all rows) within the script.

2. Parse the JSON data that the MRVS returns.

3. Loop through the rows to calculate the sum.

4. Set the value of the gross_weight variable.

Could someone provide an example of a client script that accomplishes this? What is the best practice for this scenario?

Thank you in advance for your help

[Community Alums]

hi @divyashah ,

• MRVS data is stored as JSON
  In the client script, you’ll get it as a JSON string, and you need to parse it.

• Use a Catalog Client Script

  • Type: onChange

  • Variable name: your MRVS variable (ornament_details)

  • Runs on: Client side

• Loop through all rows
  Each row becomes a JSON object — you can access fields by their names.

• Calculate the total weight
  For each row, multiply weight × number of items, then sum across all rows.

• Set the total
  Use g_form.setValue('gross_weight', total);

•  
•  

function onChange(control, oldValue, newValue, isLoading) {
  if (isLoading || newValue == '') {
    return;
  }

  try {
    // Parse the MRVS JSON data
    var mrvsData = JSON.parse(newValue);

    var total = 0;
    
    for (var i = 0; i < mrvsData.length; i++) {
      var row = mrvsData[i];

      // Adjust these field names to match your MRVS columns
      var weight = parseFloat(row.weight) || 0;
      var noOfItems = parseInt(row.number_of_items) || 0;

      total += weight * noOfItems;
    }

    // Set the total to the gross_weight variable (string variable)
    g_form.setValue('gross_weight', total.toString());

  } catch (e) {
    console.error('Error parsing MRVS data:', e);
  }
}

•  

[divyashah · Accepted solution]

Thank you for your prompt and accurate response. Your client script is the correct and most direct method for this task and is considered the standard best practice.

While implementing it, I encountered a errors of json not being properly parsed and not showing. So, to create a more resilient solution, I opted to offload the main calculation to the server using a Script Include.

For the benefit of the community, here is the final implementation that resolved the issue for me.

Final Solution:

This solution uses a Script Include to perform the calculation and two client scripts to trigger it.

1. Script Include: GetValueAnswer

• Client Callable: true

• This script receives the MRVS data, calculates the total from the answer column of each row, and returns the sum.

var GetValueAnswer = Class.create();
GetValueAnswer.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    
    getValue: function () {
        var json = this.getParameter('sysparm_mrvs_data');
        if (!json) return "0.00";

        try {
            var data = JSON.parse(json);
            var total = 0;
            for (var i = 0; i < data.length; i++) {
                total += parseFloat(data[i].answer) || 0;
            }
            return total.toFixed(2);
        } catch (e) {
            gs.error("[GetValueAnswer] JSON Parse Error: " + e.message);
            return "0.00";
        }
    },

    type: 'GetValueAnswer'
});

2. Client Script on MRVS: ornament_details

• Type: onChange

• This script does two things:

  1. Calculates the sub-total for the row being edited.

  2. Calls the Script Include to update the cumulative gross_weight. A setTimeout ensures the MRVS data is current.

function onChange(control, oldValue, newValue, isLoading) {
	if (isLoading) return;

	// Part 1: Calculate sub-total for the current row
	var weight = parseFloat(g_form.getValue('weight')) || 0;
	var numItems = parseInt(g_form.getValue('no_of_items')) || 0;
	g_form.setValue('answer', (weight * numItems).toFixed(2));

	// Part 2: Trigger cumulative calculation after a short delay
	setTimeout(function() {
		var mrvsData = g_form.getValue('ornament_details');
		if (!mrvsData || mrvsData === '[]') {
			g_form.setValue('gross_weight', '0.00');
			return;
		}

		var ga = new GlideAjax('GetValueAnswer');
		ga.addParam('sysparm_name', 'getValue');
		ga.addParam('sysparm_mrvs_data', mrvsData);
		ga.getXMLAnswer(function(response) {
			g_form.setValue('gross_weight', response);
		});
	}, 500); 
}

3. Client Script on Checkbox: get_gross_weight

• Type: onChange

• Provides a manual "Recalculate" button for the user.

function onChange(control, oldValue, newValue, isLoading) {
	if (isLoading || newValue !== 'true') {
		return;
	}

	var mrvsData = g_form.getValue('ornament_details');
	var ga = new GlideAjax('GetValueAnswer');
	ga.addParam('sysparm_name', 'getValue');
	ga.addParam('sysparm_mrvs_data', mrvsData);
	ga.getXMLAnswer(function(response) {
		g_form.setValue('gross_weight', response);
	});
}

Final Results Screenshot: