• Status: Solved
• Priority: Medium
• Security: Public
• Views: 193

# Loop through HTML table and calculate field

Hi Experts,
I have a simple problem (I think).  I have a html table (15 rows 6 columns) that I need to loop through each row and check the value of the first cell (ddEarningsType1-15) and depending on the value need to add the value of the third cell (ddhours1-15) to one of 2 text fields (TotNormalHours, TotOTHours) and then sum the TotNormalHours and TotOTHours and populate TotHours text field.  Hope this makes since, but if you need more information just let me know.

TextField = TotNormalHours   TextField = TotOTHours   TextField = TotHours

EarningType     BillingCode     Hours
Normal                     4               8
OT                           45            2.5
0
pwtucker
• 5
• 5
2 Solutions

Commented:
0

Author Commented:
Sure...See if this makes sense below.  Basically I want to calculate the hours cell based on the type of earnings the user selects.  If the user selects normal hours as EarningsType then the hours cell would be added to the TotNormalHours text box and the TotHours text box.  If the user selects OT hours as EarningsType then the hours cell for that row would be added to the TotOTHours text box and the TotHours text box.

Definitions:
EarningsType - Defines whether the record's hours are Over Time or Regular hours
BillingCode - Code that defines task
Hours - Hours user worked on this task
TotNormalHours - Regular hours worked.  Usually the first 40 hours, but not always.
TotOTHours - Over time hours worked.  Usually anything over 40 hours, but not always.
TotHours - All hours worked.  Regural hours + over time hours.

Switch(EarningsType)
{
Case  Normal or Meeting:
TotNormalHours = TotNormalHours + Hours
TotHours = TotHours + Hours
break
Case OT or RoutineOT
TotOTHours = TotOTHours + Hours
TotHours = TotHours + TotOTHours
break
}

Hopefully this makes sense.  The calculations is just summing the hours cell based on the earnings type.

Paul
0

Commented:
Though I am not sure of the user Interface like what will be the input on the screen or anything that will be keyed in by user. Anyways we will start with this UI so that the requirements will get even more clearer. So here is it and try this out:

<html>
<title>Script Demo Gops&reg;</title>
<style>body, table,input{font-family:verdana;font-size:xx-small;}</style>
<script language="javascript">
function calculateOT(){
var tbl=document.getElementById('table');
for(var i=1;i<tbl.rows.length;i++){
var totNorm=40;
var totHrs=0
var totOT=0;
for(var j=0;j<1;j++){
if(tbl.rows[i].cells[0].innerHTML=="Normal" || tbl.rows[i].cells[0].innerHTML=="Meeting"){
totNorm=totNorm+parseInt(tbl.rows[i].cells[2].innerHTML);
totHrs=totHrs+totNorm;
}else{
totOT=parseFloat(tbl.rows[i].cells[2].innerHTML);
totHrs=parseFloat(totOT)+totNorm;
}
}
tbl.rows[i].cells[3].childNodes[0].value=totNorm;
tbl.rows[i].cells[4].childNodes[0].value=totOT;
tbl.rows[i].cells[5].childNodes[0].value=totHrs;
}
}
</script>
<body>
<table width="85%" border="1" cellpadding="0" cellspacing="0" id="table">
<tr>
<th>EarningType</th>
<th>BillingCode</th>
<th>Hours</th>
<th>TotNormalHours</th>
<th>TotOTHours</th>
<th>TotHours</th>
</tr>
<tr>
<td>Normal</td>
<td>4</td>
<td>8</td>
</tr>

<tr>
<td>OT</td>
<td>45</td>
<td>2.5</td>
</tr>

</table>
<br>
<input type="button" value="Calculate" onClick="calculateOT();">
</body>
</html>
0

Commented:
just click the calculate button
0

Author Commented:
I am going to give this a shot.  The user enters information via a dropdown list in the cell.  The earnings type is text and the hours is .5 - 24 in .5 increments.  Not sure if that changes how to reference the value in the cell.
0

Author Commented:
having trouble getting the dropdown value from the cell.

this is a dropdown - tbl.rows[i].cells[0].innerHTML
this is a dropdown - tbl.rows[i].cells[3].childNodes[0].value

It is giving me <STRONG><FONT face="Arial Narrow" size=2> Earnings Type</FONT></STRONG>

0

Commented:
A further progress. Check this code:

<html>
<title>Script Demo Gops&reg;</title>
<style>body, table,input, select{font-family:verdana;font-size:xx-small;}</style>
<script language="javascript">
function calculateOT(){
var tbl=document.getElementById('table');
for(var i=1;i<tbl.rows.length;i++){
var totNorm=40;
var totHrs=0
var totOT=0;
var hrsObj=tbl.rows[i].cells[2].childNodes[0];
var earnObj=tbl.rows[i].cells[0].childNodes[0];
if(earnObj.selectedIndex==0){
earnObj.focus();
return false;
}
if(hrsObj.selectedIndex==0){
hrsObj.focus();
return false;
}
if(earnObj[earnObj.selectedIndex].value=="Normal" || earnObj[earnObj.selectedIndex].value=="Meeting"){
totNorm=totNorm+parseFloat(hrsObj[hrsObj.selectedIndex].value);
totHrs=totHrs+totNorm;
}else{
totOT=parseFloat(hrsObj[hrsObj.selectedIndex].value);
totHrs=parseFloat(totOT)+totNorm;
}
tbl.rows[i].cells[3].childNodes[0].value=totNorm;
tbl.rows[i].cells[4].childNodes[0].value=totOT;
tbl.rows[i].cells[5].childNodes[0].value=totHrs;

}
}
</script>
<body>
<table width="85%" border="1" cellpadding="0" cellspacing="0" id="table">
<tr>
<th>EarningType</th>
<th>BillingCode</th>
<th>Hours</th>
<th>TotNormalHours</th>
<th>TotOTHours</th>
<th>TotHours</th>
</tr>
<tr>
<td>
<select>
<option value="-1">--Select--</option>
<option value="Normal">Normal</option>
<option value="OT">OT</option>
</select>
</td>
<td>4</td>
<td>
<select>
<option value="-1">--Select--</option>
<option value="0.5">0.5</option>
<option value="1.0">1.0</option>
<option value="1.5">1.5</option>
<option value="2.0">2.0</option>
<option value="2.5">2.5</option>
<option value="3.0">3.0</option>
<option value="3.5">3.5</option>
<option value="4.0">4.0</option>
<option value="4.5">4.5</option>
<option value="5.0">5.0</option>
<option value="5.5">5.5</option>
<option value="6.0">6.0</option>
<option value="6.5">6.5</option>
<option value="7.0">7.0</option>
<option value="7.5">7.5</option>
<option value="8.0">8.0</option>
<option value="8.5">8.5</option>
<option value="9.0">9.0</option>
<option value="9.5">9.5</option>
<option value="10.0">10.0</option>
</select>
</td>
</tr>

<tr>
<td>
<select>
<option value="-1">--Select--</option>
<option value="Normal">Normal</option>
<option value="OT">OT</option>
</select>
</td>
<td>45</td>
<td>
<select>
<option value="-1">--Select--</option>
<option value="0.5">0.5</option>
<option value="1.0">1.0</option>
<option value="1.5">1.5</option>
<option value="2.0">2.0</option>
<option value="2.5">2.5</option>
<option value="3.0">3.0</option>
<option value="3.5">3.5</option>
<option value="4.0">4.0</option>
<option value="4.5">4.5</option>
<option value="5.0">5.0</option>
<option value="5.5">5.5</option>
<option value="6.0">6.0</option>
<option value="6.5">6.5</option>
<option value="7.0">7.0</option>
<option value="7.5">7.5</option>
<option value="8.0">8.0</option>
<option value="8.5">8.5</option>
<option value="9.0">9.0</option>
<option value="9.5">9.5</option>
<option value="10.0">10.0</option>
</select>
</td>
</tr>

</table>
<br>
<input type="button" value="Calculate" onClick="calculateOT();">
</body>
</html>
0

Author Commented:
Thanks gops1 this worked great (had to tweak a little).  Let me ask one more question.  Instead of looping through the table is there a way I can validate and calculate as the user enters the data?  For example, they select an earnings type and on the change event I check the type and enable/disable my dropdowns (not all earnings type require hours or code such as mileage or meals).  Then after the user selects their hours I calculate.  Is this possible?  If I need to give more points I will.

0

Commented:
Removed the loop.. here is the new code

<html>
<title>Script Demo Gops&reg;</title>
<style>body, table,input, select{font-family:verdana;font-size:xx-small;}</style>
<script language="javascript">
function calculateOT(o,n){
var totNorm=40;
var totHrs=0
var totOT=0;
var o=document.getElementById(o+n);
var et=document.getElementById("row"+n);
if(et.selectedIndex==0){
et.focus();
return false;
}
if(o.selectedIndex==0){
o.focus();
return false;
}

if(et[et.selectedIndex].value=="Normal" || et[et.selectedIndex].value=="Meeting"){
totNorm=totNorm+parseFloat(o[o.selectedIndex].value);
totHrs=totHrs+totNorm;
}else{
totOT=parseFloat(o[o.selectedIndex].value);
totHrs=parseFloat(totOT)+totNorm;
}
document.getElementById("totNorm"+n).value=totNorm;
document.getElementById("totOtHr"+n).value=totOT;
document.getElementById("totHrs"+n).value=totHrs;

}

function reset(o,n,s){
var o=document.getElementById(o+n);
o.selectedIndex=0;
o.disabled=false;
if(s[s.selectedIndex].value=="Meals" || s[s.selectedIndex].value=="Mileage"){
o.disabled=true;
}
}
</script>
<body>
<table width="85%" border="1" cellpadding="0" cellspacing="0" id="table">
<tr>
<th>EarningType</th>
<th>BillingCode</th>
<th>Hours</th>
<th>TotNormalHours</th>
<th>TotOTHours</th>
<th>TotHours</th>
</tr>
<tr>
<td>
<select id="row1" onchange="reset('hrs',1,this)">
<option value="-1">--Select--</option>
<option value="Normal">Normal</option>
<option value="OT">OT</option>
<option value="Meals">Meals</option>
<option value="Mileage">Mileage</option>
</select>
</td>
<td>4</td>
<td>
<select id="hrs1" onchange="calculateOT('hrs',1)">
<option value="-1">--Select--</option>
<option value="0.5">0.5</option>
<option value="1.0">1.0</option>
<option value="1.5">1.5</option>
<option value="2.0">2.0</option>
<option value="2.5">2.5</option>
<option value="3.0">3.0</option>
<option value="3.5">3.5</option>
<option value="4.0">4.0</option>
<option value="4.5">4.5</option>
<option value="5.0">5.0</option>
<option value="5.5">5.5</option>
<option value="6.0">6.0</option>
<option value="6.5">6.5</option>
<option value="7.0">7.0</option>
<option value="7.5">7.5</option>
<option value="8.0">8.0</option>
<option value="8.5">8.5</option>
<option value="9.0">9.0</option>
<option value="9.5">9.5</option>
<option value="10.0">10.0</option>
</select>
</td>
<td><input type="text" id="totNorm1" value="" style="background-color:#cccccc;" readonly=true></td>
<td><input type="text" id="totOtHr1" value="" style="background-color:#cccccc;" readonly=true></td>
<td><input type="text" id="totHrs1" value="" style="background-color:#cccccc;" readonly=true></td>
</tr>

<tr>
<td>
<select id="row2" onchange="reset('hrs',2,this)">
<option value="-1">--Select--</option>
<option value="Normal">Normal</option>
<option value="OT">OT</option>
<option value="Meals">Meals</option>
<option value="Mileage">Mileage</option>
</select>
</td>
<td>45</td>
<td>
<select id="hrs2" onchange="calculateOT('hrs',2)">
<option value="-1">--Select--</option>
<option value="0.5">0.5</option>
<option value="1.0">1.0</option>
<option value="1.5">1.5</option>
<option value="2.0">2.0</option>
<option value="2.5">2.5</option>
<option value="3.0">3.0</option>
<option value="3.5">3.5</option>
<option value="4.0">4.0</option>
<option value="4.5">4.5</option>
<option value="5.0">5.0</option>
<option value="5.5">5.5</option>
<option value="6.0">6.0</option>
<option value="6.5">6.5</option>
<option value="7.0">7.0</option>
<option value="7.5">7.5</option>
<option value="8.0">8.0</option>
<option value="8.5">8.5</option>
<option value="9.0">9.0</option>
<option value="9.5">9.5</option>
<option value="10.0">10.0</option>
</select>
</td>
<td><input type="text" id="totNorm2" value="" style="background-color:#cccccc;" readonly=true></td>
<td><input type="text" id="totOtHr2" value="" style="background-color:#cccccc;" readonly=true></td>
<td><input type="text" id="totHrs2"  value="" style="background-color:#cccccc;" readonly=true></td>
</tr>

</table>
</body>
</html>
0

Author Commented:
Thanks gops1.  Not only did you solve my problem, I also learned a little bit in the process.  The bad thing is now I am going after more functionality, but will post another question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.