Author Topic: Totalling Up Values in Different Fields.  (Read 4522 times)

0 Members and 1 Guest are viewing this topic.

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Totalling Up Values in Different Fields.
« on: May 18, 2007, 01:09:31 PM »
I am trying to figure out how to sum up the values in different fields in a particular record.  For example, if in my database, for each record, I have value1, value2........ value9 each with a value between 1 and 3.  I want to have a field say value10 which is the sum of all the values in fields value1 to value9 for a particular record.

All I am trying to do is to dislay the sum of the values of certain fields in the database.

This is kind of similar to what Jason had in this link:
http://www.ezscriptingforums.com/forum/index.php?topic=294.0


Thank you so much in advance.
« Last Edit: May 18, 2007, 01:13:37 PM by vanbao »

Offline ADalby

  • Hero Member
  • *****
  • Posts: 241
    • Uniting Bands - Where Talents Unite! - Music Promotion, Musician Classifieds
Re: Totalling Up Values in Different Fields.
« Reply #1 on: May 18, 2007, 01:33:49 PM »
Here is what I use for a few projects. I cut out a bunch of css to shorten this example but you can see whats going on:


<HTML>
<HEAD>
<TITLE></TITLE>


<script type="text/javascript">
function getElementsWithTagNameAndName(e2,t2,n2){
var findings2=new Array();var els2=e2.getElementsByTagName(t2);
for(var i2=0,l2=els2.length;i2<l2;i2++)if(els2[i2].getAttribute("name")==n2)findings2[findings2.length]=els2[i2];
return findings2;
}
function resolveTable(){
var els=getElementsWithTagNameAndName(document,"div","calcTable")
var spanels=getElementsWithTagNameAndName(document,"span","avg")
var infoels=new Array();
var theGrandTotal=0;
for(var i=0;i<els.length;i++)infoels[infoels.length]=getElementsWithTagNameAndName(els,"td","calcTd");

var finalValue,j,tmpNumber;
for(i=0;i<spanels.length;i++){
finalValue=0;
for(j=0;j<infoels.length;j++){
if(!infoels[j])continue
tmpNumber=infoels[j].innerHTML
if(!tmpNumber)continue

tmpNumber=Number(tmpNumber.replace(/[^0-9eE.]/g,""))
if(isNaN(tmpNumber))continue
finalValue+=tmpNumber
}
theGrandTotal+=finalValue
spanels.innerHTML="<b>$"+finalValue.toFixed(2)+"</b>"
}
var ge=document.getElementById("grandTotal");
if(ge)ge.innerHTML="<b>$"+theGrandTotal.toFixed(2)+"</b>"

}
</script>


</HEAD>
<BODY>

<center>
<br>



<!------------------------>
<div name="calcTable">
<!------------------------>
<table width="675" border="0" cellspacing="1" cellpadding="2" id="table1">
        <tr valign="middle">
        <td width="5" align="center"><b>&nbsp;</td>
        <td width="150" align="center" class="of"><b>Date:</td>
        <td width="225" align="center" class="of"><b>Office Name</td>
        <td width="100" align="center" class="of"><b>Patient Name</td>
        <td width="90" align="center" class="of"><b>Refund Amt</td>
        <td width="100" align="center" class="of"><b>Date Of Service</td>
        </tr>



<template>
        <tr valign="middle">
        <td width="30" align="center"></td>
       <td align="left" class="offf">[[field10]] &nbsp;</td>
       <td align="left" class="offf">[[field2]] &nbsp;</td>
       <td align="left" class="offf">[[field5]] &nbsp;</td>
       <td align="left" class="offf" name="calcTd">$[[field7]] &nbsp;</td>
       <td align="left" class="offf">[[field6]] &nbsp;</td>
   </tr>



</template>
        <tr valign="top">
   <td class=offff bgcolor="#dddddd" colspan="6" align="right"><b><img src="spacer.gif" width="1" height="1"></b></td>
        </tr>

        <tr valign="top">
   <td bgcolor="#ffffff" colspan="6" align="right"><b>Total Amount Refunded: <font color="DA0202"><span

name="avg"></span></font> &nbsp; &nbsp; </b></td>
        </tr>
</table>
<!------------------------>
</div>
<!------------------------>

<script type="text/javascript">
resolveTable();
</script>

</BODY>
</HTML>
Uniting Bands
www.unitingbands.com

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #2 on: May 18, 2007, 02:29:26 PM »
Van,

Do you want the total to actually be written to the DB?  Cuz if that is what you are trying to do, there is a pretty simple addition script I use for an accounting module I have in one of our projects.  We are only using 2 fields, but it could be adapted to add the 9 you are working with.  Let me know, I can paste it her for you.

Jason
Founder of www.slipdex.com

Offline ADalby

  • Hero Member
  • *****
  • Posts: 241
    • Uniting Bands - Where Talents Unite! - Music Promotion, Musician Classifieds
Re: Totalling Up Values in Different Fields.
« Reply #3 on: May 18, 2007, 02:49:33 PM »
Hey Jason, I wouldn't mind checking out that code. Sounds real handy!
Uniting Bands
www.unitingbands.com

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #4 on: May 18, 2007, 03:24:21 PM »
OK, here we go.  Now as I said, we used it for an accounting module, so this forces decimals.  There can be some tweaking done to get rid of the decimal part, but it doesn't hurt anything if you are using whole numbers anyway.

Code: [Select]
<script type="text/javascript">
<!-- Copyright http://javascript.internet.com -->
function startCalc(){
  interval = setInterval("calc()",1);
}
function calc(){
  one = document.orderform.part_price.value;
  two = document.orderform.discount.value;
  document.orderform.final_price.value = (one * 1) - (two * 1);
}
function stopCalc(){
  clearInterval(interval);
}
</script>
<!-- Adding Decimals -->
<script>
function checkDecimal(obj, objStr){
    var objNumber;
    if(isNaN(objStr) && objStr!=''){
        alert('Value entered is not numeric');
        objNumber = '0.00';
    }
    else if(objStr==''){
        objNumber = '0.00';
    }
    else if(objStr.indexOf('.')!=-1){
        if(((objStr.length) - (objStr.indexOf('.')))>3){
            objStr = objStr.substr(0,((objStr.indexOf('.'))+3));
        }
        if(objStr.indexOf('.')==0){
            objStr = '0' + objStr;
        }
        var sLen = objStr.length;
        var TChar = objStr.substr(sLen-3,3);
        if(TChar.indexOf('.')==0){
            objNumber = objStr;
        }
        else if(TChar.indexOf('.')==1){
            objNumber = objStr + '0';
        }
        else if(TChar.indexOf('.')==2){
            objNumber = objStr + '00';
        }
    }
    else{
        objNumber = objStr + '.00';
    }
    obj.value = objNumber;
}
</script>


Now, here is a basic setup I did for the form part:

Code: [Select]
<table border="0" cellpadding="0" cellspacing="0" width="300" id="table9">
<tr>

<td style="padding: 1px" width="80" align="right" valign="top">
<font size="1" face="Arial" color="#28461E">
Price:</font></td>
<td valign="top" style="padding: 1px">
<input type="text" name="part_price" onFocus="startCalc();" onBlur="stopCalc();checkDecimal(this,this.value)"></td>
</tr>
<tr>
<td style="padding: 1px" width="80" align="right" valign="top">
<font size="1" face="Arial" color="#28461E">
Discount:</font></td>
<td valign="top" style="padding: 1px">
<input type="text" name="discount" onFocus="startCalc();" onBlur="stopCalc();checkDecimal(this,this.value)"></td>
</tr>
<tr>
<td style="padding: 1px" width="80" align="right" valign="top">
<font size="1" face="Arial" color="#28461E">
Final Price:</font></td>
<td valign="top" style="padding: 1px">
<input type="text" name="final_price" onFocus="checkDecimal(this,this.value)" onBlur="checkDecimal(this,this.value)"></td>
</tr>
</table>

It is pretty straight forward.  I added the onfocus and onblur to each input box to cover possible actions, but it works great.  Like I said, it could be modified to handle more than 2 fields.

Jason
Founder of www.slipdex.com

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #5 on: May 18, 2007, 03:46:06 PM »
As a matter of fact...  to make it easier for those that are not familiar with js, here is the basic script:

Code: [Select]
<script type="text/javascript">
<!-- Copyright http://javascript.internet.com -->
function startCalc(){
  interval = setInterval("calc()",1);
}
function calc(){
  one = document.orderform.part_price.value;
// "one" is the variable, "document" is a standard call, "orderform" is the name of your form, "part_price" is the name of the field you want to add and "value" is a standard call.
  two = document.orderform.discount.value;
// continue to add variables here
  document.orderform.final_price.value = (one * 1) - (two * 1);
// "document" is a  standard call again, "orderform" again is your form name, "final_price" is the field you want your result to display in, "value" is a standard call.  Then all you do is add in the math functions, for addition you add a "+" sign etc.  Then just repeat the statement with the variable over and over until you include all the fields you want to add.
}
function stopCalc(){
  clearInterval(interval);
}
</script>

I hope that all made sense.  You can actually use an js allowed math function, +, -, *, / etc.

Jason
Founder of www.slipdex.com

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Re: Totalling Up Values in Different Fields.
« Reply #6 on: May 20, 2007, 01:46:25 AM »
Thank you so much Jason and ADalby.  It seems so overwhelming but I try to figure it out.

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Re: Totalling Up Values in Different Fields.
« Reply #7 on: May 20, 2007, 02:47:02 AM »
Jason, is it possible to have modify the script to make it so it will have more than one sum number.  In other word, I have 3 section where section 1 is number 1-10, section 2 is 11-20, and section 3 is 21-30. After each section, I want to script to sum up the values in that section and write it to a field call say "sum1, sum2, sum3".

It looks like the js you posted is designed for only one section.  If I have to sum up the values or section 2, I will have to repeat the script in the header but change the fields to reflect the fields in section 2 or 3.

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #8 on: May 20, 2007, 04:52:23 AM »
Van,

I have never tried it before, but you could try adding a second and third final document line:

  document.formname.sum1.value = (one * 1) + (two * 1);
  document.formname.sum2.value = (three * 1) + (four * 1);
  document.formname.sum3.value = (five * 1) + (six * 1);

Give that a shot, like I said, I never actually tried it before, bu the syntax looks like it should work.

Jason
Founder of www.slipdex.com

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Re: Totalling Up Values in Different Fields.
« Reply #9 on: May 20, 2007, 11:19:17 PM »
Jason,

 I am thinking instead of a
javascript, could might be easier to write a small
subroutine in the read.pl perl script that will sum up
the fields in the database that we specify.  In other
word, in the read.pl script, will have a "sum" tag
that will be something thing like:

$mysum1="R1+R2+R3.......R50"
$mysum2="R510+R52+R53......R75"

and so on.

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Re: Totalling Up Values in Different Fields.
« Reply #10 on: May 20, 2007, 11:23:28 PM »
Regarding my post above, I forgot to note that R1, R2, etc are the field headings.

Is there something wrong with this forum.  I can not seem to be able to modify my post after it is posted.  I thought I used to be able to but it does not seem that I could now.

Offline EZ

  • Hero Member
  • *****
  • Posts: 1081
    • Babelnotes.be
Re: Totalling Up Values in Different Fields.
« Reply #11 on: May 21, 2007, 02:44:45 AM »
Anyone else have the problems vanbao has?  I do not seem to have that problem here.

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #12 on: May 21, 2007, 04:26:37 AM »
Van,

I really can't think of an easier way to total your fields other than that js.  If you need more than one field to collect sums and the multiple document lines are not working, you can always just use multiple instances of the script.  If I have time, maybe you can just post everything you are trying to do and I can write the script out for you.  If I have time I will give it a test run on my side to ensure I get it working.

Jason
Founder of www.slipdex.com

Offline vanbao

  • Hero Member
  • *****
  • Posts: 109
Re: Totalling Up Values in Different Fields.
« Reply #13 on: May 21, 2007, 10:14:41 AM »
Jason,

Thank you for the offer.  I just paid someone to try to modify the csvread script to discplace the total value of specified number of fields.  I will share it with you guys when that is done. 

I am also interested in how it could be done using a js.  Thus, if you have time to give it a try, it will be awesome. Here is what I am trying to do.  Notice the dash lines separate the sections and I want a total for each of those section.  It would be very cool if we can get the js to sum up the section and then right it to the database under a specified field name.
http://www.nailsnow.net/members_registration.shtml

As for the forum, I just found out what happen.  for some reasons, the forum is not resizing to fit the screen.  Thus, I have to scroll to the right to read the who post.  Since the modify button is all the way to the right, I didn't see it.  Here is a screenshot of what I mean.
http://www.nailsnow.net/forum.gif


« Last Edit: May 21, 2007, 10:20:18 AM by vanbao »

Offline Jason

  • Administrator
  • Hero Member
  • *****
  • Posts: 1018
    • slipdex
Re: Totalling Up Values in Different Fields.
« Reply #14 on: May 21, 2007, 10:28:38 AM »
Van,

The screen size sized the way it did because I used a code block so as to not wrap the js code.  I will look at your project and update the js to work.  I would not modify the csv scripts but that is just my opinion.  When the updates are available, there is no way to know if they are compliant with the hacks you've added.

Jason
Founder of www.slipdex.com