Calculate Interval Between Dates With Advanced Grid

Introduction

This page will guide you through creating an application using Advanced Grid to compute the time interval between two dates. While our example focuses on hours and minutes, you can easily customize it to suit your requirements. You refer to this interval as effort.

How does it work?

For this example, you will have the following forms and grids:

• Grid Form Design
  

• Request Form Design

Insert a Custom HTML with the following script:

<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");
            var totalHrs = 0;
            var totalMins = 0;
 
            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;
                totalHrs = totalHrs + diffHrs;
                totalMins = totalMins + diffMins;
                var total = totalHrs + ":" + totalMins;
                 
 
                setValue(grid, i, 3, totalDuration);
                setValue(grid, i, 4, total);
 
            }
        });
 
        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>

Once you add the script, you will get the following result:

Download sample app