QoQ issue relating to Excel file consumed

[WolfShade]

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

[BKBK]

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]

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]

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.

[WolfShade]

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

Yes.

I'll give your suggestions a try, but since this is in a cffunction I'll email the dump results (minus the abort), as I am not outputting anything from the function.

WolfShade

UPDATE:  No change.  The QoQ of the function is still not finding the record in the query object that is the cfspreadsheet.  The values being passed are trimmed.  As I understand it, the values in a QoQ are automatically trimmed, so don't need to add trim() around the column name.  It's all within a cftry/cfcatch, so no exceptions are being thrown.

AND only this is having an issue.  The other four FY26 Excel files are working just fine.  It's just this one COL line of business that's not working as expected.  The COL FY25 is working fine, too.

But my "scratch.cfm" can find it in COL FY26, no problem.  It's just within the cffunction that it's not working.  This is driving me crazy.

UPDATE TO THE UPDATE:  Correction.  I added a second QoQ removing the filter, and all records are returned.  So the queryparam seems to be the culprit.

[BKBK]

I think there is a misunderstanding. You say, " The QoQ of the function is still not finding the record in the query object that is the cfspreadsheet. ". The word "record" tells me you tested the query-of-query with a where-clause in the SQL.

However, I had suggested that you should test with a version of the SQL without the where-clause. Place the cfdump code within the e-mail to yourself. Then check whether the dump contains the (RATE, variables.thisCombination) pair you're interested in.

UPDATE: I can now see that the last point has been addressed in your "UPDATE TO THE UPDATE". Did the addition of 

cfsqltype="cf_sql_varchar"

solve the problem?

[WolfShade]

This has me so frazzled. 

I did add a second QoQ without the WHERE clause.  ALL records returned in the cfdump I emailed to myself.  I have removed canonicalize(), I have a single trim() in place for the billing code, and even trim() the FY.  I have added the cfsqltype and tested, then completely removed the cfqueryparam from the first QoQ and tested. 

So, no WHERE clause returns everything, but adding the WHERE clause returns nothing.  But the value being passed for the WHERE clause is tight.  No extra spaces, formatting is proper.

I even changed = to LIKE.

WHERE (thisCol) LIKE '%#variables.thisCombination#%'

Nope.  Still no record.

WolfShade

UPDATE:  I just now noticed that the 26 file headers have dropdowns, but the 25 file does not.  Could this be the culprit????  Customer is redoing the file and resending.  Keeping fingers crossed!!!

UPDATE2: Nope.

[BKBK]

Then the culprit is likely to be the value of 

variables.thisCombination

The function getToken() is probably not returning an expected or up-to-date value. To check this, mail yourself this dump:

<cfdump var="#variables#" >

just before the query-of-query. Is the value variables.thisCombination correct? 

[WolfShade]

First, I sent an email in HTML of the values and an array I pushed some things to:

As you can see by the red field, no extra spaces in either value.  Formatting is correct - two digits, a hyphen, two digits, a slash, two digits, a letter, a space, a greater than, an equal sign, two digits, two letters, a slash, one digit for the billing code, and then two digits, no spaces for the fiscal year.

I just tried the dump of variables scope.  Looking at it, finding the relevant part of the variables scope:

It's all looking correct.  (The THISCOMBINATION is from me switching one field to trigger the function; the value is also in the spreadsheet, just not being found.  That's why it's different from the first image.  I wish this editor had underline - didn't want to use bold for this.)

Customer took her local copy of FY25, deleted the sheet for rates and created a new rates sheet from FY26 data and sent that.  Still not working.

WolfShade

[BKBK]

The two this.combination values don't match. One ends in 1, the other in 2.

[BKBK]

Test using hard-coded alternatives in the where-clause:

where (colName) in <cfqueryparam list="'combination_value_1','combination_value_2','combination_value_3'">

[WolfShade]

Tried that, too.  In the scratch.cfm file, it works if I hard-code the billing code.  I tried it in the .cfc, replacing #variables.thisCombination# with '02-15/10C >=40ft/2', and it returned zero records.

I'm pulling my hair out trying to understand why it works in the scratch.cfm file I created, but not within the .cfc function.  It's a standard AJaX call that's been working for five years.. works with the FY25 files, works with the other FY26 files in other functions (each line of business has it's own function, since the data in each file is very different.)

My gut reaction is that there's something wrong with the data in this particular FY26 file.  But, if that were the case, then it wouldn't work in the scratch.cfm file.. at all.  But it does.

I even tried using two QoQs.. one to fetch all the data from the consumed Excel file, one to get just the billing code record.. nothing.

The function has basically the same attributes as all the functions in this cfc:
name, access="remote", output="no", returntype="string", returnformat="plain".  It takes one argument.  The HTML using the red background for dislplay shows that it's not adding extra spaces.  If I open the Excel file and do a search for '02-15/10C >=40ft/2' it is found.  If I hard-code '02-15/10C >=40ft/2' into scratch.cfm, it works.  It's just not working in the .cfc (even when hard-coded), and only for FY26.  I've done the same form entry for FY25, and it returns a billing rate.

[WolfShade]

Test using hard-coded alternatives in the where-clause:

where (colName) in <cfqueryparam list="'combination_value_1','combination_value_2','combination_value_3'">

By @BKBK

<cfqueryparam value="'02-15/10C >=40ft/2','02-15/10C >=40ft/4'" list="Yes" />.. and I surrounded it with parenthesis.  Still no billing rate in the cffunction.. did same in scratch.cfm.. zero records returned in scratch, with the list.  But..

Okay, now it's just getting weirder.. in scratch.cfm, I hard-coded the billing code '02-15/10C >=40ft/2', both with and without cfqueryparam, and now even scratch.cfm is returning zero records.  Before, it was. 

And I copied the value from the page where it appears in the query string of the CFDUMP and used CTRL-F to find it, and it found it twice in the page.. once in the full recordset return, and once in the CFDUMP of the second query, but not as a record.. in the query string itself.. where I copied it from.

WTF is going on, here?

[WolfShade]

Sorry.. highlighted the reason why the two images are slightly different.

[BKBK]

Did you try my cfqueryparam-list suggestion? There is a reason behind it. The QoQ might be re-encoding the characters.

[WolfShade]

I think we keep cross-posting.. LOL!

[BKBK]

I think we keep cross-posting.. LOL!

By @WolfShade

No worries, @WolfShade . I get the feeling the answer is close by.

[BKBK]

Though you say "Tried that too.", your explanation shows that what you tried was not my suggestion.

[WolfShade]

I did try it.. and I'm going in circles.. 

<cfqueryparam value="'02-15/10C >=40ft/2','02-15/10C >=40ft/4'" list="Yes" />  is what I tried, as the way you typed it threw an error.. something about wrong configuration.. possible attributes are 'value' with optional attributes 'cfsqltype,list,maxlength,null,scale,separator'.  Looked up the cfqueryparam on adobe.com and came up with the bold text, above.  It also errored without the parenthesis. 

It returned zero records in both the cfc and my scratch.cfm file.  So I have no idea what's going on.. I almost feel as if someone is playing a big prank on me.  But this isn't the first time I've had issues like this, and I suspect (sometimes) that it's the ultra-tight security policies for the network that cause a lot of the errors I encounter.

I'd go back to the private sector, but no one in the private sector is looking for a CF developer who doesn't know ten other technologies that I've never heard of.  (Yes, that's an exaggeration.)

[WolfShade]

I took a huge risk and took two screencaps of my browser on my work laptop and sent them to my email.

This is from the FY25 file, bottom of my scratch.cfm page:

This is from the FY26 file, bottom of my scratch.cfm page:

Same code running both, hard-coded billing value.

The FY26 was working yesterday, with a hard-coded value.  But that was before the customer re-did the Excel file.

So, the issue has to be the data.  But the customer says they are generating the files like they did, last year.

[BKBK]

Sorry about the cfqueryparam-list suggestion. I was in a hurry to run some errands. In any case, you eventually got what I meant.

That test seems to point to something. It could be that characters like <, >, =, are throwing QoQ off. After we exclude this possibility, then we know that the cause of the issue is the data.

[WolfShade]

I hear ya about the hurry.. 

That test seems to point to something. It could be that characters like <, >, =, are throwing QoQ off.

By @BKBK

But if that were the case, it should also fail on the FY25 file.

[BKBK]

I hear ya about the hurry.. 

That test seems to point to something. It could be that characters like <, >, =, are throwing QoQ off.

By @BKBK

But if that were the case, it should also fail on the FY25 file.

By @WolfShade

Just to confirm so we're on the same page: are you saying that the QoQ in the function works as expected for FY25? Where-clause, cfqueryparam and all? I had thought the whole time that it didn't work for any FY.

[BKBK]

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?

[WolfShade]

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

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!