6 Replies Latest reply on Jun 10, 2010 10:36 PM by jon@cmiwebstudio

    sum data from three columns

    jon@cmiwebstudio Level 3

      ok, so i have a grid of multiple rows and three columns, and what i need to be able to do is sum the total of each of the three columns when one checkbox is selected.  check box indicates this item needs to be summed, and each one has three values (price comparison chart).  so say column one is $1, column two is $2, and column 3 is $3, when i select checkout box "xyz" i will get three totals.... $1, $2, and $3 at the bottom of each column.

       

      i'm trying to use javascript, and this works really well with one column of numbers being summed, but summing 3 columns from one check box, i can't figure this out.  can anyone point me in the right direction?  any open source, extensions, paid extensions, anything?

        • 1. Re: sum data from three columns
          David_Powers Adobe Community Professional

          The following page does what I think you want:

           

          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
          <html xmlns="http://www.w3.org/1999/xhtml">
          <head>
          <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
          <title>Sum Columns</title>
          <script type="text/javascript">
          function calculateTotals(e) {
               var target = window.event ? window.event.srcElement : e ? e.target : null;
               var table = document.getElementById('prices');
               var rows = table.getElementsByTagName('tr');
               var len = rows.length-1, cells;
               var total1 = total2 = total3 = 0;
               if (target.checked) {
                 for (var i = 1; i < len; i++) {
                   cells = rows[i].getElementsByTagName('td');
                   total1 += Number(cells[0].innerHTML.substring(1));
                   total2 += Number(cells[1].innerHTML.substring(1));
                   total3 += Number(cells[2].innerHTML.substring(1));
                 }
                 cells = rows[len].getElementsByTagName('td');
                 cells[0].innerHTML = '$' + total1;
                 cells[1].innerHTML = '$' + total2;
                 cells[2].innerHTML = '$' + total3;
               } else {
                 cells = rows[len].getElementsByTagName('td');
                 cells[0].innerHTML = '';
                 cells[1].innerHTML = '';
                 cells[2].innerHTML = '';
               }
          }
          </script>
          </head>
          
          <body>
          <table width="400" id="prices">
            <tr>
              <th scope="col">Product</th>
              <th scope="col">Shop A</th>
              <th scope="col">Shop B</th>
              <th scope="col">Shop C</th>
            </tr>
            <tr>
              <th scope="row">Item 1</th>
              <td>$20</td>
              <td>$30</td>
              <td>$23</td>
            </tr>
            <tr>
              <th scope="row">Item 2</th>
              <td>$13</td>
              <td>$23</td>
              <td>$14</td>
            </tr>
            <tr>
              <th scope="row">Item 3</th>
              <td>$34</td>
              <td>$35</td>
              <td>$36</td>
            </tr>
            <tr>
              <th scope="row">Total</th>
              <td> </td>
              <td> </td>
              <td> </td>
            </tr>
          </table>
          <form id="form1" name="form1" method="post" action="">
            <input type="checkbox" name="showTotals" id="showTotals" value="showTotals"
             onclick="calculateTotals(event)" />
            <label for="showTotals">Show totals</label>
          </form>
          </body>
          </html>
          
          
          1 person found this helpful
          • 2. Re: sum data from three columns
            jon@cmiwebstudio Level 3

            thank you very much.  not quite what i wanted, so if it's not too much to ask, could i ask for a tweak?  this is a huge help.

             

            i need onblur results so the totals are added as the check boxes next to each row are checked.  So using your example, a checkbox next to item 1, item 2, and item 3, and when i check the checkbox to item 1, i get instant totals for shop a, b, and c in the total row.  and like wise, if i were to also select checkbox for item 2, it would add to those totals as well for shop a, b, and c so you would view the total cost of item 1 and item 2 summed.  thank you so much for this help... javascript is not my specialty obviously.

            • 3. Re: sum data from three columns
              David_Powers Adobe Community Professional

              JavaScript  isn't my speciality, either. The following page adds the values from the current row to the totals and subtracts them, depending on whether the checkbox is selected.

               

              <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
              <html xmlns="http://www.w3.org/1999/xhtml">
              <head>
              <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
              <title>Sum Columns</title>
              <script type="text/javascript">
              function calculateTotals(e) {
                   var target = window.event ? window.event.srcElement : e ? e.target : null;
                   var row = target.parentNode.parentNode;
                   var cells = row.getElementsByTagName('td');
                   var val1 = Number(cells[0].innerHTML.substring(1));
                   var val2 = Number(cells[1].innerHTML.substring(1));
                   var val3 = Number(cells[2].innerHTML.substring(1));
                   var total1 = document.getElementById('total1');
                   var total2 = document.getElementById('total2');
                   var total3 = document.getElementById('total3');
                   if (target.checked) {
                       total1.innerHTML = '$' +  (val1 + Number(total1.innerHTML.substring(1)));
                       total2.innerHTML = '$' +  (val2 + Number(total2.innerHTML.substring(1)));
                       total3.innerHTML = '$' +  (val3 + Number(total3.innerHTML.substring(1)));
                   } else {
                       total1.innerHTML = '$' +  (Number(total1.innerHTML.substring(1)) - val1);
                       total2.innerHTML = '$' +  (Number(total2.innerHTML.substring(1)) - val2);
                       total3.innerHTML = '$' +  (Number(total3.innerHTML.substring(1)) - val3);     
                   }
                   
              }
              </script>
              </head>
              
              <body>
              <form id="form1" name="form1" method="post" action="">
                <table width="400" id="prices">
                  <tr>
                    <th scope="col">Product</th>
                    <th scope="col">Shop A</th>
                    <th scope="col">Shop B</th>
                    <th scope="col">Shop C</th>
                    <th scope="col"> </th>
                  </tr>
                  <tr>
                    <th scope="row">Item 1</th>
                    <td>$20</td>
                    <td>$30</td>
                    <td>$23</td>
                    <td><input type="checkbox" name="getTotal1" id="getTotal1" onclick="calculateTotals(event)" /></td>
                  </tr>
                  <tr>
                    <th scope="row">Item 2</th>
                    <td>$13</td>
                    <td>$23</td>
                    <td>$14</td>
                    <td><input type="checkbox" name="getTotal2" id="getTotal2" onclick="calculateTotals(event)" /></td>
                  </tr>
                  <tr>
                    <th scope="row">Item 3</th>
                    <td>$34</td>
                    <td>$35</td>
                    <td>$36</td>
                    <td><input type="checkbox" name="getTotal3" id="getTotal3" onclick="calculateTotals(event)" /></td>
                  </tr>
                  <tr>
                    <th scope="row">Total</th>
                    <td id="total1">$0</td>
                    <td id="total2">$0</td>
                    <td id="total3">$0</td>
                    <td> </td>
                  </tr>
                </table>
              </form>
              </body>
              </html>
              
              

               

              It relies on the first cell in each row being <th>, rather than <td>. So, cells[0] inside the function refers to the first <td> in the row. It also requires the three total cells to have IDs.

               

              A more expert JavaScript programmer might be able to produce a more elegant solution, but this seems to do what you want.

              • 4. Re: sum data from three columns
                jon@cmiwebstudio Level 3

                fantastic!  this helped out tremendously.  thanks very much!

                 

                fyi, with javascript, how would one truncate the results, getting a few frisky sums like $5.4350000000000005 .

                 

                also wondering to set the 3 totals to a $_POST var so i can use it on other pages.  i know javascript to php is difficult because of the their nature, so if it's that's not possible, how could i modify the operation of the javascript to trigger on bodyload, automatically generating sums based on what's there?

                • 5. Re: sum data from three columns
                  David_Powers Adobe Community Professional

                  I played around with the function, and used some nested functions to carry out the repeated processes. I haven't tested it with lots of different numbers, but it seems to behave quite well in my limited test:

                   

                  function calculateTotals(e) {
                       var target = window.event ? window.event.srcElement : e ? e.target : null;
                       var row = target.parentNode.parentNode;
                       var cells = row.getElementsByTagName('td');
                       var total1 = document.getElementById('total1');
                       var total2 = document.getElementById('total2');
                       var total3 = document.getElementById('total3');
                       var getTotal = target.checked ? add : subtract;
                       total1.innerHTML = '$' +  getTotal(getVal(cells[0]), getVal(total1));
                       total2.innerHTML = '$' +  getTotal(getVal(cells[1]), getVal(total2));
                       total3.innerHTML = '$' +  getTotal(getVal(cells[2]), getVal(total3));
                       update('t1', total1);
                       update('t2', total2);
                       update('t3', total3);
                       function getVal(cell) {
                            return Number(Number(cell.innerHTML.substring(1)).toFixed(2));
                       }
                       function add(a, b) {
                            return (a+b).toFixed(2);
                       }
                       function subtract(a, b) {
                            return (b-a).toFixed(2);
                       }
                       function update(field, total) {
                            document.getElementById(field).setAttribute('value', total.innerHTML);
                       }
                  }
                  

                   

                  As for converting the totals to $_POST values, this revised function assumes you have three hidden fields called t1, t2, and t3. It updates them automatically. The drawback with this approach is that it relies on JavaScript. A more robust approach would be to use the checkboxes to identify the rows the customer has selected, and perform the calculation on the server.

                  1 person found this helpful
                  • 6. Re: sum data from three columns
                    jon@cmiwebstudio Level 3

                    thanks!  that worked perfectly for my circumstances with almost no changes.

                     

                    I've bought 2 of your books so I hope it made it worth your time to do this.