Skip to main content
WolfShade
WolfShade
Legend
September 17, 2025
Answered

QoQ issue relating to Excel file consumed

  • September 17, 2025
  • 1 reply
  • 840 views

All,

 

I have an app that's been working _mostly_ flawlessly for over five years.  It's a rates lookup table that uses Excel sheets as a read-only database.  There are two years of rates per line of business (ie, Air Passenger rates for FY2025 and FY2026).

 

A user selects a fiscal year from a dropdown, and five buttons appear for the user to select from.  A selection is made and a custom form for that FY and line of business appears.  Selections are made, and when all selections are set, AJaX sends a special code to a cffunction that consumes the related Excel file, then does a QoQ of that query to find a specific rate, based upon the code submitted, and returns the rate which is then displayed on the page for the user to see.

 

FY2026 rates need to be in place no later than 30 SEP.  But I have an issue that I can't explain.

 

There is one (so far, could be more) billing rate that exists in the related Excel file that the QoQ in the cffunction cannot find.  If I create a scratch.cfm file, put the code for consuming the Excel file at the top and cfdump the results, I get slightly more than 9900 records returned.  Then when I follow that with a QoQ using the straight string of the value and cfdump that, I get the one record I'm looking for.

 

I've cfmail'd the value being used in the cfqueryparam to myself, and sent it as HTML with the value in a span tag styled to have a red background.  There are no spaces, the format is correct.  But the process inside the cffunction keeps returning zero records, even though eyeballing the values of the query show that this billing code is in the results and matches what's being sent for filtering the data.

 

There are no hidden sheets in the Excel file, so the data is being pulled from the proper sheet.  Manually querying from my scratch file works.  But the function isn't seeing it.

 

I'm missing something, obviously.. just can't figure out what.


WolfShade

    Correct answer BKBK

    I replied via email, but it didn't come through.

    quote

    Suggestion: for the FY26, do cfdump in scratch.cfm of the "select *" query (without where-clause). Does it contain the record you're looking for?


    By @BKBK

     

    CORRECTION:  NO.  (I thought I saw FY25, not FY26).

     

    For FY25:   I had opened it in browser, copied the query parameter (WHERE clause value) from the SQL in the dump, used CTRL-F to find it in browser and it found it twice.. once from where I had copied it from the query string in the dump, and once in the full query dump earlier on the page.  The value is there.. it's just when the QoQ has the WHERE clause, it's not finding it in the FY26, but it _IS_ finding it in the FY25 file.

     

    UPDATE:  Now I know why my email response didn't go through.. even though the email says that replies via email are text only, it's coming from a mailbox that doesn't accept replies.

      Thanks, Adobe!

     


    This works:

    <cfset myQuery = queryNew("id,code","Integer,Varchar", [ [1,"02-15/10C >=40ft/1"], [2,"02-15/10C >=40ft/2"] ])>

<cfset variables.thisCombination='02-15/10C >=40ft/2'>

<cfquery name="qry" dbtype="query" >
select id
from myQuery
where code=<cfqueryparam cfsqltype="varchar" value="#variables.thisCombination#">
</cfquery>

<cfdump var="#qry#" >

    so the issue is probably not with the QoQ

     

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    September 18, 2025

    Could you share the (function) code?

    Anyway, I have a suggestion. It is based on your remark, "when I follow that with a QoQ using the straight string of the value and cfdump that, I get the one record I'm looking for."
    What happens when, within the function, you replace the_rate_value with toString(the_rate_value) for the value going to the query-of-query?

    WolfShade
    WolfShadeAuthor
    Legend
    September 18, 2025

    Hi, BKBK.

     

    The toString() is already in place.  Has been since I originally wrote it.  🙂

     

    I'm accessing the forum from my personal computer (accessing tech support isn't considered "proper use" of company equipment) so will have to manually type it all out, here.  Can't email it to myself - that's considered Data Loss, and I'll get flagged for it.  😕😕

     

    Basically, the cffunction is expecting an AJaX submit of a string containing the billing code and a two digit fiscal year, separated by a pipe.  It uses getToken() to assign the two values to variables, selects the Excel file based upon the fiscal year (files are a three letter Line of Business followed by a two digit year, then (dot) xlsx) so it consumes either COL25.xlsx, or COL26.xlsx.  After that follows the QoQ.  After the QoQ, there is a switch/case containing three values - records returned: 0, 1, or more.  If no records are found, the function returns a message stating as much.  If one record is found, it returns the billing rate.  If more than one is found, it tells the user as much, plus a message to contact the Estimate Office followed by both phone number and email address of the office.

     

    As far as I can tell, none of the COL billing rates for FY26 work.  The FY25 works just fine.

     

    <cffunction name="myFunction" access="remote" returntype="string" returnformat="plain">
  <cfargument name="billingCode" required="yes" type="string" />
  <cftry>
    <cfset variables.thisCombination=canonicalize(getToken(arguments.billingCode,1,'|'),false,false,true) />
    <cfset variables.thisFY=getToken(arguments.billingCode,2,'|') />
    <cfset variables.thisFileName="COL#variables.thisFY$.xlsx" />
    <cfspreadsheet action="read"
        src="#expandfilepath(variables.thisFileName#"
        excludeheaderrow="true"
        headerrow="1"
        query="qry_col_rates"
        sheet="2" />
    <cfquery name="qry" dbtype="query">
        SELECT RATE
        FROM qry_col_rates
        WHERE (thisCol) = <cfqueryparam value="#trim(variables.thisCombination)#" />
    </cfquery>
...

     

    I've asked about the Excel file from the customer.  She is sure that the file was generated exactly the same as the FY25 Excel file.  IDK.  But everything works when I manually enter the billing code value, so I don't think the Excel file is necessarily at fault.


    WolfShade

     

    PS.  I just noticed that the toString() was removed by me, yesterday, trying to figure out what is going on.  I'm putting it back.

     

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    September 19, 2025

    The column name (thisCol) is strange. Have you replaced the actual name for the purpose of privacy in the forum?

     

    Perhaps trimming variables.thisCombination outside the query? I would suggest

     <cfset variables.thisCombination=canonicalize(getToken(arguments.billingCode,1,'|'),false,false,true) />
<cfset variables.thisCombination=trim(toString(variables.thisCombination))>

    followed later by

    <cfqueryparam value="#variables.thisCombination#" cfsqltype="cf_sql_varchar"/>

    Note also the addition of cfsqltype.

     

    Debugging suggestion: replace the code

    <cfquery name="qry" dbtype="query">
        SELECT RATE
        FROM qry_col_rates
        WHERE (thisCol) = <cfqueryparam value="#trim(variables.thisCombination)#" />
    </cfquery>

    with the debugging code

    <cfquery name="qry" dbtype="query">
    SELECT RATE
    FROM qry_col_rates
</cfquery>

<cfdump var="#qry#" abort="true">

    Then see whether the dump contains the (RATE, variables.thisCombination) pair you're interested in.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices