Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 537

Error when I try to do a simple calculation to get accurate total

I added a column to one of my coldfusion pages to show the amount of a deduction that occurs in our system when a PO get's paid. I want to do a simple subtraction based on the value that this new column(WrapAmount) holds. I have a total value that I calculate by using the method below:

``````<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 />
``````

I've tried simply changing <cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
to
<cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt - AmountToPayOnNextCheck.WrapAmount />

but the coldfusion debugger tells me "" cannot be converted to a number when I try to view the report. Any assistance is greatly appreciated!
0
J C
• 4
• 4
1 Solution

Commented:
It just means one of the variables you're trying to add/subtract isn't a valid number. It's an empty string "".  So CF can't do math on it.

Use val() to ensure the variables are numbers. Val automatically converts non-numeric values (like an empty string) to 0.

<cfset VARIABLES.total = val(variables.total) +
val(AmountToPayOnNextCheck.TotalAmt)
- val(AmountToPayOnNextCheck.WrapAmount) />
0

Author Commented:
Thanks a lot, that did it.
0

Author Commented:
I am now trying to add a total of the WrapAmount at the footer of this page. I tried using

#val(AmountToPayOnNextCheck.WrapAmount)#

It returns a 0, not sure if you are willing to chime in on this after the fact, I can post another question if not.
0

Commented:
If it returns 0 that means the value is not numeric.  What were you expecting it to return instead?
0

Commented:
> I am now trying to add a total of the WrapAmount

Sounds like you need to calculate the total inside the loop, like you're doing with #total#.  Something like this would calc the total for each group. If you need the overall total for the entire query, just initialize the #totalWrapAmount# variable *before* the output loop.

ie
<cfoutput group="PONo">
....
<!--- calculate total for CURRENT group --->
<cfset totalWrapAmount = 0>
<cfoutput>
....
<cfset totalWrapAmount  = totalWrapAmount  + val(AmountToPayOnNextCheck.WrapAmount)>
</cfoutput>
....
</cfoutput>
0

Author Commented:
It's really strange to me. The code below works. It gives me a total that subtracts the WrapAmount which was my original issue and you totally helped me address it.

<cfset VARIABLES.companyTotal = VARIABLES.companyTotal + VARIABLES.total - val(AmountToPayOnNextCheck.WrapAmount) />

When I try to show the Wrap Total by itself using the code you provided it returns a value of 2.64 which is incorrect for this particular data set. The correct value is reflected in the above total but I can't get just a sum of the WrapAmount to display by itself.
0

Commented:
Incorrect how? What do you expect the total to be instead and why?

I suspect it's a matter of where you're incrementing the total, but it's impossible to say for sure without seeing a dump of the actual query values. So we can figure out how you're getting 2.64.
0

Author Commented: