How to perform calculations over different rows of one column
How to perform calculations over different rows of one column
supertrip
Posts: 3Questions: 1Answers: 0
Hello! I just created a jQuery Datatable which is working fine, but at this point I need to create a new column in the Datatable that takes the data from one specific column (named "Start Date") and gives as a result the difference: row[ i +1 ] - row[ i ] for each row, meaning that I need to make a calculation between each row and the previous one.
Since I am a newbie with Datatable, can anyone help me achieving this result?
I already tried to select the rows from the "Start Date" column by using
var i = 0;
var table = $('#table_id').DataTable();
var row = table.row( i ).data().StartDate;
but it only works with i = 0 , for i > 0 if I run
return table.row( i ).data().StartDate;
It gives me an error message: "Cannot read property 'StartDate' of undefined". Am I completely wrong with trying to accomplish what I have in mind? Anyone knows how to do it?
Here is my HTML code:
<head>
<title>Table</title>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
</head>
<body>
<table id="table_id" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th></th>
<th>Milestone</th>
<th>Actual Date</th>
<th>Planned Date</th>
<th>Delay</th>
<th>Days Left</th>
<th width="18%">Reason for Late Submission</th>
</tr>
</thead>
<tfoot> </tfoot>
</table>
</body>
</html>
and here is my javascript:
$(document).ready(function() {
loadMyItems();
});
function loadMyItems() {
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
var oDataUrl = siteUrl + "link";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: mySuccHandler,
error: myErrHandler
});
}
function mySuccHandler(data) {
try {
var dataTableExample = $('#table_id').DataTable();
if (dataTableExample != 'undefined') {
dataTableExample.destroy();
}
dataTableExample = $('#table_id').DataTable({
scrollY: 770,
"order": [[ 2, "asc" ]],
"pageLength": 50,
"aaData": data.d.results,
"aoColumns": [{
"mData": "EditLink",
"render": function(EditLink) {
return '<button onclick="EditItemWithCheckoutAlert(event, \''+EditLink+'\');">Edit</button>';
}
}, {
"mData": "Title"
}, {
"mData": "StartDate"
}, {
"mData": "Planned_x0020_Date"
}, {
"mData": "Days_x0020_of_x0020_delay"
}, {
"mData": "Days_x0020_Left"
}, {
"mData": "Reason_x0020_for_x0020_Late_x002",
"render": function( data, type, full, meta) {
if(!data){var returnText = "";}
else {var returnText = data.results;}
return returnText;
}
}]
});
} catch (e) {
alert(e.message);
}
}
function myErrHandler(data, errCode, errMessage) {
alert("Error: " + errMessage);
}
This question has an accepted answers - jump to answer
Answers
Ok, I solved out my problem. For anyone that might be interested, here is my solution (probably not the most elegant, but this is the only thing that came to my mind):
I created a new column, the one where to store all the calculations, and left it with blank rows.
Then I added to my code the "createdRow" function to set an ID to every cell of both the "Actual Date" column and the new column (in my case the indexes of these columns are "2" and "4")
So now I can point to this cells by calling them with the "rowCallback" function (which works after the table has been generated, but before it is displayed), and inside that function I get the data from the "Actual Date" rows, calculate the new values, and insert it in new column that was left with blank rows:
Note that in my case I had to calculate the difference between dates, so you might not need a few of these lines.
Hope I have been of help to someone.
Cheers!
Hi,
Thanks for posting back. Great to hear that you've got a solution for this. Calculating a value for a row based on a value in a different row is inherently a little tricky. Using
rowCallbackprobably about as good as it gets at the moment.Regards,
Allan
Thanks for the reply!