Jump to content

Sum-total of Numerical Values of Data in Columns within a Row


My Sharona
Go to solution Solved by Nathan Explosion,

Recommended Posts

13 hours ago, Miss_B said:

Can you please provide more details? The more the better.

I have a table, and I am adding numbers to each row via columns. I need the row to add the numbers at the end of the column and display the sum total of all numbers in that row. It needs to work much like a spreadsheet in Excel or Open Office.

Code I have that works in other environments, won't work in the IPS environment.

Thanks.

Edited by My Sharona
Link to comment
Share on other sites

7 hours ago, Nathan Explosion said:

A SQL table?

A Pages database table?

A form helper table?

What is the code?

Gotcha. Know I know what @Miss_B meant, thanks.

I am trying to add it to a Page Builder: Text Block

Chat GPT gives me html with some Java

<!DOCTYPE html>
<html>
<head>
    <title>simple code for row sums</title>
    <style>
        table {
            border-collapse: collapse;
            width: 50%;
            margin: auto;
        }
        th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: center;
        }
    </style>
</head>
<body>

<table>
    <thead>
        <tr>
            <th>Identifier/Product</th>
            <th>Column 1</th>
            <th>Column 2</th>
            <th>Total</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Item 1</td>
            <td>17</td>
            <td>24</td>
            <td></td>
        </tr>
        <tr>
            <td>Item 2</td>
            <td>13</td>
            <td>2</td>
            <td></td>
        </tr>
    </tbody>
</table>

<script>
    const table = document.querySelector('table');
    const rows = table.querySelectorAll('tbody tr');
    const rowTotalCell = document.getElementById('rowTotal');
    
    rows.forEach(row => {
        const cells = row.querySelectorAll('td');
        let total = 0;
        
        cells.forEach((cell, index) => {
            if (index === 1 || index === 2) {
                total += parseFloat(cell.textContent);
            }
        });
        
        const totalCell = row.querySelector('td:last-child');
        totalCell.textContent = total.toFixed(2);
    });
</script>

</body>
</html>

 

Link to comment
Share on other sites

1 hour ago, My Sharona said:

I am trying to add it to a Page Builder: Text Block

OK - so what actually happens when you try?

That code works as expected as HTML page - so how have you taken that code and implemented it? What happens when you try? What is your own code?

Or are you looking for someone to convert that for you?

And isn't a "Text block" just that - a block for adding simple text to a page? Does it allow HTML/Javascript?

Edited by Nathan Explosion
Link to comment
Share on other sites

12 hours ago, Nathan Explosion said:

OK - so what actually happens when you try?

That code works as expected as HTML page - so how have you taken that code and implemented it? What happens when you try?

This is what renders on a Page in a Page builder: Text Block or utilizing the Source of a WYSIWYG Editor block:
Could contain: Text, Page
As you can see, it doesn't total.
 

12 hours ago, Nathan Explosion said:

Or are you looking for someone to convert that for you?

I was just hoping to get some help here as to why it doesn't work in the IPS environment. If I can't get it figured out, I guess I will just have to do it manually. I figure it has to be something simple.

 

12 hours ago, Nathan Explosion said:

And isn't a "Text block" just that - a block for adding simple text to a page? Does it allow HTML/Javascript?

Yes, it allows html/java. I use it for a number of blocks just fine.

Thanks.

Link to comment
Share on other sites

21 minutes ago, Nathan Explosion said:

Show your actual used code - what you are actively using in the block.

That is as far as I have got with it. If I cant get a row to total, there is no use populating it.

I am simply using numbers in a row and need them to add up.

Is there a way to make that code, add the numbers within columns, add up at the end of the row in the IPS environment?

Link to comment
Share on other sites

  • Solution

Page Builder: Text

Literally a copy/paste of the relevant code, with a slight modification to how the table is selected in the Javascript code. Free free to add your style to it.

 

<table id="thisismytable">
	<thead>
	<tr>
		<th>Identifier/Product</th>
		<th>Column 1</th>
		<th>Column 2</th>
		<th>Total</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<td>Item 1</td>
		<td>17</td>
		<td>24</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 2</td>
		<td>13</td>
		<td>2</td>
		<td></td>
	</tr>
	</tbody>
</table>
<script>
 const table = document.querySelector('#thisismytable');
    const rows = table.querySelectorAll('tbody tr');
    const rowTotalCell = document.getElementById('rowTotal');
    rows.forEach(row => {
        const cells = row.querySelectorAll('td');
        let total = 0;
        cells.forEach((cell, index) => {
            if (index === 1 || index === 2) {
                total += parseFloat(cell.textContent);
            }
        });
        const totalCell = row.querySelector('td:last-child');
        totalCell.textContent = total.toFixed(2);
    });
</script>

 

 

 

 

Edited by Nathan Explosion
Link to comment
Share on other sites

22 hours ago, Nathan Explosion said:

Page Builder: Text

Literally a copy/paste of the relevant code, with a slight modification to how the table is selected in the Javascript code. Free free to add your style to it.

 

<table id="thisismytable">
	<thead>
	<tr>
		<th>Identifier/Product</th>
		<th>Column 1</th>
		<th>Column 2</th>
		<th>Total</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<td>Item 1</td>
		<td>17</td>
		<td>24</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 2</td>
		<td>13</td>
		<td>2</td>
		<td></td>
	</tr>
	</tbody>
</table>
<script>
 const table = document.querySelector('#thisismytable');
    const rows = table.querySelectorAll('tbody tr');
    const rowTotalCell = document.getElementById('rowTotal');
    rows.forEach(row => {
        const cells = row.querySelectorAll('td');
        let total = 0;
        cells.forEach((cell, index) => {
            if (index === 1 || index === 2) {
                total += parseFloat(cell.textContent);
            }
        });
        const totalCell = row.querySelector('td:last-child');
        totalCell.textContent = total.toFixed(2);
    });
</script>

 

 

 

 

Thank you for this, I will give it a try over the weekend.

 

22 hours ago, opentype said:

A Pages block doesn’t take a full HTML page and it should have a better identifier than just “table”. Nathan’s code fixes those things. 

But how is the data fed into the table anyway? If it takes hard-coded data in the block, I wouldn’t want to use JavaScript on the visitor’s site to make those calculations. 

The data is entered manually. I was hoping to avoid the step of having to add the columns within the row, for each row.

 

 

Link to comment
Share on other sites

On 8/17/2023 at 6:25 AM, Nathan Explosion said:

Page Builder: Text

Literally a copy/paste of the relevant code, with a slight modification to how the table is selected in the Javascript code. Free free to add your style to it.

 

<table id="thisismytable">
	<thead>
	<tr>
		<th>Identifier/Product</th>
		<th>Column 1</th>
		<th>Column 2</th>
		<th>Total</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<td>Item 1</td>
		<td>17</td>
		<td>24</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 2</td>
		<td>13</td>
		<td>2</td>
		<td></td>
	</tr>
	</tbody>
</table>
<script>
 const table = document.querySelector('#thisismytable');
    const rows = table.querySelectorAll('tbody tr');
    const rowTotalCell = document.getElementById('rowTotal');
    rows.forEach(row => {
        const cells = row.querySelectorAll('td');
        let total = 0;
        cells.forEach((cell, index) => {
            if (index === 1 || index === 2) {
                total += parseFloat(cell.textContent);
            }
        });
        const totalCell = row.querySelector('td:last-child');
        totalCell.textContent = total.toFixed(2);
    });
</script>

 

 

 

 

This seems to work just fine. Thank you again Nathan, greatly appreciated.

If I may ask one more... I have played around with it a bit and figured out how to add more columns and data to the rows and have them add to the total. However, the totals give decimal places ("x.000..." (one for each column it seems)). Is there a way to remove the decimals?

Thanks!

Link to comment
Share on other sites

1 hour ago, Nathan Explosion said:

Show your modified code...

<table id="natestable">
	<thead>
	<tr>
		<th>Item</th>
		<th>Variable 1</th>
		<th>Variable 2</th>
		<th>Variable 3</th>
		<th>Variable 4</th>
		<th>Variable 5</th>
		<th>Variable 6</th>
		<th>Total</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<td>Item 1</td>
		<td>1</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 2</td>
		<td>0</td>
		<td>1</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 3</td>
		<td>0</td>
		<td>0</td>
		<td>1</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 4</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>1</td>
		<td>0</td>
		<td>0</td>
		<td></td>
	</tr>
	<tr>
		<td>Item 5</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>0</td>
		<td>1</td>
		<td>0</td>
		<td></td>
	</tr>
	</tbody>
</table>
<script>
 const table = document.querySelector('#natestable');
    const rows = table.querySelectorAll('tbody tr');
    const rowTotalCell = document.getElementById('rowTotal');
    rows.forEach(row => {
        const cells = row.querySelectorAll('td');
        let total = 0;
        cells.forEach((cell, index) => {
            if (index === 1 || index === 2 || index === 3 || index === 4 || index === 5 || index === 6) {
                total += parseFloat(cell.textContent);
            }
        });
        const totalCell = row.querySelector('td:last-child');
        totalCell.textContent = total.toFixed(0);
    });
</script>

Hope I did all that right.

 

1 hour ago, Nathan Explosion said:

And maybe look at what the JS function toFixed() does, as that may give you a clue at to what that 2 is for.

Yep, I believe that fixed it. thumb.gif.9552e86ffa1560b9f31c1d7f51bee557.gif

Edited by My Sharona
Link to comment
Share on other sites

23 hours ago, Nathan Explosion said:

That makes me twitch.

Change it to:

if (index > 0 && index < cells.length - 1) {
	total += parseFloat(cell.textContent);
}

 

Thank you, sir. Works like a charm.

Just a FYI here... In fooling around, I have noticed that if you add another block (say you wanted two of these on the same page) the second block on the page won't do the adding of the columns in rows. I tried leaving the 'table id' the same (in both places, within a block) and also changing the 'table id' of the second block, but neither works.

Link to comment
Share on other sites

X tables in one block (X = 1 or more...just edit the 'tableIds' array to put the IDs in place)

<table id="table1">
    <thead>
    <tr>
        <th>Item</th>
        <th>Variable 1</th>
        <th>Variable 2</th>
        <th>Variable 3</th>
        <th>Variable 4</th>
        <th>Variable 5</th>
        <th>Variable 6</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>Item 1</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 2</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 3</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 4</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 5</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td></td>
    </tr>
    </tbody>
</table>
<table id="table2">
    <thead>
    <tr>
        <th>Item</th>
        <th>Variable 1</th>
        <th>Variable 2</th>
        <th>Variable 3</th>
        <th>Variable 4</th>
        <th>Variable 5</th>
        <th>Variable 6</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>Item 1</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 2</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 3</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 4</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 5</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td></td>
    </tr>
    </tbody>
</table>
<script>
const tableIds = ['#table1','#table2'];
    let table, rows, rowTotalCell, cells, total, totalCell;
    tableIds.forEach(myTable => {
        table = document.querySelector(myTable);
        if(table !== null){
            rows = table.querySelectorAll('tbody tr');
            rowTotalCell = document.getElementById('rowTotal');
            rows.forEach(row => {
                cells = row.querySelectorAll('td');
                total = 0;
                cells.forEach((cell, index) => {
                    if (index > 0 && index < cells.length - 1) {
                        total += parseFloat(cell.textContent);
                    }
                });
                totalCell = row.querySelector('td:last-child');
                totalCell.textContent = total.toFixed(0);
            });
        }
    });
</script>

 

Edited by Nathan Explosion
Link to comment
Share on other sites

23 hours ago, Nathan Explosion said:

X tables in one block (X = 1 or more...just edit the 'tableIds' array to put the IDs in place)

<table id="table1">
    <thead>
    <tr>
        <th>Item</th>
        <th>Variable 1</th>
        <th>Variable 2</th>
        <th>Variable 3</th>
        <th>Variable 4</th>
        <th>Variable 5</th>
        <th>Variable 6</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>Item 1</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 2</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 3</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 4</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 5</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td></td>
    </tr>
    </tbody>
</table>
<table id="table2">
    <thead>
    <tr>
        <th>Item</th>
        <th>Variable 1</th>
        <th>Variable 2</th>
        <th>Variable 3</th>
        <th>Variable 4</th>
        <th>Variable 5</th>
        <th>Variable 6</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>Item 1</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 2</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 3</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 4</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td>0</td>
        <td></td>
    </tr>
    <tr>
        <td>Item 5</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>0</td>
        <td>1</td>
        <td>0</td>
        <td></td>
    </tr>
    </tbody>
</table>
<script>
const tableIds = ['#table1','#table2'];
    let table, rows, rowTotalCell, cells, total, totalCell;
    tableIds.forEach(myTable => {
        table = document.querySelector(myTable);
        if(table !== null){
            rows = table.querySelectorAll('tbody tr');
            rowTotalCell = document.getElementById('rowTotal');
            rows.forEach(row => {
                cells = row.querySelectorAll('td');
                total = 0;
                cells.forEach((cell, index) => {
                    if (index > 0 && index < cells.length - 1) {
                        total += parseFloat(cell.textContent);
                    }
                });
                totalCell = row.querySelector('td:last-child');
                totalCell.textContent = total.toFixed(0);
            });
        }
    });
</script>

 

Fantastic. Good to know should that need arise. thumb.gif.38f646b29f7ddb5f04a72d35d17ce249.gif

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...