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

My Sharona · August 15, 2023

Having some trouble with a table, in the IPS environment, of having numerical values of columns within a row add up automatically and display that sum total in a new column at the end of that row.

Help is appreciated, thanks!

Edited August 15, 2023 by My Sharona

Miss_B · August 15, 2023

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

My Sharona · August 16, 2023

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 August 16, 2023 by My Sharona

Nathan Explosion · August 16, 2023

34 minutes ago, My Sharona said:

I have a table

A SQL table?

A Pages database table?

A form helper table?

What is the code?

My Sharona · August 16, 2023

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>

Nathan Explosion · August 16, 2023

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 August 16, 2023 by Nathan Explosion

My Sharona · August 17, 2023

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:

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.

Nathan Explosion · August 17, 2023

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

My Sharona · August 17, 2023

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?

Nathan Explosion · August 17, 2023

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 August 17, 2023 by Nathan Explosion

opentype · August 17, 2023

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.

Nathan Explosion · August 17, 2023

Interesting - works for a short while, then complains about the table selector.

Few mins...

and code updated - changed order of JS/HTML to HTML/JS

Edited August 17, 2023 by Nathan Explosion

My Sharona · August 18, 2023

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.

My Sharona · August 21, 2023

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!

Nathan Explosion · August 21, 2023

Show your modified code...

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

On 8/17/2023 at 11:25 AM, Nathan Explosion said:
totalCell.textContent = total.toFixed(2);

My Sharona · August 21, 2023

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.

Edited August 21, 2023 by My Sharona

Nathan Explosion · August 21, 2023

33 minutes ago, My Sharona said:

if (index === 1 || index === 2 || index === 3 || index === 4 || index === 5 || index === 6) {
                total += parseFloat(cell.textContent);
            }

That makes me twitch.

Change it to:

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

My Sharona · August 22, 2023

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.

Nathan Explosion · August 22, 2023

1 minute ago, My Sharona said:

I tried leaving the 'table id' the same (in both places, within a block)

Well, that's a definite no-no.

2 minutes ago, My Sharona said:

Just a FYI here

Do you need assistance with trying to figure that one out, or is literally just an 'FYI'?

Nathan Explosion · August 22, 2023

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 August 22, 2023 by Nathan Explosion

My Sharona · August 23, 2023

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.

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Recently Browsing 0 members