UDF to save the original DateUpdated in AR_InvoiceHistoryHeader

SOLVED

We base a number of automated reports on the invoice history table's DateUpdated field, which works great under normal circumstances, but if you do a VI import to update something in posted invoice data, the DateUpdated is changed... causing duplicated notifications.

Working as designed.

So, I am trying to capture the original DateUpdated in a UDF (intending to use this as the report filter instead of the Sage field).  However, I can't get it to work (v2020).

My first attempt was to set up the UDF with a Default of SystemDate, but when posting new test invoices the UDF is left blank.

Then I tried a Pre-Write script to set the value, but from what I can tell, the script is not even triggering (during the posting process). 

I think both problems may be due to the fact that the business object associated with the history UDF is AR_InvoiceHistoryInquiry_bus... and I'm running out of ideas.

Has anyone successfully created a mirror UDF to store a date in AR_InvoiceHistoryHeader, set during the posting process?  To clarify, it cannot be data from the invoice (InvoiceDate, TransactionDate...)... what I need is to capture the "now" date when the user clicks "Yes" to the journal update.

I'd really prefer to solve this problem with Sage tools (instead of a SQL trigger).  Any suggestions would be welcome.

Parents
  • 0

    It isn't ideal but what if you left the script on the pre-write of the header so it will be triggered by a VI import.  In the script, have it check if your UDF is blank, if it is, copy the original DateUpdated value into your UDF.  If it isn't blank, ignore it as it was previously populated with what should have been the original DateUpdated value.  Then in your reports/queries, can you use the COALESCE function on your UDF as the first argument and the DateUpdated as the second argument?

  • 0 in reply to David Speck

    Thanks David.  Ideally I'm looking for something simple for others to understand, which will work with SQL and non-SQL systems, but non-ideal that does the job is perfectly fine.

    The original idea I started with was an "updated by VI" checkbox UDF, which would be set by script, similar to your idea... where we'd exclude all rows updated by VI, but that would not work if a VI job was run the same day as posting (which does happen sometimes).

    I'll do a bit of testing to see if I can get your strategy to work.  It all depends on if the VI triggered script can grab the original DateUpdated before it's changed by the program.  The customer who needs it now has their alerts in KS / SA&WF, which does make things a bit more difficult to test, but they are Premium so I can set up a SQL View (to swap in the different date) if I can only sometimes get the original date into the UDF.

    I also had an idea of adding a UDF and running the script in AR_TransactionPosting, then flowing the Original value from there, which would be even better (no script)... if it works.  (To be tested).

  • 0 in reply to Kevin M
    SUGGESTED

    FYI, you can use the IfNull scaler function for non-SQL systems.

    Here is the SQL.

    Select 
        CI_Item.*, 
        {fn IfNull(CI_Item.LastSoldDate, CI_Item.LastReceiptDate)} 
    From 
        CI_Item 
    Where 
        CI_Item.ItemType = '1'

    Here is a snippet of the function in action.

  • 0 in reply to David Speck

    Thanks David, that may be handy later but I really need to capture the original Date Updated somewhere for anything to work on the query side.

    Running the script Pre-Write during a VI job is too late... the DateUpdated has already been changed.  I expect Get / Set storage var's would solve that but I have not gone that far (yet?).

    Advanced field settings for AR_InvoiceHistoryHeader to initialize the UDF to SystemDate didn't work, which was a bit of a surprise.  I thought that was going to be a nice simple solution, when the idea came to mind.  Very disappointing.

    I can't set a default for an AR_TransactionPosting UDF, nor attach a script to that table.

  • 0 in reply to Kevin M
    SUGGESTED

    I see what you mean, a table's pre-write event is "too late" when you use GetValue.  You could use perform logic set up like this,

    with the following code.  You will need to adjust the UDF's name though.

    coBusiness'GetValue("UDF_DateUpdated$", S_UDF_DateUpdated$); IF S_UDF_DateUpdated$ = "" THEN { coBusiness'GetValue("DateUpdated$", S_DateUpdated$); coBusiness'SetValue("UDF_DateUpdated$", S_DateUpdated$) } ! '

  • +1 in reply to David Speck
    verified answer

    Also, unfortunately, the ChangedRecordInfo method doesn't pick up the DateUpdated field so you can't use it.  However, you could use GetColumns("Main") to determine which position in the value returned by GetOrigRecord holds the DateUpdated value in a pre-write event script.  Both methods return a SEP separated string so you can split one into an array and loop through it until you find the DateUpdated column and if you track the current loop number, you can use that as the subscript for the array that holds the original record that you would split. 

    Something like this should work.  You'll need to correct the "C h r" functions to Chr.

    sDateUpdated = "" : oBusObj.GetValue "DateUpdated$", sDateUpdated
    oScript.DebugPrint "sDateUpdated: " & sDateUpdated
    sOriginalDateUpdated = ""
    sMain_Columns = "" : sMain_Columns = oBusObj.GetColumns("Main")
    sOriginalRecord = "" : sOriginalRecord = oBusObj.GetOrigRecord()
    If InStr(sMain_Columns, C h r(138)) <> 0 And InStr(sOriginalRecord, C h r(138)) <> 0 Then
    	aMain_Columns = Split(sMain_Columns, C h r(138))
    	aOriginalRecord = Split(sOriginalRecord, C h r(138))
    	For nColumnNumber = 0 To UBound(aMain_Columns)
    		If UCase(aMain_Columns(nColumnNumber)) = UCase("DateUpdated$") Then
    			sOriginalDateUpdated = aOriginalRecord(nColumnNumber)
    		End If
    	Next
    End If
    oScript.DebugPrint "sOriginalDateUpdated: " & sOriginalDateUpdated

  • 0 in reply to David Speck

    This has serious potential for sure.  I never knew about the GetOrigRecord() method.  Thanks David. 

    I'll throw in an EditState check and give this a try.

  • 0 in reply to Kevin M

    FYI, I finally tested this idea, and made something work (on my test system).  Instead of a check on the EditState, I went with checking the UDF instead.

    	retVal = 0
    	sUDFDateUpdated = "" : sOriginalDateUpdated = ""
    	retVal = oBusObj.GetValue("UDF_ORIG_DATE_UPDATED$", sUDFDateUpdated)
    	if sUDFDateUpdated = "" Then
    		sMain_Columns = "" : sMain_Columns = oBusObj.GetColumns("Main")
    		sOriginalRecord = "" : sOriginalRecord = oBusObj.GetOrigRecord()
    		If InStr(sMain_Columns, Ch r(138)) <> 0 And InStr(sOriginalRecord, Ch r(138)) <> 0 Then
    			aMain_Columns = Split(sMain_Columns, Ch r(138))
    			aOriginalRecord = Split(sOriginalRecord, Ch r(138))
    			For nColumnNumber = 0 To UBound(aMain_Columns)
    				If UCase(aMain_Columns(nColumnNumber)) = UCase("DateUpdated$") Then
    					sOriginalDateUpdated = aOriginalRecord(nColumnNumber)
    				End If
    			Next
    			retVal = oBusObj.SetValue("UDF_ORIG_DATE_UPDATED$", sOriginalDateUpdated)
    		End If
    	end if

    Thanks again!

Reply
  • 0 in reply to Kevin M

    FYI, I finally tested this idea, and made something work (on my test system).  Instead of a check on the EditState, I went with checking the UDF instead.

    	retVal = 0
    	sUDFDateUpdated = "" : sOriginalDateUpdated = ""
    	retVal = oBusObj.GetValue("UDF_ORIG_DATE_UPDATED$", sUDFDateUpdated)
    	if sUDFDateUpdated = "" Then
    		sMain_Columns = "" : sMain_Columns = oBusObj.GetColumns("Main")
    		sOriginalRecord = "" : sOriginalRecord = oBusObj.GetOrigRecord()
    		If InStr(sMain_Columns, Ch r(138)) <> 0 And InStr(sOriginalRecord, Ch r(138)) <> 0 Then
    			aMain_Columns = Split(sMain_Columns, Ch r(138))
    			aOriginalRecord = Split(sOriginalRecord, Ch r(138))
    			For nColumnNumber = 0 To UBound(aMain_Columns)
    				If UCase(aMain_Columns(nColumnNumber)) = UCase("DateUpdated$") Then
    					sOriginalDateUpdated = aOriginalRecord(nColumnNumber)
    				End If
    			Next
    			retVal = oBusObj.SetValue("UDF_ORIG_DATE_UPDATED$", sOriginalDateUpdated)
    		End If
    	end if

    Thanks again!

Children
No Data