x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 12 replies
  • Subscribers 90 subscribers
  • Views 5035 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
963
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
jepritch

Scripting with User-Defined Tables (UDTs)

Posted By

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

Parents
  • 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:

    BUSOBJ=CM_UDTMaint_bus,SO_UDT_CR_STATUSCODES;
    UDTTBL=SO_UDT_CR_STATUSCODES;
    UDFTBL=SO_UDT_CR_STATUSCODES;
    UDFCOL=UDF_DESCRIPTION;
    UDFLOCK;
    BIND=ALB_UDF_CRDRELNO_UDF_ML_STATUS,UDF_DESCRIPTION$; (Also tried UDF_ML_STATUS)

    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.

Reply
  • 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:

    BUSOBJ=CM_UDTMaint_bus,SO_UDT_CR_STATUSCODES;
    UDTTBL=SO_UDT_CR_STATUSCODES;
    UDFTBL=SO_UDT_CR_STATUSCODES;
    UDFCOL=UDF_DESCRIPTION;
    UDFLOCK;
    BIND=ALB_UDF_CRDRELNO_UDF_ML_STATUS,UDF_DESCRIPTION$; (Also tried UDF_ML_STATUS)

    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.

Children

*Community Hub is the new name for Sage City