How to output with a control variable inside cfquery name?

[acoder]

Look for -1 somewhere in the code probably after the code you've posted.

Note that lines 6-10 could be combined to avoid repetition, e.g.

<cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
    <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
</cfif>

likewise for the d&h part.

[bonneylake]

Hey Acoder,

I looked through the whole code, but no where does it have -1, unless i am missing it. Here is what i have in full.

<table width="85%" align="center">
<thead>
<tr>
<th>HC Part Number</th>
<th>Manu Part Number </th>
<th>MPL manufacturer</th>
<th>MPL LastBuyQuote</th>
<th>Techdata Price</th>
<th>Synnex Price</th>
<th>D&H Price</th>
<th>%price diff</th>
<th>MPL Description</th>
<th>Techdata Description</th>
</tr>
</thead>



<cfoutput>

<cfset alt=0>
<cfset classStr="">

<cfloop query="matchmanu">
<cfif alt eq 0>
	<cfset classStr="">
	<cfset alt=1>
<cfelse>
	<cfset classStr="alt">
	<cfset alt=0>
</cfif>

<tr>

<td class="#classStr#">
#matchmanu['hc part number'][matchmanu.currentrow]#
</td>
<td class="#classStr#">
#matchmanu.mpn#
</td>
<td class="#classStr#">
#matchmanu.Mfg#
</td>
<td class="#classStr#">
#decimalFormat(matchmanu.lastbuyquote)#
</td>
<cfquery name="techdata" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery>
<td class="#classStr#">
#dollarformat(techdata.COST)#
</td>         
<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where Unit_Cost__wo_PromoRebate is not null and (SYNNEX_SKU = '#val(synnxsku)#')
</cfquery>
<td class="#classStr#">
#dollarformat(synnx.Unit_Cost__wo_PromoRebate)#
</td>
<cfquery name="dh" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#dandhsku#'
</cfquery>
<td class="#classStr#">
#dollarformat(dh.unit_cost)#
</td>

 <cfset lowest = 0>
 <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
     <cfset lowest = techdata.COST>
 </cfif>
   <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
      <cfif lowest eq 0 or lowest gt synnx.Unit_Cost__wo_PromoRebate>
      <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
      </cfif>
   </cfif>
 <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
       <cfif lowest eq 0 or lowest gt dh.unit_cost>
       <cfset lowest = dh.unit_cost> 
       </cfif>
   </cfif>
   

<td class="#classStr#">
<cfset cost = #matchmanu.lastbuyquote#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 or pricediff lt -0.03>
<cfset pricediff = round(pricediff * 100)/100>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

</td>
<td class="#classStr#">
#matchmanu.DESCRIPTION#
</td>
<td class="#classStr#">
<cfquery name="techdatad" datasource="Distributor">
Select DESCR
from dbo.PRODUCTS
where PART_NUM = '#techdatasku#' 
</cfquery>
#techdatad.DESCR#
</td>
</tr>
</cfloop>
</cfoutput>
</table>

Thank you,
Rach

[acoder]

It'll be line 91:

<cfset pricediff = ((lowest - cost)/cost)>

which is causing the problem. If, for example, cost is 37.49 then pricediff would end up being 0 - 37.49/37.49 which equals -37.49/37.49 = -1!

To avoid that, add a condition on line 90:

<cfif lowest neq 0 and IsNumeric(cost) and val(cost) neq 0.0>

[bonneylake]

Hey Acoder,

That worked perfectly! I am glade i asked you because some of the values actually have -1 and have a lowest price to choose from so its a good thing we changed it .Other wise they would get -1 for the ones with no lowest cost and then some that have a lowest price with -1.But i cant think of any other questions to ask so i guess this topic is closed. But Thank You again for all your help!!!!

Thank you,Thank you!!! :)
Rach

[acoder]

Glad to help! Until next time... ;)