spreadsheet auto-calculation

 
1
0
-1

Hi,

Can anyone one tell me how to get the spreadsheet element to do calculations automatically.

I need to calculate some rates using some predefined templates. The user enters an amount in a field and then select the template using selectbox.

the template is loaded in the spreadsheet element by multirow form binder but the calculations in the spreadsheet are not updated unless i manually edit an effected cell.

Thanks

    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -1

      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");
        }
    }
}
      1. Chiek

        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

      2. Eric

        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"); } } }

      3. Alfa Papa

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

      4. Chiek

        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

      CommentAdd your comment...
    2.  
      1
      0
      -1

      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



        CommentAdd your comment...
      1.  
        1
        0
        -1

        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

          CommentAdd your comment...