I am creating a simple spreadsheet from a query using CF Spreadsheet functions. Code is below. I know we're on an ancient version. This is not my call. Thankfully we will finally be upgrading in a couple of months.
In the field OMS Num, we have an alphanumeric value which is always 4 characters. Some of these are dropping the last character when adding to the row. The value itself is complete all the way up to the point of spreadsheetaddrows. If I put anything before or after it, it works fine. I've tried SpreadsheetFormatColumn as well to no avail.
I've experimenting putting other values before the last letter and the same thing happens. So "12345D" becomes "13245" "863D" becomes "863". Any clues what's happening here and how to remedy it?
Data: "OMS" field
365A
UA66
UA74
705D - Converts to 705
592A
UK71
985A
216F - Converts to 216
940C
335F - Converts to 335
337F - Converts to 337
Code snippet
<Cfset qQuestionTypes = QueryNew("VERWMERKM,PLNNR_OMSSeqGrp,SourceType,OPS_ID,InputType,WorkCenter,OMS,OMS_ItemNbr,PartNbr,PartDesc,PartQty,HasInspectMethPic,QG_Group,ARBPL_WorkCenter","varchar, varchar,varchar,varchar, varchar,varchar, varchar,varchar,varchar, varchar,varchar, varchar,varchar,varchar")>
<cfset QueryAddRow(qQuestionTypes)>
<cfset QuerySetCell(qQuestionTypes,"VERWMERKM","A#numberformat(VERWMERKM,'0000000')#")>
<cfset QuerySetCell(qQuestionTypes,"PLNNR_OMSSeqGrp",PLNNR)>
<cfset QuerySetCell(qQuestionTypes,"SourceType",qQuestDetails.SourceType)>
<cfset QuerySetCell(qQuestionTypes,"OPS_ID",OPS_ID)>
<cfset QuerySetCell(qQuestionTypes,"inputtype",qQuestDetails.inputtype)>
<cfset QuerySetCell(qQuestionTypes,"WorkCenter",new_dummy10)>
<cfset QuerySetCell(qQuestionTypes,"OMS",TRIM(new_dummy20))>
<cfset QuerySetCell(qQuestionTypes,"OMS_ItemNbr",new_dummy40)>
<cfset QuerySetCell(qQuestionTypes,"PartNbr",qPartDetails.component_number)>
<cfset QuerySetCell(qQuestionTypes,"PartDesc",qPartDetails.Description)>
<cfset QuerySetCell(qQuestionTypes,"PartQty",qPartDetails.Qty)>
<cfset QuerySetCell(qQuestionTypes,"HasInspectMethPic",hasInspMeth)>
<cfset QuerySetCell(qQuestionTypes,"QG_Group",QualityGateRef)>
<cfset QuerySetCell(qQuestionTypes,"ARBPL_WorkCenter",ARBPL)>
<cfset QuerySetCell(qQuestionTypes,"SourceType",qQuestDetails.SourceType)>
<cfset sSpreadsheet = spreadsheetNew("QM3Details",TRUE)>
<cfset spreadsheetSetActiveSheet(sSpreadsheet,"QM3Details")>
<!--- Add header rows --->
<cfset spreadsheetAddRow(sspreadsheet, "SAP Question Nbr,OMS Seq Grp, Source Type, OPS_ID, Input Type, Work Center, OMS Num, OMS item, Part Number, Part Desc, Part Quantity, Has Insp Meth Pic,QG Group, ARBPL_Workcenter")>
<!--- Test of individual writes
<cfloop query="qQuestionTypes">
<cfset spreadsheetaddrow(sspreadSheet, '#qQuestionTypes.VERWMERKM#,#qQuestionTypes.PLNNR_OMSSeqGrp#,#qQuestionTypes.SourceType#,#qQuestionTypes.OPS_ID#,#qQuestionTypes.InputType#,#qQuestionTypes.WorkCenter#,#tostring(qQuestionTypes.OMS)#,#qQuestionTypes.OMS_ItemNbr#,#qQuestionTypes.PartNbr#,#qQuestionTypes.PartDesc#,#qQuestionTypes.PartQty#,#qQuestionTypes.HasInspectMethPic#,#qQuestionTypes.QG_Group#,#qQuestionTypes.ARBPL_WorkCenter#')>
</cfloop> --->
<cfset spreadsheetaddrows(sspreadSheet, qQuestionTypes,20,1,true)>
<!--- Write File --->
<cfset fileModDate=DateFormat(variables.dtCurrentDateTime, 'yyyymmdd_HHmmss')>
<cfset qm3filename="\somefolder\QM3_Details_#fileModDate#.xlsx">
<cfset spreadsheetWrite(sspreadsheet, qm3filename, true)>
3
Replies
AdChoices