:: Forum >>

Calculated columns (cell formula)

Using the below link I am using this method in my grid which adds up a Nett & Disc row into a Total. This works great going accros the grid and I now have lots of flexiablty with calcuations across the grid.

http://www.activewidgets.com/javascript.forum.20911.6/calculated-columns-cell-formula.html

I now need to be able to show column totals.

I have really tried and have managed to get the column total into function and then display the result of the function in a varailable which I can then display in a totals block.

However this total dosen't refresh when the grid changes.

Please help!!

function sum01(col, row){
var cell1 = this.getCellValue(3, 0);
var cell2 = this.getCellValue(3, 1);
    var cell3 = this.getCellValue(3, 2);
    var cell4 = this.getCellValue(3, 3);
    var cell5 = this.getCellValue(3, 4);
    var cell6 = this.getCellValue(3, 5);
    
return cell1 + cell2 + cell3 + cell4 + cell5 + cell6;
}

obj.setCellData(sum01,5, 6);

var nett = obj.getCellValue(5, 6);
Jez
Saturday, April 4, 2009
Are you using row footers for this? http://www.activewidgets.com/aw.ui.grid/footer-template.html
Anthony
Saturday, April 4, 2009
No I am using a separate HTML fieldset to show the totals..

Here's Most of the page/code.. Hopefully it's all there and make sense?

Ideas here would be good? Maybe if I could use calculated columns method, but I can not work out how to use that going down the grid,
which is why I have created a function to calculate the total. But I need this total to refresh when the grid changes.



<Html>
<Body>
<fieldset id="SLI_Inv_Details">
<legend>Invoicing Details</legend>
<script type="text/javascript">
var myHeaders = ['Line Description', 'Nett', 'Disc', 'Total', 'Centre'];
var myCells = [
["Line One Sales" ,300.00,0.00],
["Line Two Sales",500.00,0.00],
["Line Three Sales",10750.00,0.00],
["Line Four Sales",250.00,0.00],
["Line Five Sales",350.00,0.00],
]

var str = new AW.Formats.String;
var num = new AW.Formats.Number;
var disc = new AW.Formats.Number;
num.setTextFormat('####.##');
disc.setTextFormat('#.##');

var obj = new AW.UI.Grid;
obj.setHeaderText(myHeaders);
obj.setCellData(myCells);
obj.setCellFormat([str, num, disc, num, str, num]);
    

//set number of rows/columns
obj.setColumnCount(5);
obj.setRowCount(5);
obj.setSize(760, 150);
obj.setColumnWidth(250, 0);
obj.setColumnWidth(100, 1);
obj.setColumnWidth(100, 2);
obj.setColumnWidth(100, 3);
obj.setColumnWidth(200, 4);

obj.setCellEditable(true);
obj.setCellEditable(false, 4);
obj.setCellFormula('(((column(1)/100)*column(2))-column(1))*-1', 3); // calc total in column-3 after disc

function sum01(col, row){
var cell1 = this.getCellValue(3, 0);
var cell2 = this.getCellValue(3, 1);
var cell3 = this.getCellValue(3, 2);
var cell4 = this.getCellValue(3, 3);
var cell5 = this.getCellValue(3, 4);
var cell6 = this.getCellValue(3, 5);
    
return cell1 + cell2 + cell3 + cell4 + cell5 + cell6;
}

obj.setCellData(sum01,5, 6);
var net = obj.getCellValue(5, 6);

document.write(obj);
</script>
                        

<fieldset id="SLI_T_Details">
<legend>Totals</legend>
<table>
<tr><td>Nett:</td><td>
<script type="text/javascript">
var num = new AW.Formats.Number;
num.setTextFormat('####.##');
var nett = new AW.UI.Input;
nett.setControlFormat(num);
nett.setControlText(nett);
nett.refresh();
document.write(nett);
</script></tr>
<tr><td>Disc:</td><td><input maxlength="10" name='Disc' type='text' size='10' value='0.00'></tr>
<tr><td>VAT:</td><td><input maxlength="10" name='VAT' type='text' size='10' value='0.00'></tr>
<tr><td>Total:</td><td><input maxlength="10" name='Total' type='text' size='10' value='0.00' align="right"></tr>
</table>
</fieldset>
</body>
</HTML>


Jez
Sunday, April 5, 2009
<Html>
<Body>
<fieldset id="SLI_Inv_Details">
<legend>Invoicing Details</legend>
<script type="text/javascript">
var myHeaders = ['Line Description', 'Nett', 'Disc', 'Total', 'Centre'];
var myCells = [
["Line One Sales" ,300.00,0.00,"","",""],
["Line Two Sales",500.00,0.00,"","",""],
["Line Three Sales",10750.00,0.00,"","",""],
["Line Four Sales",250.00,0.00,"","",""],
["Line Five Sales",350.00,0.00,"","",""]
]

var str = new AW.Formats.String;
var num = new AW.Formats.Number;
var disc = new AW.Formats.Number;
num.setTextFormat('####.##');
disc.setTextFormat('#.##');

var obj = new AW.UI.Grid;
obj.setHeaderText(myHeaders);
obj.setCellData(myCells);
obj.setCellFormat([str, num, disc, num, str]);


//set number of rows/columns
obj.setColumnCount(5);
obj.setRowCount(6);
obj.setSize(760, 150);
obj.setColumnWidth(250, 0);
obj.setColumnWidth(100, 1);
obj.setColumnWidth(100, 2);
obj.setColumnWidth(100, 3);
obj.setColumnWidth(200, 4);

obj.setCellEditable(true);
obj.setCellEditable(false, 4);
obj.setCellFormula('(((column(1)/100)*column(2))-column(1))*-1', 3); // calc total in column-3 after disc

function sum01(col, row){
var cell1 = this.getCellValue(3, 0);
var cell2 = this.getCellValue(3, 1);
var cell3 = this.getCellValue(3, 2);
var cell4 = this.getCellValue(3, 3);
var cell5 = this.getCellValue(3, 4);
// var cell6 = this.getCellValue(3, 5);
// return cell1 + cell2 + cell3 + cell4 + cell5 + cell6;

return cell1 + cell2 + cell3 + cell4 + cell5 ;
}

obj.setCellData(sum01,3, 5);
var net = obj.getCellValue(3, 5);

obj.onCellValueChanged = function(value, col, row){
if(col==1 || col==2){
obj.setCellData(sum01,3, 5);
var net2 = obj.getCellValue(3, 5);
nett.setControlText(net2);
nett.refresh();
}
}

document.write(obj);
</script>


<fieldset id="SLI_T_Details">
<legend>Totals</legend>
<table>
<tr><td>Nett:</td><td>
<script type="text/javascript">
var num = new AW.Formats.Number;
num.setTextFormat('####.##');
var nett = new AW.UI.Input;
nett.setControlFormat(num);
nett.setControlText(net);
nett.refresh();
document.write(nett);
</script></tr>
<tr><td>Disc:</td><td><input maxlength="10" name='Disc' type='text' size='10' value='0.00'></tr>
<tr><td>VAT:</td><td><input maxlength="10" name='VAT' type='text' size='10' value='0.00'></tr>
<tr><td>Total:</td><td><input maxlength="10" name='Total' type='text' size='10' value='0.00' align="right"></tr>
</table>
</fieldset>
</body>
</html>
Sunday, April 5, 2009
I’m assuming that’s Anthony's reply? I away until Wed, So I’ll try your example then, Thanks in advance!!

Jez
Tuesday, April 7, 2009
No, that's not mine. There's someone here who posts without identifying him or herself.
Anthony
Tuesday, April 7, 2009
Ok.. Well I try the example anyway, it looks good.

Jez
Jez
Tuesday, April 7, 2009

This topic is archived.


Back to support forum

Forum search