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: