Scripting with User-Defined Tables (UDTs)

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.  


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,


  • 0

    Is there a method to load a bus obj into memory (possibly from a script init event) for the session itself? I have been trying to create a 'hack' of sorts in DFDM where a custom multiline obj can obtain a value from a UDT without a linked validation. I am in the process of creating an integrated credit release tracking system for use between Sales, Shipping, and Accounting. The UDT simply uses the s/o # as the key field since it should always be a 1-to-1 relationship at most (i.e. a sales order wouldn't have two different release records in this table but could have none).

    So the issue is that there is a validation-linked relationship between SalesOrder + Credit Releases UDT. The key of this UDT is simply the order # and is linked by validation, so that allows SO Entry to return columns from this UDT - which is good. However, within the UDT itself is a reference to a 2nd UDT that has the descriptions for the status codes (also validation-linked), which does not pull over to the SO Entry since the link is between the two UDTs not with the S/O.

    So I've been attempting to use my limited knowledge of DFDM in an attempt to trick Sage into grabbing this information anyways, but have always come up unsuccessful. I feel the issue is that the BUSOBJ from the 2nd UDT isn't loaded into memory when attempting to use this 'tag' property:

    In text form:


    My hopes is a similar issue with this topic - but instead of loading the UDT BusObj for scripting, load it for the actual session.

    For a better visual, I am trying to get the description for this UDF_ML_STATUS field that's located in another UDT:

    Which I am aware is not officially supported, hence the reason I called it a 'hack' earlier.

  • 0 in reply to SoonerFan21

    Add your UDF to the SO header table (but not on the panel), and set up a script to refresh that UDF value from the UDT when each record is opened (Post Read).  Then you can have the description, properly linked without any "hack" of the panel settings.

Reply Children
No Data