Help formatting calculation result needed

**poncho** · Nov 3, 2005, 09:56

I'm having a bit of bother with a 'total' calculation. This will be a group of tables each with a unique id, the rows will be brought in from a database, so they will need iterated over.

Everything works so far, except the calculation to work out totals for all rows in a table.

To explain further;
I have a table (table id = administration) row with 'total_hrs' which will have the table id tacked onto the start of it and the row number tacked onto the end, like so "administration_total_hrs0" for first row.

Basically I need to add the values of "administration_total_hrs0", "administration_total_hrs1", "administration_total_hrs2" etc and append the result to another field (administration_totaldepthrs).

At the moment I'm getting "NaN" displayed in this field, I'm not sure how to take the result of the total and format it in the "administration_totaldepthrs" field properly.

Any help would be greatly appreciated. I've included the offending javascript and html.

HTML Code:

<table id="administration">
            <caption align="top">
              Weekly Payroll Report 
            </caption>
		    <thead>
              <tr>
                <td class="col1">Front Desk</td>
                <td class="col2">Total Hrs.</td>
                <td class="col3">Reg. Hrs.</td>
                <td class="col4">Reg. Rate</td>
                <td class="col5">O.T. Hrs.</td>
                <td class="col6">O.T. Rate</td>
                <td class="col7">Gross</td>
                <td class="col8">Holiday Hrs.</td>
                <td class="col9">Holiday Pay</td>
              </tr>
            </thead>
            <tbody id="administration_body">
              <tr>
                <td class="col1">Employee Name</td>
                <td class="col2"><input name="administration_total_hrs0" type="text" id="administration_total_hrs0" onchange="calcPayroll();" /></td>
                <td class="col3"><input name="administration_reg_hrs0" type="text" id="administration_reg_hrs0" onchange="calcPayroll();" /></td>
                <td class="col4"><input name="administration_reg_rate0" type="text" id="administration_reg_rate0" onchange="calcPayroll();" /></td>
                <td class="col5"><input name="administration_ot_hrs0" type="text" id="administration_ot_hrs0" onchange="calcPayroll();" /></td>
                <td class="col6"><input name="administration_ot_rate0" type="text" id="administration_ot_rate0" onchange="calcPayroll();" /></td>
                <td class="col7"><input name="administration_gross0" type="text" id="administration_gross0" onchange="calcPayroll();" /></td>
                <td class="col8"><input name="administration_holiday_hrs0" type="text" id="administration_holiday_hrs0" onchange="calcPayroll();" /></td>
                <td class="col9"><input name="administration_holiday_pay0" type="text" id="administration_holiday_pay0" onchange="calcPayroll();" /></td>
              </tr>
			  <tr>
                <td class="col1">Employee Name</td>
                <td class="col2"><input name="administration_total_hrs1" type="text" id="administration_total_hrs1" onchange="calcPayroll();" /></td>
                <td class="col3"><input name="administration_reg_hrs1" type="text" id="administration_reg_hrs1" onchange="calcPayroll();" /></td>
                <td class="col4"><input name="administration_reg_rate1" type="text" id="administration_reg_rate1" onchange="calcPayroll();" /></td>
                <td class="col5"><input name="administration_ot_hrs1" type="text" id="administration_ot_hrs1" onchange="calcPayroll();" /></td>
                <td class="col6"><input name="administration_ot_rate1" type="text" id="administration_ot_rate1" onchange="calcPayroll();" /></td>
                <td class="col7"><input name="administration_gross1" type="text" id="administration_gross1" onchange="calcPayroll();" /></td>
                <td class="col8"><input name="administration_holiday_hrs1" type="text" id="administration_holiday_hrs1" onchange="calcPayroll();" /></td>
                <td class="col9"><input name="administration_holiday_pay1" type="text" id="administration_holiday_pay1" onchange="calcPayroll();" /></td>
              </tr>
            </tbody>
            <tfoot>
              <tr>
                <td colspan="9" align="right" valign="top"><table border="0" cellpadding="0" cellspacing="0">

                    <tr>
                      <td align="left" valign="top">&nbsp;</td>
                      <td width="50%" align="left" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
                          <tr>
                            <td align="right"><label for="administration_totaldepthrs">Total Dept Hours </label></td>
                            <td width="10">&nbsp;</td>
                            <td width="90"><input type="text" name="administration_totaldepthrs" id="administration_totaldepthrs" /></td>
                          </tr>
                          <tr>
                            <td align="right"><label for="administration_totalholidayhrs">Total Holiday Hours </label></td>
                            <td width="10">&nbsp;</td>
                            <td width="90"><input type="text" name="administration_totalholidayhrs" id="administration_totalholidayhrs" /></td>
                          </tr>
                          <tr>
                            <td align="right"><label for="administration_totalholidaypay">Total Holiday Pay </label></td>
                            <td width="10">&nbsp;</td>
                            <td width="90"><input type="text" name="administration_totalholidaypay" id="administration_totalholidaypay" /></td>
                          </tr>
                          <tr>
                            <td align="right"><label for="administration_totalgrosspay">Total Gross Dept Pay </label></td>
                            <td width="10">&nbsp;</td>
                            <td width="90"><input type="text" name="totalgrosspay" id="administration_totalgrosspay" /></td>
                          </tr>
                      </table></td>
                    </tr>
                </table></td>
              </tr>
              <tr>
                <td colspan="9" align="right" valign="top"><input name="calc" type="button" id="calc" value="Calculate" onclick="calcPettyFields();" />
                    <input name="Submit" type="submit" id="Submit" value="Submit" />                </td>
              </tr>
            </tfoot>
          </table>

HTML Code:

function calcPayroll() {
	var depts_array,PayrollTable,PayrollBody,row,i,j,sum,total_hrs,reg_hrs,reg_rate,ot_hrs,ot_rate,gross,
	holiday_hrs,holiday_pay,total_pay,totaldepthrs,totalholidayhrs,totalholidaypay,totalgrosspay,
	totaldepthrs_tmp,totalholidayhrs_tmp,totalholidaypay_tmp,totalgrosspay_tmp;
	depts_array = Array('administration','front_desk','night_audit','housekeeping','laundry','maintenance','houseman','breakfast');
	sum = 0;
	var Table = document.getElementsByTagName('table');
	for(j=0;j<Table.length;j++) {
	
		totaldepthrs = document.getElementById(depts_array[j]+'_totaldepthrs');
		totalholidayhrs = document.getElementById(depts_array[j]+'_totalholidayhrs');
		totalholidaypay = document.getElementById(depts_array[j]+'_totalholidaypay');
		totalgrosspay = document.getElementById(depts_array[j]+'_totalgrosspay');
		
		PayrollBody = document.getElementById(depts_array[j]+'_body');
		row = PayrollBody.getElementsByTagName('TR');
		for(i=0;i<row.length;i++) {
			total_hrs = document.getElementById(depts_array[j]+'_total_hrs'+i);
			reg_hrs = document.getElementById(depts_array[j]+'_reg_hrs'+i);
			reg_rate = document.getElementById(depts_array[j]+'_reg_rate'+i);
			ot_hrs = document.getElementById(depts_array[j]+'_ot_hrs'+i);
			ot_rate = document.getElementById(depts_array[j]+'_ot_rate'+i);
			gross = document.getElementById(depts_array[j]+'_gross'+i);
			holiday_hrs = document.getElementById(depts_array[j]+'_holiday_hrs'+i);
			holiday_pay = document.getElementById(depts_array[j]+'_holiday_pay'+i);
			
			
			ot_hrs.value = (total_hrs.value - reg_hrs.value);
			ot_rate.value = (reg_hrs.value * 1.5);
			gross.value = ((reg_hrs.value * reg_rate.value) + (ot_hrs.value * ot_rate.value));
			var holiday_pay_tmp = (holiday_hrs.value * reg_rate.value);
			holiday_pay.value = holiday_pay_tmp.toFixed(2);
			
			totaldepthrs_tmp += Number(total_hrs.value);
			totalholidayhrs_tmp += Number(holiday_hrs.value);
			totalholidaypay_tmp += Number(holiday_pay.value);
			totalgrosspay_tmp += Number(gross.value);
		}
		
		totaldepthrs.value = totaldepthrs_tmp;
		totalholidayhrs.value = totalholidayhrs_tmp;
		totalholidaypay.value = totalholidaypay_tmp;
		totalgrosspay.value = totalgrosspay_tmp;
	}
}

**vwphillips** · Nov 3, 2005, 12:35

main problem is the the value of a textbox is a string which needs convertion to digits prior to calculation by

Number('stringvalue');
parseInt('stringvalue');
parseFloat('stringvalue');

the forms a bit big to work on at the moment but
http://www.vicsjavascripts.org.uk/Fo...pendium.htm#f3
may give you some ideas

**poncho** · Nov 3, 2005, 13:03

I tried forcing the string to a number with the Number() function to no avail. If I concatenate the strings without using Number(), parseFloat() or parseInt() I'm getting "undefined1020" which is "undefined", first element value and second element value.

Code (last block of code inside the secondary for loop):

Code:

function calcPayroll() {
	var depts_array,PayrollTable,PayrollBody,row,i,j,sum,total_hrs,reg_hrs,reg_rate,ot_hrs,ot_rate,gross,
	holiday_hrs,holiday_pay,total_pay,totaldepthrs,totalholidayhrs,totalholidaypay,totalgrosspay,
	totaldepthrs_tmp,totalholidayhrs_tmp,totalholidaypay_tmp,totalgrosspay_tmp;
	depts_array = Array('administration','front_desk','night_audit','housekeeping','laundry','maintenance','houseman','breakfast');
	sum = 0;
	var Table = document.getElementsByTagName('table');
	for(j=0;j<Table.length;j++) {
	
		totaldepthrs = document.getElementById(depts_array[j]+'_totaldepthrs');
		totalholidayhrs = document.getElementById(depts_array[j]+'_totalholidayhrs');
		totalholidaypay = document.getElementById(depts_array[j]+'_totalholidaypay');
		totalgrosspay = document.getElementById(depts_array[j]+'_totalgrosspay');
		
		PayrollBody = document.getElementById(depts_array[j]+'_body');
		row = PayrollBody.getElementsByTagName('TR');
		for(i=0;i<row.length;i++) {
			total_hrs = document.getElementById(depts_array[j]+'_total_hrs'+i);
			reg_hrs = document.getElementById(depts_array[j]+'_reg_hrs'+i);
			reg_rate = document.getElementById(depts_array[j]+'_reg_rate'+i);
			ot_hrs = document.getElementById(depts_array[j]+'_ot_hrs'+i);
			ot_rate = document.getElementById(depts_array[j]+'_ot_rate'+i);
			gross = document.getElementById(depts_array[j]+'_gross'+i);
			holiday_hrs = document.getElementById(depts_array[j]+'_holiday_hrs'+i);
			holiday_pay = document.getElementById(depts_array[j]+'_holiday_pay'+i);
			
			
			ot_hrs.value = (total_hrs.value - reg_hrs.value);
			ot_rate.value = (reg_hrs.value * 1.5);
			gross.value = ((reg_hrs.value * reg_rate.value) + (ot_hrs.value * ot_rate.value));
			var holiday_pay_tmp = (holiday_hrs.value * reg_rate.value);
			holiday_pay.value = holiday_pay_tmp.toFixed(2);
			
			totaldepthrs_tmp += total_hrs.value;
			totalholidayhrs_tmp += Number(holiday_hrs.value);
			totalholidaypay_tmp += Number(holiday_pay.value);
			totalgrosspay_tmp += Number(gross.value);
		}
		
		totaldepthrs.value = totaldepthrs_tmp;
		totalholidayhrs.value = totalholidayhrs_tmp;
		totalholidaypay.value = totalholidaypay_tmp;
		totalgrosspay.value = totalgrosspay_tmp;
	}
}

Have you an idea where the "undefined" is coming from? I haven't done much javascript work for a while, so this is completely throwing me

I'm thinking because I have to grab the element id by loop index

Code:

total_hrs = document.getElementById(depts_array[j]+'_total_hrs'+i);

,that may be causing some problems.

I really appreciate your help on this. Considering telling the client he'd be better off with a spreadsheet!

Cheers;
Poncho

**7stud** · Nov 3, 2005, 14:44

See if this helps:

Code:

<html>
<head><title></title>
<script type="text/javascript" language="javascript">
<!-- Hide from browsers without javascript

/*
Basically I need to add the values of "administration_total_hrs0", "administration_total_hrs1", "administration_total_hrs2" etc and append the result to another field (administration_totaldepthrs).
*/

function calcTotal()
{
	var id1 = "administration";
	var id2 = "_total_hours";
	var length = 3;
	var total = 0;
	for(var i = 0; i < length; i++)
	{
		var elmt = document.getElementById(id1 + id2 + i);
		var num = 0;
		if ( isNaN(num = Number(elmt.value)) )
		{
			alert("Element: " + id1 + id2 + i + 
				" is not a number.\nIt will not be included in the total.");
		}
		else
		{
			total += num;
		}
	}

	document.getElementById("administration_totaldepthrs").innerHTML = total;
}

// End hiding -->
</script>
</head>
<body>

<table id="administration">
<thead></thead>
<tfoot></tfoot>
<tbody>
<tr>
<td><input type="text" id="administration_total_hours0" /></td>
<td><input type="text" id="administration_total_hours1" /></td>
<td><input type="text" id="administration_total_hours2" /></td>
</tr>

<tr><td>total:</td><td id="administration_totaldepthrs"></td></tr>
<tr><td><input type="button" id="b0" name="b0" value="Get Total" onclick="calcTotal()" /></td></tr>

</tbody>
</table>

</body>
</html>

**vwphillips** · Nov 3, 2005, 14:53

ot_hrs.value = (total_hrs.value - reg_hrs.value);

is still attempting to subtract strings

the link I posted should have demonstrated that using ids is hard work

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>
  <title></title>
<script language="JavaScript" type="text/javascript">
<!--

function Calculate(id){
 obj=document.getElementById(id);
 rows=obj.getElementsByTagName('TR');
 for (zxc0=0;zxc0<rows.length;zxc0++){
  inputs=rows[zxc0].getElementsByTagName('INPUT');
  for (zxc1=0;zxc1<inputs.length;zxc1++){
   inputs[zxc1].value=inputs[zxc1].value.replace(/\D/g,'');
  }
  if (inputs[0].value.length&&inputs[1].value.length){
   inputs[2].value=inputs[0].value*1-inputs[1].value*1;
  }
  if (inputs[2].value.length&&inputs[3].value.length){
   inputs[4].value=inputs[2].value*inputs[3].value;
  }
 }
}

//-->
</script>

</head>

<body>
Complete fields 0, 1 click field 2<br>
Complete fields 3 click field 4<br>
<table cellpadding="0" cellspacing="0" border="1">
 <tbody id="fred" >
  <tr>
    <td><input size=5  onblur="Calculate('fred');" ></td>
    <td><input size=5  onblur="Calculate('fred');"></td>
    <td><input size=5 disabled='disabled' style="background-color:silver;" ></td>
    <td><input size=5  onblur="Calculate('fred');" ></td>
    <td><input size=5 disabled='disabled' style="background-color:silver;" ></td>
  </tr>
  <tr>
    <td><input size=5  onblur="Calculate('fred');" ></td>
    <td><input size=5  onblur="Calculate('fred');"></td>
    <td><input size=5 disabled='disabled' style="background-color:silver;" ></td>
    <td><input size=5  onblur="Calculate('fred');" ></td>
    <td><input size=5 disabled='disabled' style="background-color:silver;" ></td>
  </tr>
 </tbody>
</table>
</body>

</html>

**7stud** · Nov 3, 2005, 14:58

You might want to use total.toFixed(2) as well.

**poncho** · Nov 3, 2005, 15:20

Thank you 7stud, that did the trick!

This has been my first foray into javascript for quite a while, I think I'll stick to PHP!

Thanks again

**poncho** · Nov 3, 2005, 15:56

Thanks for all your help guys, I got it sorted out in the end.

7stud, yeah I got the toFixed() function going thanks.

wwphillips, yes using ids sucks but I don't have a better idea at the moment. I have a few more of this type of page to do and not a lot of time. As I sid in my last comment, I think I'll stick to PHP from here on in. I'll leave javascript to someone more experienced than myself.

Cheers;
Poncho

**poncho** · Nov 8, 2005, 11:01

I've got all the calucation sorted now. I now have a problem with the total figures being looped when they shouldn't. I would really appreciate if someone would have a look and point out my ridiculous mistake, because I can't see the wood for the trees!

http://www.burodefunk.com/mark/temp/

Basically, we have a list of tables. The no. of tables can vary, and so can the no. of rows inside each table. All the math for each row is fine, AFAIK. The problem is that the totals for table1 are bing set to all tables. I know I have my loops all messed up, but I'm not sure how to fix it. It doesn't need to be pretty, it just needs to work

Any help would be appreciated.

Cheers;
Poncho

**poncho** · Nov 8, 2005, 17:51

Can anybody help me with this one? Any help would be greatly appreciated.

Please ignore the size of the example page, it's only the per-table totals I'm having trouble with, or more specifically, the order in which I have my 'for' loops I think.

I have commented the javascript code for ease of deduction. The code in question starts on line 75 of the example page. (http://www.burodefunk.com/mark/temp/)

If anybody has any ideas, I'd love to hear them. Without wading deep in the code, can anyone give me any tips for a page like this? ie. Should I be splitting this up into several 'helper' functions or anything like that? As I said, I'm not really that great at javascript.

Cheers;
Poncho

**poncho** · Nov 9, 2005, 18:45

I've re-written the javascript, splitting it into a function that handles all the calculations inside a table and a 'caller' function that calls the other one per table.

I've updated the example page accordingly if anybody needs a similar solution. Also, I'm using a function from the Prototype library "getElementsByClassName", very handy.

Cheers;
Poncho

The SitePoint Forums have moved.

User Tag List

Thread: Help formatting calculation result needed

Thread Tools

Display

Help formatting calculation result needed (Solved!)

Solved!

Bookmarks

Bookmarks

Posting Permissions