UDF Date filter not working with SO Printing Script - BUG?

SOLVED

Huge thanks to David Speck for your code and your expertise.

I've got a script that works well for printing all SOs, but I'm trying to filter it down using a UDF Date Parameter (UDF_Delivery_By) we have in SO Printing.  I'm certain I have my syntax wrong.  Any suggestions for getting this working?  

The relevant bit of code is below with the lines that are seemingly being ignored in blue .  All orders continue to be printed.  I've checked that the filter works when printing inside of Sage SO Printing.  Also, there are no errors when running the script.  It just prints all SOs not yet printed.

nRowKey = 1
retval = oSO.nSelectReportSetting(sReportSetting)
   ErrorLog retval, "Error setting report setting.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKeyValue("ModuleCode$", scModuleCode)
   ErrorLog retval, "Error setting module code.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKeyValue("CompanyKey$", scCompanyKey)
   ErrorLog retval, "Error setting company key.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKeyValue("ReportID$", scReportID)
   ErrorLog retval, "Error setting report ID.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKeyValue("ReportSetting$", UCase(sReportSetting))
   ErrorLog retval, "Error setting report setting.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKeyValue("RowKey$", Right("00000" & CStr(nRowKey), 5))
   ErrorLog retval, "Error setting RowKey", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetKey()
   ErrorLog retval, "Error setting key.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir


retval = oSO.nSetValue("SelectField$", "Order Number")
   ErrorLog retval, "Error selecting order number field.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("SelectFieldValue$", "Order Number")
   ErrorLog retval, "Error selecting field value.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("KeyReference$", "<SALESORDERNO$>")
   ErrorLog retval, "Error setting key reference.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Tag$", "TABLE=SO_SALESORDERHEADER;COLUMN=~SalesOrderNo$")
   ErrorLog retval, "Error setting tag.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Operand$", ">") 'all orders
   ErrorLog retval, "Error setting operand.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Value1$", "") 'all orders
   ErrorLog retval, "Error setting SO No.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir



retval = oSO.nSetValue("SelectField$", "UDF_Delivery_By")
   ErrorLog retval, "Error selecting UDF_Delivery_By field.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("SelectFieldValue$", "UDF_Delivery_By")
   ErrorLog retval, "Error selecting UDF_Delivery_By field value.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Tag$", "TABLE=SO_SALESORDERHEADER;COLUMN=~UDF_Delivery_By$")
   ErrorLog retval, "Error setting UDF_Delivery_By tag.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Operand$", "L")
   ErrorLog retval, "Error setting UDF_Delivery_By operand.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
retval = oSO.nSetValue("Value1$", "20211203")
   ErrorLog retval, "Error setting UDF_Delivery_By value 1.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir


retval = oSO.nWrite()
   ErrorLog retval, "Error writing.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir


On Error Resume Next
retval = oSO.nProcessReport("PRINT")
   ErrorLog retval, "Error printing.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir 
if retval <> 0 then
   ErrorLog retval, "Sales Order(s) printed successfully.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
end if

  • 0

    What you are using for the UDF_Delivery_By$ looks correct.  However, for the SalesOrderNo$, if you are not selecting a range or actual less/greater than value, i don't think you need to bother including it.  Also, a better way to get ALL is to use A as the operand, i don't think you would have to bother setting Value1$ or Value2$ in this case.  Refer to the following link for more info on report selections.

    File Layouts and Program Information - SY_Selection (sage.com)

  • 0 in reply to David Speck

    Thanks, David.  I have already tried excluding the portion for SalesOrderNo$ selection, but it did not help.  I am still getting all unprinted SOs and the date parameter is being ignored.  Any other suggestions?  And thank you very much for your feedback.

  • 0 in reply to n0tgunshy2

    What are the returned values looking like when you are setting the filter up for the UDF_Delivery_By$?

    Since you are doing "Less than" on "12/3/2021", can you try using a tighter filter or range instead? Maybe try using "Equal to" on a known good value?

  • 0 in reply to David Speck

    Unfortunately, I've done that, also.  No go.  Totally ignores my filter.  Would it help if I pasted the entire script?

  • 0 in reply to David Speck

    Also, if all else fails and you are not previewing the report, it may be possible to use the report object's SetReportWhereClause method.  You pass it a string that is similar to the filter used with GetResultSets.  It always seems to return a 0 even when it succeeds by the way.

    I know for sure that SetReportWhereClause can be used with the ProcessReportWorkTable method but I'm not positive it will work with the ProcessReport method so this may or may not work for you.

    Here are some samples from when I was testing an inventory report.  Replace the "Character" with "Chr".  The forum doesn't like "Chr" within code samples.

    sWhereClause = ""
    ' Product Line equal to "FD&A".
    	sWhereClause = "CI_Item=UCS(~ProductLine$)=UCS(""FD&A"");"
    	
    ' Item Code Begins with "2" And Product Line Equal to "FD&A".
    	sWhereClause = "CI_Item=MID(UCS(~ItemCode$),1,1)=UCS(""2"") AND UCS(~ProductLine$)=UCS(""FD&A"");"
    	
    ' Item Code Equal to "2551-3-50" And Product Line Equal to "FD&A".
    	sWhereClause = "CI_Item=UCS(~ItemCode$)=UCS(""2551-3-50"") AND UCS(~ProductLine$)=UCS(""FD&A"");"
    	
    ' Item Code Greater than or equal to "2".
    	sWhereClause = "CI_Item=UCS(~ItemCode$)>=UCS(""2"");"
    	
    ' Item Code in a range from "D" to "E". The Character(0) and Character(255) are added to the begin and end respectfully to make sure the range is proper if you were to specify "D" and "D" as the Begin and End, which is essentially a "Begins with" anyway.
    	sRangeBegin = "D"
    	sRangeEnd = "G"
    	sRangeBegin = sRangeBegin & Character(0)
    	sRangeEnd = sRangeEnd & Character(255)
    	sWhereClause = "CI_Item=UCS(~ItemCode$)>=UCS(""" & sRangeBegin & """) AND UCS(~ItemCode$)<=UCS(""" & sRangeEnd & """);"

  • 0 in reply to n0tgunshy2

    Possibly at least the portions of the script that contain the oSO object. 

    Have you tried using just a filter on the sales order number being equal to a specific value to see if that is even working as expected?

  • 0 in reply to David Speck

    Yes, actually.  The specific range of SOs works.  Which is why I thought I must be coding the filter for UDF_Delivery_Date wrong.  I'll paste the oSO object related code below.  And I'm reviewing your comments regarding the SetReportWhereClause, too.  Thanks, David!  

  • 0 in reply to n0tgunshy2

    Have you double checked the spelling of the UDF's name?

    What does the data in the UDF look like in DFDM?

  • 0 in reply to n0tgunshy2

    Be sure to convert your dates to the Sage date format.

  • 0 in reply to n0tgunshy2

       retval = oSS.nSetCompany(sCompany) 'removed some error checking here for legibility
    	
       retval = oSS.nSetDate(sModule, sRunDate) 'removed some error checking here for legibility
       
       retval = oSS.nSetModule(sModule) 'removed some error checking here for legibility
       
    	retVal = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrderPrinting_Ui")) 'removed some error checking here for legibility
    	Set oSO = oScript.NewObject("SO_SalesOrderPrinting_rpt", oSS) 'removed some error checking here for legibility
    
            oSS.nTerminateUI() 'Terminate UI to avoid screen prompts
    
    	If oSO.nScriptObject <> 0 Then
    	oSO.oScriptObject.nDeactivateProcedure "*ALL*"
    	'oSO.oScriptObject.nActivateProcedure "*ALL*" 
    	End If
    	
    'Assign print params
            scModuleCode = oSS.sModuleCode
    			ErrorLog retval, "Error getting module code.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
            scCompanyKey = oSS.sCompanyKey
    			ErrorLog retval, "Error getting company key to " & sCompanyKey, oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
            scReportID = "": retval = oSO.nGetValue("cReportID$", scReportID)
    			ErrorLog retval, "Error getting report ID.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
            sReportSetting = "AUTOPRINT"
    		retval = oSO.nSetTemplateDesc("Plain")
    			ErrorLog retval, "Error setting template to plain.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
    			
                nRowKey = 1
                retval = oSO.nSelectReportSetting(sReportSetting)
    				ErrorLog retval, "Error setting report setting.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKeyValue("ModuleCode$", scModuleCode)
    				ErrorLog retval, "Error setting module code.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKeyValue("CompanyKey$", scCompanyKey)
    				ErrorLog retval, "Error setting company key.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKeyValue("ReportID$", scReportID)
    				ErrorLog retval, "Error setting report ID.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKeyValue("ReportSetting$", UCase(sReportSetting))
    				ErrorLog retval, "Error setting report setting.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKeyValue("RowKey$", Right("00000" & CStr(nRowKey), 5))
    				ErrorLog retval, "Error setting RowKey", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetKey()
    				ErrorLog retval, "Error setting key.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
               ' retval = oSO.nSetValue("SelectField$", "Order Number")
    				'ErrorLog retval, "Error selecting order number field.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
               ' retval = oSO.nSetValue("SelectFieldValue$", "Order Number")
    				'ErrorLog retval, "Error selecting field value.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetValue("KeyReference$", "<SALESORDERNO$>")
    				ErrorLog retval, "Error setting key reference.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
    			'retval = oSO.nSetValue("Tag$", "TABLE=SO_SALESORDERHEADER;COLUMN=~SalesOrderNo$")
    				'ErrorLog retval, "Error setting tag.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
               ' retval = oSO.nSetValue("Operand$", "A") 'all orders
    				'ErrorLog retval, "Error setting operand.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
    
    			
    			retval = oSO.nSetValue("SelectField$", "WarehouseCode")
    				ErrorLog retval, "Error selecting WarehouseCode field.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetValue("SelectFieldValue$", "WarehouseCode")
    				ErrorLog retval, "Error selecting WarehouseCode field value.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir				
    			retval = oSO.nSetValue("Tag$", "TABLE=SO_SALESORDERHEADER;COLUMN=~WarehouseCode$")
    				ErrorLog retval, "Error setting WarehouseCode tag.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                retval = oSO.nSetValue("Operand$", "=") 
    				ErrorLog retval, "Error setting WarehouseCode operand.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                'retval = oSO.nSetValue("Value1$", sDelDate)
    			retval = oSO.nSetValue("Value1$", "111")
    				ErrorLog retval, "Error setting WarehouseCode value 1.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
    			
    			
                retval = oSO.nWrite()
    				ErrorLog retval, "Error writing.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
                On Error Resume Next
                retval = oSO.nProcessReport("PRINT")
    				ErrorLog retval, "Error printing.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir '<<<<<<<<<<<<<<<ERROR OCCURS HERE
    				if retval <> 0 then 
    				ErrorLog retval, "Sales Order(s) printed successfully.", oSO.sLastErrorMsg, sRunDate, oWriteObj, strLogFile, strTempDir
    				end if
                
            retval = oSS.nLogoffUser()