the number of minutes between two timefields
This page is about calculating with time-fields. If you are looking for a way to add a timefield to your CRF, have a look at this page, where you can find just that.
And the page you are currently reading is remarkably similar to this page where you can find instructions how to calculate the difference between two dates.
Let's start at the end: this is what we want:
fig. 1: what we want
start easy
If you can't wait to see it in action, click here for the XL-file.
It looks like this:
fig. 2: the XL
"That won't do the trick" you'll say and right you are: we need some java-script to do the math. We stored that in the instructions-part of the Section:
fig. 3: the script in the section
the works
<script src="includes/jmesa/jquery.min.js">// for OC versions before 3.1.4, use jquery-1.3.2.min.js !</script>
<script lang="Javascript">
$.noConflict();
jQuery(document).ready(function($){
//find out who's who
var fieldTimeField1 = $("#TimeField1").parent().parent().find("input");
var fieldTimeField2 = $("#TimeField2").parent().parent().find("input");
var fieldDifference = $("#Difference").parent().parent().find("input");
function calculateMinutes(fieldTimeField){
// retrieve values
var TimeAsText = fieldTimeField.val();
// check if field is filled
if (TimeAsText!=""){
// split and determine hours and minutes
var splitString = TimeAsText.split(":");
var hours = splitString[0];
var minutes = splitString[1];
return (60*hours) + (1*minutes);
}
}
function calculateDifference(){
var Diff = calculateMinutes(fieldTimeField2) - calculateMinutes(fieldTimeField1);
if (fieldDifference.val() != Diff){
fieldDifference.val(Diff);
fieldDifference.change();
}
}
// fire when anything is entered
fieldTimeField1.keyup(function(){
calculateDifference();
});
fieldTimeField2.keyup(function(){
calculateDifference();
});
});
</script>
You can read this script almost from top to bottom. It starts with making references to the items in the CRF:
TimeField1, TimeField2 and Difference.
Then two functions follow: the first one, calculateMinutes
takes a CRF-item as parameter and returns the
minutes since midnight. This function is used by the second function, calculateDifference to calculate the difference (surprise!),
to check if this differs from the value already in field Difference and if so, to set the new value.
Finally we indicate that we want this function to run, every time something is entered in either TimeField1 or TimeField2
with fieldTimeField1.keyup and fieldTimeField2.keyup.
sure, fine, but I need this in a repeating item group
The above is not very impressive if you want to calculate minutes between items in a grid, or repeating-item-group, because then you do not have the right item text to put a div in and refer to that etc, etc. To get the result shown at fig. 4, we need some more java-scripting. This time we put it all in the instructions of the section.
fig. 4: same trick in a repeating item group
The script is a bit harder to explain: see below. (Many thanks to Sander de Ridder and Lindsay Stevens, who came up with a script on the wikibook page on How to use long, external lists: that forms an essential part of this script.)
<script src="includes/jmesa/jquery.min.js">// for OC versions before 3.1.4, use jquery-1.3.2.min.js !</script>
<script lang="Javascript">
$.noConflict();
jQuery(document).ready(function($){
//set group name the same as in XL
var groupName = "MyGroup";
groupName = groupName.toUpperCase();
function calculateMinutes(fieldTimeField){
var TimeAsText = fieldTimeField.val();
// check if field is filled
if (TimeAsText!=""){
// split and determine hours and minutes
var splitString = TimeAsText.split(":");
var hours = splitString[0];
var minutes = splitString[1];
return (60*hours) + (1*minutes);
}
}
function calcDiff(me){
if(me.attr("ID")!=undefined){
var myID = me.attr("ID");
var gIndex = myID.indexOf(groupName);
if(gIndex!=-1){
var myParent = me.parent();
var colNr = $(myParent).parent().children().index($(myParent)) + 1;
if(colNr==1|colNr==2){
//fldF0 is the other time field
var fldF0 = $(myParent).siblings(':eq(0)').children('input:text');
//fldF1 is the field to write the difference in
var fldF1 = $(myParent).siblings(':eq(1)').children('input:text');
var val1 = calculateMinutes(me);
var val2 = calculateMinutes(fldF0);
var valTot = Math.abs(val2-val1);
//ony write the value if it has changed
if (valTot != fldF1.val()){
fldF1.val(valTot);
fldF1.change();
}
}
}
}
}
$("table.aka_form_table").on("keyup", ":input", function(){
calcDiff($(this));
});
});
</script>
Start with the line at the bottom $("table.aka_form_table").on("keyup", ":input", function() where you call a function when in an object of type input something is typed, keyup. You don't want this to happen everywhere, but just in a table that has the class aka_form_table, because this class is used for the RepeatingItemGroup.
Now we go to the top of the script. First we define which Group we are interested in and this we store in groupName. Make sure that you use exactly the same Group name as defined in the CRF. Then in the second line we convert this groupName to uppercase, which may seem odd, but comes in handy when you want to test the CRF in preview-mode: in preview your item will be called MyGroup_0input3344 while in actual data-entry mode it is called IG_TDSDI_MYGROUP_0input3344. (So if you want to testdrive your java-script in preview-mode, just comment out groupName = groupName.toUpperCase();)
We skip function calculateMinutes(), because that works the same as in the other CRF, explained earlier. What's more interesting is the function calcDiff(me), where me is the object in the table where we typed something. First we want to find the ID of this input with myID = me.attr("ID") and then we check if the name of the group we are looking for is in the ID of the input. If not, we're in the wrong table all together and we exit the function.
But if we are in the right table, then we look at the parent of our input, which is a td and the parent of our parent is the table row tr. To find out which column we are in, we ask for the index of our parent and then add 1, because the set of children starts with 0: colNr = $(myParent).parent().children().index($(myParent)) + 1; If the column is 1 or 2, then we must take the two values, convert them into minutes and write the difference to the item Difference.
This can be done with $(myParent).siblings(':eq(0)').children('input:text'), because what it does is that it
goes to the parent, td, and then finds the siblings, so other td's in the tr, where the index starts with 0 and the parent itself
is not included. Confusing? Yes, it is! It's easier if you take the example where you click in one of the inputs of T1.
The parent is now the td in column 1, and this td has 2 siblings: sibling(0) for column 2 and sibling(1) for column3.
Now if we type something in an input in T2, our parent will have again 2 siblings: sibling(0) for column 1 and sibling(1)
again for column3.
Now that is handy, because sibling(0) will always be "the other time-field" and sibling(1) will
always be the difference-field. We subtract the two time-fields, but because we do not know which one is the greater
of the two, we must take the absolute difference, valTot = Math.abs(val2-val1);
However be aware of the fact that we do not check if the first time-field is before the second one.
You may wonder why use children('input:text') when we want the value of "the other time-field". This is because we talk about a collection of objects of a td. In it can be all sorts of things, but we are interested in inputs. However if we would refer to children('input') we would get the collection of inputs in the td and all though you can not see them, there are 2! In each td is not only the visible input, but also a hidden input with the default value of he item. Therefore we must specify we're only interested in inputs of type text.
Have fun scripting your own CRFs (and don't hesitate to ask if you get stuck).
Other how-to-pages can be found here.
this page was last reviewed April 2014