On another thread I was asked how to use User-Defined Tale (UDT) within User-defined Scripting. So, I thought I'd share a quick example. Firstly, there is very little difference in dealing with a UDT vs one of Sage's other business type objects. The main difference is in how you instantiate the object itself.
In my example, I have created a quick and dirty Audit table for anyone who has changed any sales order. In this table I have an AuditKey (which consists of Order + Date + Time), OrderNo, ChangeDate, ChangeTime, and ChangeUser. For those of you familiar with scripting this should look like many other scripts you have written with the exception of the oSession.GetObject() call.
When dealing with UDT's there are two objects you can use:
- CM_UDTMaint_bus - which is a business object, used to maintain your table
- CM_UDTMaint_svc - which is a read-only service object, to be used mainly when you are strictly reading the table. For field validation for example.
When instantiating these objects using oSession.GetObject() there are 2 parameters you need to pass. One is the name of the object (CM_UDTMaint_bus) and the other is the name of the user-defined table. In my example (SO_UDT_OrderAudit). After instantiating the object, you would use it like you would use any other Sage object, with SetKey(), SetValue(), Write(), Delete(), GetValue(), EditState, etc.
The following script I have linked to the pre-write event of the Sales Order Header table and will track when anyone makes any changes to a sales order. Not sure how practical my example is but hopefully you get the idea.
Example:
retVal = 0
oAudit = 0
Set oAudit = oSession.AsObject(oSession.GetObject("CM_UDTMaint_bus", "SO_UDT_ORDERAUDIT"))
order = ""
retVal = oBusObj.GetValue("SalesOrderNo$", order)
user = ""
user = oSession.UserName
dateStamp = ""
timeStamp = ""
userKey = ""
retVal = oSession.GetStampInfo(userKey, dateStamp, timeStamp)
retVal = oAudit.SetKey(order+dateStamp+timeStamp)
If retVal > 0 Then
retVal = oAudit.SetValue("UDF_ChangeDate$", dateStamp)
retVal = oAudit.SetValue("UDF_ChangeTime$", timeStamp)
retVal = oAudit.SetValue("UDF_ChangeUser$", user)
retVal = oAudit.SetValue("UDF_OrderNo$", order)
retVal = oAudit.Write()
If retVal=0 Then
' error on the write to audit table
End If
End If
This results in this type of info in the audit table when you view it from UDT Maintenance
Hope this helps,
Elliott
