?
Solved

Problem displaying sum on my coldfusion page

Posted on 2012-08-27
8
Medium Priority
?
480 Views
Last Modified: 2012-08-28
I have a coldfusion page that displays to our vendor's each PO that they are going to be paid on for the current pay period. There is a deduction for some of them that happens and I recently added the column/deducted value to the report. I need to get a sum for the deducted amount of each PO and show a total for it. The method I am currently using shows me the amount of one deduction on the page and does not give me a proper sum. Any help is appreciated.
0
Comment
Question by:J C
  • 5
  • 2
8 Comments
 

Author Comment

by:J C
ID: 38339051
totalWrapAmount is the variable that I'd like to hold the sum.

<form name="form1" method="post" action="">
<table width="99%" cellpadding="3" cellspacing="0" border="0" bordercolor="#999999" bgcolor="#FFFFFF">
	<tr>
		<td align="center">
			<table width="100%" cellpadding="3" cellspacing="2" border="0" style="border:2px solid #999999">
				
				<tr>
				<td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Print Lien Release</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Vendor Code</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Purchase Order</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Total Amount</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Wrap Ded.</span></td>
                  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Project Code</span></td>
				  <td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Lot</span></td>
				  <td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Due Date</span></td>
				</tr>
				
				<cfset VARIABLES.totNoTax = 0>
				<cfset VARIABLES.temp_po_row_count = 0>
				
				<cfoutput query="AmountToPayOnNextCheck" startRow="#StartRow_AmountToPayOnNextCheck#" maxRows="#MaxRows_AmountToPayOnNextCheck#" group="VendCode" groupcasesensitive="true">
				  <cfset VARIABLES.companyTotal = 0 />
				  <cfoutput group="PONo">
				
				  <cfset VARIABLES.total = 0 />

				  <cfoutput>
				    <cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
					
					</cfoutput>
					 <cfset totalWrapAmount = 0 />                          
                			 <cfoutput>
                               <cfset totalWrapAmount  = totalWrapAmount + val(AmountToPayOnNextCheck.WrapAmount)>
                               </cfoutput>
				   	<cfset VARIABLES.companyTotal = VARIABLES.companyTotal + VARIABLES.total />
				  <tr>
                    <td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><input type="checkbox" name="po_#POID#" value="#POID#" /></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="left"><span class="con">#AmountToPayOnNextCheck.VendCode#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.RuleID# - #AmountToPayOnNextCheck.PONo#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="right"><span class="con">#dollarformat(VARIABLES.total)#</span></td>
                    <td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="wrap">#dollarformat (AmountToPayOnNextCheck.WrapAmount)#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.projCode#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2 eq 0)>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.Lot#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#dateformat(AmountToPayOnNextCheck.DueDate,"mm/dd/yy")#</span></td>
				    
				  </tr>
				<cfset VARIABLES.totNoTax = variables.totnotax +#total#>
				<cfset VARIABLES.temp_po_row_count = VARIABLES.temp_po_row_count + 1>
				</cfoutput>
				
				 <tr>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">Company Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(VARIABLES.companyTotal)#</span></td>
			            <td bgcolor="##cbdad6" align="right"><span class="hdr">Wrap Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(totalWrapAmount)#</span></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>

			</cfoutput>
				 <tr>
					<td colspan="7"></td>
				</tr>
			  </table>
		</td>
	</tr>
</table>
 <input type="hidden" name="conditionalType" value="" />
</form>

Open in new window

0
 

Author Comment

by:J C
ID: 38339121
Here is a SS. The Wrap Ded is the column I've added and the one I am trying to get a sum of. I'd like to show the total before deductions which I am already doing. I would then like to show the sum of all the wrap deductions for this particular vendor. I would then like to show the check total which will be  Companytotal - totalWrapAmount.

I was able to get the TotalWrapAmount to an amount that is close but for some reason it's not hitting it exactly. CF is telling me the total should be 41.76 when it is actually 41.13. I am not sure why it's tacking on the additional amount. If someone can tell me how it should be structured in order to accomplish what I'd like it would be much appreciated!

Current Code:

<form name="form1" method="post" action="">
<table width="99%" cellpadding="3" cellspacing="0" border="0" bordercolor="#999999" bgcolor="#FFFFFF">
	<tr>
		<td align="center">
			<table width="100%" cellpadding="3" cellspacing="2" border="0" style="border:2px solid #999999">
				
				<tr>
				<td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Print Lien Release</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Vendor Code</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Purchase Order</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Total Amount</span></td>
				  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Wrap Ded.</span></td>
                  <td bgcolor="#CCCCCC" align="center"><span class="hdr">Project Code</span></td>
				  <td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Lot</span></td>
				  <td width="10%" bgcolor="#CCCCCC" align="center"><span class="hdr">Due Date</span></td>
				</tr>
				
				<cfset VARIABLES.totNoTax = 0>
				<cfset VARIABLES.temp_po_row_count = 0>
				
				<cfoutput query="AmountToPayOnNextCheck" startRow="#StartRow_AmountToPayOnNextCheck#" maxRows="#MaxRows_AmountToPayOnNextCheck#" group="VendCode" groupcasesensitive="true">
				  <cfset VARIABLES.companyTotal = 0 />
				  <cfoutput group="PONo">
				
				  <cfset VARIABLES.total = 0 />

				  <cfoutput>
				    <cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
					
					</cfoutput>
					
				   	<cfset VARIABLES.companyTotal = VARIABLES.companyTotal + VARIABLES.total />
				  <tr>
                    <td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><input type="checkbox" name="po_#POID#" value="#POID#" /></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="left"><span class="con">#AmountToPayOnNextCheck.VendCode#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.RuleID# - #AmountToPayOnNextCheck.PONo#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="right"><span class="con">#dollarformat(VARIABLES.total)#</span></td>
                    <td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="wrap">#dollarformat (AmountToPayOnNextCheck.WrapAmount)#</span></td>
					<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.projCode#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2 eq 0)>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.Lot#</span></td>
					<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#dateformat(AmountToPayOnNextCheck.DueDate,"mm/dd/yy")#</span></td>
				    
				  </tr>
				<cfset VARIABLES.totNoTax = variables.totnotax +#total#>
				<cfset VARIABLES.temp_po_row_count = VARIABLES.temp_po_row_count + 1>
				</cfoutput>
				 <cfset totalWrapAmount = 0 />                          
                			 <cfoutput>
                               <cfset totalWrapAmount  = totalWrapAmount + val(AmountToPayOnNextCheck.WrapAmount)>
                               </cfoutput>

				 <tr>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">Company Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(VARIABLES.companyTotal)#</span></td>
			            <td bgcolor="##cbdad6" align="right"><span class="hdr">Wrap Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(totalWrapAmount)#</span></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>
				<tr>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr">Check Total: </span></td>
					<td bgcolor="##cbdad6" align="right"><span class="hdr"></span></td>
			            <td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
					<td bgcolor="##cbdad6"></td>
                    <td bgcolor="##cbdad6"></td>
                    
				</tr>


			</cfoutput>
				 <tr>
					<td colspan="7"></td>
				</tr>
			  </table>
		</td>
	</tr>
</table>
 <input type="hidden" name="conditionalType" value="" />
</form>

Open in new window

ss2.png
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 38339827
i am not sure of it as it is big code, just remove additional cfoutput tags and see how works
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:J C
ID: 38341507
I am not finding any extra cfoutput tags
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38343360
I think he meant that you're using 2 grouped outputs . "grouped" outputs are notriously tricky, especially when they're nested.  The results are very different depending on where you place code relative to the <cfoutput> tags:

            <cfoutput query="AmountToPayOnNextCheck" group="VendCode" ..>
                           code here runs only when the "VendCode" value changes
                       <cfoutput group="PONo">
                                  code here runs only when the "VendCode" and "PONo" values change
                          <cfoutput>
                                  code here should run for every row in the query
                                   </cfoutput>
                            </cfoutput>
           </cfoutput>

So like I mentioned on the other thread it's likely a problem with where you've placed the code relative to the output tags. Another gotcha is forgetting to order the query results properly. That can cause all kinds of strange results.  Is your query ordered by: "VendCode" first, then "PoNo" ?

If the query data is NOT confidential, can you post a dump of the query? Otherwise add debugging to display the individual wrap values as they total to help figure out why you're getting the wrong number.  

              ie
                   <cfoutput>
                               debugging totalWrapAmount ==> #totalWrapAmount# + #val(AmountToPayOnNextCheck.WrapAmount)#
                               <cfset totalWrapAmount  = totalWrapAmount + val(AmountToPayOnNextCheck.WrapAmount)>
                                  new amount = #totalWrapAmount#<br>
                       </cfoutput>
0
 

Author Comment

by:J C
ID: 38343431
Thanks, I get it now. I think I almost have it fixed now. The wrap amount I've corrected. Now I have to work on the final calculation. Once I have it working I will accept your solution. Thanks again.
0
 

Author Comment

by:J C
ID: 38343500
Thanks a lot for the explanation, you helped me to understand what was happening and I was able to get it fixed.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38343629
You're welcome :) Glad it helped.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question