UDF to save the original DateUpdated in AR_InvoiceHistoryHeader

Question

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.

David Speck · Accepted 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

David Speck · Answer

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.

David Speck · Answer

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$) } ! '

Sage 100

UDF to save the original DateUpdated in AR_InvoiceHistoryHeader

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING