How to calculate the sum of the value in MRVS (multi row variable set)

[Si Min]

Hi,

Coming across using the MRVS and wish to calculate the total amount for the number of books as shown in the pic.

Appreciate if anyone can enlighten me. I did do a checkup from this post but can't really get the idea.

[Si Min]

Hi Manish,

Appreciate you help on this.

Is what I am looking for. Would like to ask instead of requestor clicking the button, is there a way to auto populate the total amount instead?

And I have modified the Macro & Widget script for rounding the total amount to 2 decimal places by adding the "toFixed(2) but it did not workout.

Macro Script

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script>
function calculateTotal() {
    try {
//Replace mrvs with your variable set name
        var jsonStr = g_form.getValue("book_details");
        var objList = JSON.parse(jsonStr);
        var result = 0;
        for (var i = 0; i &lt; objList.length; i++) {
//Replace variable_name with your variable's name
            result += parseFloat(objList[i]["estimate_price_sgd"]);
        }
//Replace variable_to_set with the name of variable where you want to set the value
        g_form.setValue("total_estimated_amount_sgd", result).toFixed(2);
    } catch (e) {
        console.log("Error in the UI Macro Client Script");
    }
}
</script>
<button type="button" class="btn btn-primary" onclick="calculateTotal()">Calculate</button>
</j:jelly>

Widget Client Controller Script

function($scope) {
    /* widget controller */
    var c = this;

    calculateTotal = function() {
        try {
            //Replace mrvs with your variable set name
            var jsonStr = $scope.page.g_form.getValue("book_details");
            var objList = JSON.parse(jsonStr);
            var result = 0;
            for (var i = 0; i < objList.length; i++) {
                //Replace variable_name with your variable's name
                result += parseFloat(objList[i].estimated_price_sgd);
            }
            //Replace variable_to_set with the name of variable where you want to set the value
            $scope.page.g_form.setValue("total_estimated_amount_sgd", result).toFixed(2);
        } catch (e) {
            console.log("Error in the UI Macro Client Script");
        }
    }
}

Thanks.

[Manish Vinayak1]

Hi Si Min,

I can't think of a way to auto-population considering the limitation of not being able to access the variables outside MRVS from MRVS Client side script. However, to the other issue you mentioned, here is the solution:

This line is trying to execute .toFixed() on the setValue() function's return value:

 g_form.setValue("total_estimated_amount_sgd", result).toFixed(2);

Change it to the following:

 g_form.setValue("total_estimated_amount_sgd", result.toFixed(2));

Thanks,

Manish

[Si Min]

Hi Manish,

Thanks for your quick response.

Appreciate your help and I have marked your reply as the correct answer.

Thank you again!

[Si Min]

Hi Manish,

Got 1 more question, the button will not works if the requestor removes all entries from the MRVS. The value captured in total amount field is not get cleared after clicking it.

Thanks.

[Manish Vinayak1]

Hi Si Min,

Well, according to the code, that should get cleared when you click on "Calculate Total Estimated Amount" after removing all the rows from the Multi-Row Variable Set.

This approach needs to have an On Submit Client Script Validation, you need to traverse through the Multi-Row Variable Set Values again, and verify that the calculated amount from on the Total Estimated Amount field matches with the sum of all amounts in the MRVS. (As someone might have removed a row after the calculation).

If that doesn't match, you could do either of the following things:

• Calculate the Total Estimated Amount automatically on Submit, update it on the field and proceed submission
• Calculate the Total Estimated Amount automatically on Submit, update it on the field, give the user an "alert / confirm box" saying the total was updated and proceed submission as per their response
• Validate that the value in the Total Estimated Amount is not equal to the sum of all rows in the MRVS, abort submission and prompt user to Click on Calculate Total Amount for revised calculations

I know it is a lot for just one calculation, but I can't think of any other solution considering the MRVS limitation.

Hope this helps! Let me know if you have further questions.

Cheers,

Manish