How to calculate fields from Multi Row Variable Set and update external variables in real-time?

[SREEKANTH_P]

I’m working on a catalog item where I’ve defined a Multi Row Variable Set (MVRS) named invoice_details. This MVRS contains the following variables:

original_amount

revised_amount

invoice_date

I have the following requirements:

I want to add a read-only calculated variable inside the MVRS called cost, which should be the product of original_amount and revised_amount.

This cost field should update in real-time as the user updates the original or revised amount.

Outside of the MVRS, I have two calculated fields:

revenue_impact_dc → should be the sum of (original_amount - revised_amount) across all MVRS rows.

revenue_impact_usd → should be the above result multiplied by a budget rate, which comes from a separate single variable or table.
How can I achieve:

The real-time update of the cost field inside MVRS

And the recalculation of the external revenue impact fields whenever the user changes the MVRS values?

[Ankur Bawiskar]

@SREEKANTH_P 

you can detect when a row is added/deleted and then perform the calculation logic and update outside variable

check these links and enhance your logic

MRVS detect when a row is removed or deleted 

Widget that reacts to onChange event of Variable 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Roshnee Dash]

Since MVRS doesn't support calculated fields directly, you can simulate this behavior using a Catalog Client Script of type "onChange" for both original_amount and revised_amount.

Steps:

1. Create a new variable inside the MVRS:

   • Name: cost
   • Type: Read-only (you can use a UI Policy to make it read-only)
   • Default value: blank

2. Create two Catalog Client Scripts (or one combined script) for original_amount and revised_amount:

   • Type: onChange
   • Applies to: original_amount and revised_amount
   • Script:

function calculateCost(rowIndex) {
  var original = g_form.getValue('original_amount', rowIndex);
  var revised = g_form.getValue('revised_amount', rowIndex);

  var cost = parseFloat(original || 0) * parseFloat(revised || 0);
  g_form.setValue('cost', cost.toFixed(2), rowIndex);
}

calculateCost(g_form.getRowIndex());

Recalculation of external fields (revenue_impact_dc and revenue_impact_usd)

To calculate values based on all rows in the MVRS, you’ll need a Catalog Client Script of type "onChange" or "onLoad" that loops through all MVRS rows.

Steps:

1. Create two variables outside MVRS:

   • revenue_impact_dc (read-only)
   • revenue_impact_usd (read-only)

2. Create a Catalog Client Script:

   • Type: onChange
   • Applies to: original_amount and revised_amount
   • Script:

function updateRevenueImpact() {
  var totalImpact = 0;
  var rowCount = g_form.getMultiRowVariableCount('invoice_details');

  for (var i = 0; i < rowCount; i++) {
    var original = parseFloat(g_form.getValue('original_amount', i) || 0);
    var revised = parseFloat(g_form.getValue('revised_amount', i) || 0);
    totalImpact += (original - revised);
  }

  g_form.setValue('revenue_impact_dc', totalImpact.toFixed(2));

  // Assuming budget_rate is a separate variable
  var budgetRate = parseFloat(g_form.getValue('budget_rate') || 1);
  var usdImpact = totalImpact * budgetRate;
  g_form.setValue('revenue_impact_usd', usdImpact.toFixed(2));
}

updateRevenueImpact();

• You can also trigger this script onLoad or onSubmit if needed.

You can check with

• Use UI Policies to make cost, revenue_impact_dc, and revenue_impact_usd read-only.
• Ensure variable names in the script match exactly with those defined in the catalog item.
• Test thoroughly with multiple MVRS rows to ensure calculations are accurate.

[Ankur Bawiskar]

@Roshnee Dash 

Thanks for sharing !

It would be nice if you could share a working example and the output for the above scripts you shared so that it helps other community members as well.

I could see some unfamiliar g_form methods in your script such as below, so curious to know how it works !

g_form.getMultiRowVariableCount

[Roshnee Dash]

Hi @Ankur Bawiskar 
The above one in one of my projects, we implemented a custom class or method that I dont remember exactly which was utilized document.querySelector and container.querySelectorAll to retrieve the row count based on a variable set identified by its sys_id. On the portal, the variable set's container is rendered with an id attribute in the format id="sys_id of your variable set", which we used to target the specific section dynamically.