1) You have your query INSIDE your loop. I would run the query, and then
loop through the query.
<cfquery name="getproducts" datasource="montage">
Select Price, Prod_desc, prodcode,
>From Prodcodes
Where prodcode in (#fieldlist#)
</cfquery>
(You'll have to do some string processing to turn form.fieldnames into an
appropriately formatted #fieldlist# variable.)
2) Some pseudo-code.
<run query>
<initialize total variables>
<Loop through query records>
<initialize record variables>
<evaluate "form." & getProducts.prodcode>
<write form output>
<update total variables>
<end records loop>
<output total variables>
3) If you have specific questions, post to list or contact me off-list.
-----Original Message-----
From: Kathy Bergman [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 08, 2000 2:30 PM
To: CF-Talk
Subject: Can I get a subtotal
I have been struggling with this for days so I am going to try to be very
clear in relating my problem.
On the first page I have select boxes with option choices ranging from 1-10
that are generated dynamically, from database fields. Currently the code
below generates 34 select boxes for 34 different products. This page works
correctly.
<cfoutput query="getprod" group="prodcode">
<tr><cfoutput>
<td>#prod_desc#</td>
<td> <select name="#prodcode#">
<option value="0">
<option value="1">1
<option value="2">2
<option value="3">3
<option value="4">4
etc...
</select>
</td>
</cfoutput>
</tr>
</cfoutput>
On the next page, I am able to loop through the field names and output how
many of which product were selected:
<CFLOOP LIST="#fieldnames#" INDEX="ListElement">
<cfquery name="getproducts" datasource="montage">
Select Price, Prod_desc, prodcode,
>From Prodcodes
Where prodcode = '#listelement#'
</cfquery>
<CFSET ThisName=Evaluate(Listelement)>
<CFSET ThisValue=Evaluate(ThisName)>
<CFSET #OrderQty# = #ThisValue#>
<CFSET #Subtotal# = (#Getproducts.Price# * #OrderQty#)>
<CFSET #itemsubtotal# = #thisvalue# * #Getproducts.Price#>
<CFIF '#thisvalue#' neq "0">
<CFOUTPUT query="getproducts" group="prod_desc">
<form action="Confirm.cfm" method="post">
<tr>
<td>#getproducts.prod_desc#</td>
<td>#NumberFormat(Getproducts.Price,'$9(9999.99)')#</td>
<td>#thisvalue#</td>
<td>#NumberFormat(itemsubtotal,'$9(9999.99)')#</td>
</tr>
</cfoutput>
</cfif>
</cfloop>
This is where I become perplexed. What I need to do next, is output
subtotal amounts showing quantity of all products picked and a sum of their
prices.
I have tried everything I can think of to no avail. Here, is the mumbo
jumbo I'm trying to do:
<CFSET #itemsordered# = ??>
<CFSET #orderSubTotal# = ??>
<CFSET #TotalQty# = ??>
<CFSET #OrderTotal# = #OrderSubTotal# + #SalesTax# + #Shipping#>
<CFOUTPUT>
<tr>
<td><b>Subtotal</b></td>
<td>nbsp;</td>
<td>#itemsordered#</td>
<td>#NumberFormat(OrderSubTotal,'$9(9999.99)')#</td>
</tr>
<tr>
<td><b>Sales Tax</b></td>
<td> </td>
<td> </td>
<td>#NumberFormat(SalesTax,'$9(9999.99)')# </td>
</tr>
<tr>
<td><b>Shipping</b> </font></td>
<td> </td>
<td> </td>
<td>#NumberFormat(Shipping,'$9(9999.99)')#</td>
</tr>
<tr>
<td><b>TOTAL</b></td>
<td > </td>
<td>#TotalQty#</font></td>
<td>#NumberFormat(OrderTotal,'$9(9999.99)')# </td>
</tr>
</cfoutput>
</table>
Any help would be GREATLY appreciated. I have considered another list, an
array and using sum in the query. Nothing gives me the right outputs.
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
