How to output with a control variable inside cfquery name?

[acoder]

It should be on one line. Check the values of cost and lowest to see what they are (display them for testing purposes).

[bonneylake]

Hey Acoder,

Confused on what you mean Check the values of cost and lowest? just output them like #acompare.cost# output it an then change it from that to #lowest# and output it? bit confused on how to check.

so everyone on one line should be something like this?

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#cost#
<cfelse>
0
</cfif>

Thank you,
Rach

[acoder]

Yes, just output them to test what their values are, so you can see what values you're working with and if they're correct or not.

[bonneylake]

Hey Acoder,

According to what i am looking at, there both outputting numbers. Its getting the correct value for the lowest company by doing #lowest# and when i do the cost, the cost is matching up correctly and its a number as well by doing #acompare.Cost# . But i know cost is a float and the company price is numeric, i don't know if that makes a difference or not as to why the pricediff wont work.

i know the problem has to be with cost because when i did the following, i got the error Division by zero is not allowed. I know that cost is a float value and lowest is numeric. but not sure how i would convert float to numeric

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - '#val(cost)#')/'#val(cost)#')>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
0
</cfif>

Thank you,
Rach

[acoder]

Don't put the val() in quotes. Is cost ever 0? If it is, it would cause zero division errors?

If it's 0, a comparison doesn't make sense, so a blank or "N/A" would have to be output.

[bonneylake]

Hey Acoder,


Actually doing this seemed to help. I am not getting errors anymore an now some of the values are 0 and some are not.But i am not sure if this is the correct solution or not.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
 0
</cfif>
</cfif>

i changed the 0 that appears underneath cfelseif statement to n/a an that didn't seem to make a difference at all to the results.

but here is what i had before when i was just outputting #acompare.cost#

for line 194 i had 16 49
for line 292 i had 432.8000

an then when i did it with the isnumeric (above) this is what i get

for line 194 i get 0
for line 292 i get 432.8000

i also did a test with the calculator to make sure its right i did for the first one i did
(($9.98 - $16.49)/$16.49) and got the result -0.394 (an the numbers continue)

an then for the second one i did

(($453.97 - $432.80)/$432.80) and i got the result 0.048 (an the numbers continue)

Thank you,
Rach

[acoder]

You need to combine lines 4 and 6:

<cfif pricediff gt 0.03 and pricediff lt -0.03>

Do you want to output the cost or the price difference?

What are lines 194 and 292 that you mentioned in the previous post? Are these in the table? Also, does 16.49 have a space, i.e. is it "16 49"? If it is, the IsNumeric() will be false.

[bonneylake]

Hey Acoder,

Definately want to output the price different so instead of using #cost# would i use #pricediff#?. Line 194 and 292 appear in the report.used them because there the first 2 that have something in the price diff column an my bad its 16.49 (typed it wrong).

So would this be correct?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[acoder]

Yes, but as well as a check to make sure cost is numeric, you may want to add a check that it's not 0.

[bonneylake]

Hey Acoder,

So would this be correct? not sure if i got the cost neq in the right spot or not. not sure if it should be before or after isnumeric

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfif cost neq 0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>
</cfif>

Thank you,
Rach

[acoder]

Yes, or you could combine the two:

<cfif IsNumeric(cost) and cost neq 0.0>

[bonneylake]

Hey Acoder,

I tried it the way i had it and tried it your way an now all the values for the price diff say n/a. I think its cause when you do the price diff at the beginning of all of them they had 0.0 in front? Any suggestions?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and cost neq 0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[acoder]

That can only mean it's failing the IsNumeric or neq 0 test. Is cost a float or is it stored as a string?

[bonneylake]

Hey Acoder,

I know the neq 0 part is failing,because the isnumeric was working before. But cost is a float field in the table, not sure if its a string or not. Just know its float.

But i took out the cost neq 0 to make sure it was causing it and turns out its me using #pricediff# to output it is causing the problem. Its saying n/a for all of them based on i changed #cost# to pricediff#.Any suggestions?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and cost neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[acoder]

Add val() around cost. If it's displaying N/A, that means the price difference is less than +-3%.