This post will teach you how to calculate the time between two dates using Advanced Grid. This app will calculate the difference in hours and minutes however, you can adapt it accordingly to your needs. In this app, we will call the difference between the two dates, effort.

Form Design


Grid Form Design


Request Form Design


Custom HTML

The custom HTML is where the effort is calculated.

Javascript
<script>
    $(function() {
        var grid = FormUtil.getField("advgrid"); // Field id of advance grid
 
        $(grid).on("change", function() {
            var start = FormUtil.getGridCellValues("advgrid.start");
            var end = FormUtil.getGridCellValues("advgrid.end");
 
            for (var i = 0; i < start.length; i++) {
 
                var startDate = new Date(start[i]);
                var endDate = new Date(end[i]);
                var diffMs = (endDate - startDate);
 
                var diffHrs = Math.floor(diffMs / 3600000); // Hours
                var diffMins = Math.round((diffMs % 3600000) / 60000); // Minutes
                var totalDuration = diffHrs + ":" + diffMins;
 
                setValue(grid, i, 3, totalDuration);
 
            }
        });
 
        function setValue(grid, rowIndx, colIndx, colValue) {
            var DM = $(grid).find(".pq_grid").pqGrid("option", "dataModel");
            var data = DM.data;
            var json = data[rowIndx][data[rowIndx].length - 1];
            var obj = eval("[" + json + "]");
 
            var colKey = DM.columnKey[colIndx];
 
            obj[0][colKey] = colValue;
 
            //Update data
            json = JSON.encode(obj);
            json = json.substring(1, json.length - 1);
            DM.data[rowIndx][colIndx + 1] = colValue;
            DM.data[rowIndx][data[rowIndx].length - 1] = json;
 
            //Update hidden table
            var indexKey = DM.data[rowIndx][0];
            var tbl = $(grid).find('.table_container table');
            tbl.find("tr.key_" + indexKey).find("[column_key=" + colKey + "]").text(colValue);
            tbl.find("tr.key_" + indexKey).find("textarea").val(json);
 
            //Update table cell
            var colCell = $(grid).find("tr[pq-row-indx=" + rowIndx + "] .pq-grid-cell[pq-col-indx=" + (colIndx + 1) + "]");
            $(colCell).find(".pq-td-div").html('<div class="form-cell-value"><span>' + colValue + '</span></div>');
        }
    });
</script>


Runtime

This is the result of the above code:


Download the sample app below:

  • No labels