Adding the values of a cfgrid column
25 comments Posted by: LauraWe were asked about solving the problem of adding the values of a column in a flash cfgrid, so we made an example in response.
The cfgrid shows the items of a shopping cart. The quantity column is editable, so we can change the number of items we want to buy. Then, we click "Calculate Order" and the total shows at the bottom. Although we are triggering the addition at the button's onclick event, we could do the same at the cfgrid onchange. The relevant part is when we get the column value and add it to the total.
To get a specific column in row i:
cart.getItemAt(i)['price']; //where price is the name of the column and cart is the name of the grid
In addition, we are changing the value of the subtotal column with the price multiplied by the quantity in row i:
cart.editField(i, 'amount', subTotal); //where amount is the name of the column we want to edit
The complete code:
<cfsavecontent variable="calculateTotal">
var totalAmount = 0;
var item;
var subTotal = 0;
for(var i = 0; i < cart.length; i++) {
item = cart.getItemAt(i);
//get subtotal
subTotal = number(item['price']) * item['quantity'];
//tally the amount up
totalAmount += subTotal;
//change the subtotal column for this row
cart.editField(i, 'amount', subTotal);
}
//show the total
total.text = totalAmount;
</cfsavecontent>
<cfform name="myForm" format="flash">
<cfgrid name= "cart" query="products" rowheaders="false" selectmode="edit">
<cfgridcolumn name="price" display="No">
<cfgridcolumn name="name" header="Product" select="false">
<cfgridcolumn name="quantity" header="Quantity" type="numeric" width="60" dataalign="right" >
<cfgridcolumn name="amount" header="Subtotal" type="currency" dataalign="right" select="false" width="60">
</cfgrid>
<cfinput type="Button" name="calculateBtn" value="Calculate Order" onclick="#calculateTotal#">
<cfinput type="Text" name="total" disabled="true" label="Total $" size="5">
</cfform>
Note that if we have many rows this may not be the best method as it may cause the "A script in this movie is causing Flash Player to run slowly" alert. We can avoid it by using a different technique. More on that in my next post.
Live example
Download the source
25 Comments so far
Write yoursAs usuall you guys continue to impress with pushing the limits of what is possible with cfforms.
This post partly answers a question that were asked in a IRC room the other day, but the question there was not only if you can change the value in a cfgrid, but if you could have dropdown list of values to choose from.
I answered that I've never seen it done, but since your example here is part of the way I thought I'd ask what you think. Do you think it's possible?
I'm really impressed with your excellent skills at using flash form,cfgrid. And with the "
onload" example posted before, i have got some ideas of dynamically setting color of a specific row of cfgrid. However, it's not working by combing "onload" and "setting property of cfgrid", do you have any suggestions?
By the way, how do you know the function of setting some properties of a certain flash form control, such as "setProperty()"?
Thanks in advance :)
What you are trying to achieve is difficult because the onload is called before the data in the datagrid is received (the data and the form do not load at the same time). When you change the property of a row, you are changing a empty datagrid. Later, when the data arrives, it overwrites all the changes ( I think that is the problem )
"cart.getItemAt(i)['price'];",
"<cfsavecontent variable="calculateTotal">", and
"onclick="#calculateTotal#"" were just the ideas
I was looking for.
Cheers!
my question; i have a grid that i would like to automatically have the total of, much the way that the RealEstate application, when a certain action happens it calls a some remoting, and then gives back a result... then the result is what populates my grid, in this instance, i'm having that grid populate with a series of items, much like an assembly set of items, say you buy a computer, and you serialize the monitor, the printer, you want to add the memory as a line item, and so on....
(i call it a batch or an assembly item...) so anyway, i run these query's in my cfc, to get the items i need, and then load the grid, but then would like the total to automatically show in the top right of the screen, i have this working with the calculate button, but am having trouble finding a place to execute #calculateTotal#, because since i add a batch of items, the datagrid is never actually touched, so i cant use the datagrid's onChange event... any ideas?
I have a project log grid that also tracks hours. I would like it to total the hours for the employee, but I have been unable to get it to work. Your assistance would be greatly appreciated as I am new to AS.
<cfsavecontent variable="calculateTotal" onclick="#calculateTotal#">
var totalAmount = 0;
var item;
var subTotal = 0;
for(var i = 0; i < empHrsGrid.length; i++) {
item = empHrsGrid.getItemAt(i);
subTotal = number(item['hrs']) * number(1);
totalAmount += subTotal;
}
total.text = totalAmount;
</cfsavecontent>
<cfform name="EditLog" action="" format="flash" method="post" height="500">
<cfgrid name="empHrsGrid"
query="getAllbyStaff"
width="900"
selectmode="edit"
insert="no"
delete="no"
vspace="100"
onchange="#calculateTotal#">
<cfgridcolumn name="projectnumber" type="string_nocase" header="Project Number" width="75" headeralign="center" headerbold="yes" select="yes" dataalign="center">
<cfgridcolumn name="hrs" type="numeric" header="Hours" width="50" headeralign="center" headerbold="yes" select="yes" dataalign="center" >
<cfgridcolumn name="newcontactdate"type="numeric" header="Log Date" width="85" headeralign="center" headerbold="yes" select="yes" dataalign="center">
<cfgridcolumn name="lognotes" header="Log Entry" headeralign="center" headerbold="yes" select="no" >
</cfgrid>
<cfinput type="submit" name="gridEntered" value="Submit" >
<cfinput type="Button" name="calculateBtn" value="Calculate Total Hours (not quite working yet...)" onclick="#calculateTotal#">
<cfinput type="Text" name="total" disabled="true" label="Total Hrs Posted" size="7">
</cfform>
I have been farting around with this for days... and I feel like an idiot. But I am sure that it is something kind of simplistic that I am missing. The total hours box shows 0 when it first loads but when the calculate is clicked, it shows NaN. I haven't gotten any of the onload, or onclick or onchange events to work either for calculatetotal.
Please help one of your admirers...
Thank you :)
I am not sure why I used number(), but it should be Number(). Before making the addition, I would check whether or not is NAN and if it is, then I would ignore it (probably a null which would correspond to a 0)
It would look something like this (change as needed):
var totalAmount = 0;
var item;
var subTotal = 0;
for(var i = 0; i < myGrid.length; i++) {
item = myGrid.getItemAt(i);
//get subtotal
subTotal = Number(item['price']);
if (isNaN(subTotal)) {
subTotal = 0;
}
//tally the amount up
totalAmount += subTotal;
}
//show the total
total.text = totalAmount;
John
<p>Returns a number to two decimal places.
<p>
<cfloop FROM = 1 TO = 20 INDEX = "counter">
<cfoutput>
$#DecimalFormat(counter)#
</cfoutput>
<br>
</cfloop>
I am using cfgrid to create a sub-form within a flash form. Fields from the main form will update table 1 (Orders) while the grid rows will be used to update table 2 (OrderDetails). Both on the same datasource. The entire form is to be submitted at once and AFTER the prices, etc have been calculated updated. How can I ensure that the
grid.rowstatus.action[i] is set to "I"?
so that the form handler inserts all the individual rows (via CFloop)?
Thanks a lot.
WM
(I did try cfgridupdate, but it did not work. If anyone has
it is possible then please help me
thanks again
You can get columnCount and columnNames.
Check the docs for all the available properties and functions of the datagrid. http://livedocs.macromedia.com/flex/15/asdocs_en/mx/controls/DataGrid.html
You can get that by using myGrid.length
im having some trouble with making the cfgridcolumn type show as currency. i know there are some bugs with flash form, however im sure it can be done.
in my senario i have data queried from an access db, the columns i require are set to curreny etc. however when i change the data type to curreny in cfgridcolumn i get a NaN error in the subTotal and Total calculations. im assuming this is because of the $ sign.
Do you have any way around this?? i can post my code if you like, its just a tad too big.
Thanks
Nik
My Love this example since it answers 60% of may questions regarding calculations. I am populating a grid with add on options for vehicles. I want the end user to click a select box inside of the grid and then calulate totals from each selected options cost and not include items that are not selected. Any idea's on how to do this effeciantly?
When I select a new position number from the drop down list, the form below needs to refresh with the all of the information from the table for the selected value. I can't get the onchange to work. (profile_grid.cfm is the current page) My onchange for the 'old' select statement I was using is onchange="this.form.vCase.value=this.form.vCaseFind[this.form.vCaseFind.selectedIndex].value;this.form.submit();" and it works perfectly.
<CFGRID NAME="vPositionFind" WIDTH="80%" HEIGHT="90"
QUERY="PositionNumberInfo" INSERT="No"
DELETE="No" SORT="No"
FONT="Tahoma" BOLD="No" ITALIC="No" textcolor="##2D0D00"
fontSize = "10" colHeaderFontSize = "10"
GRIDDATAALIGN="LEFT" GRIDLINES="Yes"
ROWHEADERS="No" ROWHEADERALIGN="LEFT" colheadertextcolor="##990000"
SELECTCOLOR="BLUE" SELECTMODE="single"
bgcolor="##FFFFEE"
onchange="getUrl('profile_grid.cfm?vPosition=' + vPositionfind.dataProvider[vPositionfind.selectedIndex]['PositionNumber'],'target');">
<CFGRIDCOLUMN NAME="PositionNumber" HEADER="Pos.No."
HEADERALIGN="LEFT" DATAALIGN="LEFT"
SELECT="yes" DISPLAY="Yes"
<CFGRIDCOLUMN NAME="EmpLName" HEADER="Employee Last"
HEADERALIGN="LEFT" DATAALIGN="LEFT"
SELECT="no" DISPLAY="Yes" >
<CFGRIDCOLUMN NAME="EmpFName" HEADER="Employee First"
HEADERALIGN="LEFT" DATAALIGN="LEFT"
SELECT="no" DISPLAY="Yes">
<CFGRIDCOLUMN NAME="DivisionCode" HEADER="DivCode"
HEADERALIGN="LEFT" DATAALIGN="LEFT"
SELECT="no" DISPLAY="Yes" >
Any help you can give me with this would be greatly appreciated! Thanks!
Hope this helps someone. I sure needed to do this in my application.
<!--- replace <cfscript> block with a query:
<cfquery name="products">
SELECT name, amount, quantity, price
FROM cart
.......etc
</cfquery>
--->
<cfscript>
products = queryNew("");
names = "SUN ROOF,Alloy Wheels,Leather,Power Steering,Manual Transmision";
price = "150,300,650,300,-325";
CHECK = "false,false,false,false,true";
queryaddcolumn(products,"name",listtoarray(names));
queryaddcolumn(products,"price",listtoarray(price));
queryaddcolumn(products,"CHECK",listtoarray(CHECK));
</cfscript>
<cfsavecontent variable="calculateTotal">
var totalAmount = 0;
var item;
var subTotal = 0;
for(var i = 0; i < cart.length; i++) {
item = cart.getItemAt(i);
//get subtotal
if(item.CHECK.toString() == "true")
{
subTotal = number(item['price']) + subTotal;
}
}
//show the total
total.text = subTotal;
</cfsavecontent>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">;
<html>
<head>
<title>Adding the values of a cfgrid column</title>
</head>
<body>
<cfform name="myForm" format="flash" width="320" height="250">
<cfformgroup type="vBox">
<cfformitem type="text">Select Items to update Value of Options</cfformitem>
<cfgrid name= "cart" query="products" height="150" rowheaders="false" selectmode="edit">
<cfgridcolumn name="name" header="Product" type="NUMERIC" width="150" dataalign="left" >
<cfgridcolumn name="price" header="price" type="numeric" dataalign="right" select="false" width="60">
<cfgridcolumn name="CHECK" header="CHECK" type="boolean" width="60">
</cfgrid>
<cfformgroup type="horizontal" style="horizontalAlign:left;">
<cfinput type="Button" name="calculateBtn" value="Calculate Order" onclick="#calculateTotal#" style="cornerRadius:0">
<cfinput type="Text" name="total" disabled="true" label="ADDED VALUE $" size="7" style="disabledColor:Black; borderStyle:none; fontWeight:bold;">
</cfformgroup>
</cfformgroup>
</cfform>
</body>
</html>
just wondering how would i go about saving the totalamount to datasource? so that when the page is loaded the total amount already shows. also so a can use the total value in other parts of the form.
May i know how to get a value from cfgrid to pass to another page...please help me...
no tech articles on this..