Can you please elaborate on "some rates using some predefined templates" and "select the template using selectbox."?

Is the value already readily available in other columns? If yes, it is just a matter on keying it in into the "formula" field like what is documented in Spreadsheet

Hi Bastiana,

I am calculating compensation due to personnel.

The spreadsheet is in an Ajax subform loaded by the compensation template selected.

The first 3 columns are loaded from a table into the spreadsheet using multirow form binder.

The setting for the spreadsheet is 

I am using the following script taken from Prepopulate a grid with data via Javascript to calculate the amount.

<script>
    function calculateTotal(amount) {
        var pay = FormUtil.getField("grossPay").val();
        if (pay === undefined || pay === "") {
            pay = "0"; //set a default
        }
        return (amount/100) *pay;
    }
</script>

The formula only calculate after i have manually updated the percentage cell but I would like the calculations to occur on loading the spreadsheet without user interaction.

How can i trigger the spreadsheet to update or recalculate cells by script.

thanks

Hi, the spreadsheet seem to using the Handsontable community version. In the spreadsheet setting, it supports "Custom Settings (JSON)". I tried with following seem to work. You will need to prepare the changed row which contains the dependent column.

{
    "afterInit" : function() {
        var ht = this;
        var rows = ht.countRows();
        if (rows > 0) {
            var changes =  [];
            for (var i = 0; i < rows; i++) {
                changes[i] = [i, "amount", "", ""];
            }
            //prepare changes to trigger the calculation
            ht.runHooks("afterChange", changes, "edit");
        }
    }
}

Hi Panda, I can't seem to get this to work. I am using ver 6.07 Enterprise. Even though i place this in the "Custom Settings (JSON)" of the Spreadsheet, the spreadsheet only updates if i manually update the percentage (inputVal) column. Should I move this to a custom HTML element. Thanks

Hi Chiek, I had the same challenge but Panda's code worked for me with one minor adjustment (the first row did not correctly auto-calculate which I figured is a problem with the array). In my case "inherent_rating" is the auto-calculated spreadsheet column. { "afterInit" : function() { var ht = this; var rows = ht.countRows(); if (rows > 0) { var changes = []; for (var i = 0; i < rows; i++) { changes[i+1] = [i, "inherent_rating", "", ""]; } //prepare changes to trigger the calculation ht.runHooks("afterChange", changes, "edit"); } } }

Halo Chiek I experienced the same case and still not yet solved. How do you solve this problem (triggered on load) ? Thank you

Hello Alfa Papa I eventually got panda code to work. The problem was the data in my table not the code. Hope that helps. Thanks