Other ERPsFree generatorPDF, Excel, Word exportSage 100All Sage X3 and Sage 100 tools

Sage 100 User-Defined Script Generator

This generator writes a Sage 100 user-defined script (VBScript) for the sales order or invoice header that reads the customer's credit hold flag, copies it into a UDF and can stop the save. It targets Sage 100 Standard, Advanced and Premium, 2019 to 2025, where Custom Office scripting runs inside the business objects.

Fill in

Table (business object)

Both tables carry ARDivisionNo$ and CustomerNo$.

Event

Only Pre-Write can stop the save with SetError.

Customer field

Target UDF (string)

Message text

Block the save

Generated code

Download:

User-defined script (VBScript)

' Sage 100 UDS - SO_SalesOrderHeader - Table - Pre-Write
' Copies AR customer CreditHold$ into UDF_CREDIT_HOLD$ and optionally blocks the save.
retVal = 0
sDiv = ""
sCust = ""
sHold = ""

retVal = oBusObj.GetValue("ARDivisionNo$", sDiv)
retVal = oBusObj.GetValue("CustomerNo$", sCust)

If sCust <> "" Then
    Set oCust = oSession.AsObject(oSession.GetObject("AR_Customer_svc"))
    retVal = oCust.SetKeyValue("ARDivisionNo$", sDiv)
    retVal = oCust.SetKeyValue("CustomerNo$", sCust)
    retVal = oCust.Find()
    If retVal = 1 Then
        retVal = oCust.GetValue("CreditHold$", sHold)
        retVal = oBusObj.SetValue("UDF_CREDIT_HOLD$", sHold)
        If sHold = "Y" Then
            If "Yes" = "Yes" Then
                retVal = oScript.SetError("Customer is on credit hold. Contact credit control before saving.")
            ElseIf oSession.UI <> 0 Then
                retVal = oSession.AsObject(oSession.UI).MessageBox("", "Customer is on credit hold. Contact credit control before saving.")
            End If
        End If
    End If
    ' retVal = oScript.DebugPrint("Cust " & sDiv & "-" & sCust & " hold=" & sHold)
    Set oCust = Nothing
End If

Where to attach it

Setting	Value
Menu	Custom Office > Main > User-Defined Script Maintenance
Table	SO_SalesOrderHeader
Event	Table - Pre-Write
UDF needed	UDF_CREDIT_HOLD$ (string) created in User-Defined Field and Table Maintenance
After saving	Compile the scripts, then have users close and reopen the task

Works in Sage 100 Standard, Advanced and Premium (Standard runs scripts on the workstation, Advanced and Premium on the server) and requires custom office scripts enabled for the company in Company Maintenance. String fields end in $ (CustomerNo$, CreditHold$); numeric fields such as CreditLimit do not, and their variables must start as 0, not "". SetError only stops the write on pre-events; on Column Post-Validate it does not undo the value, which is why the generator shows a message instead. Uncomment DebugPrint only while testing with the debugging window on. Scripts that call external COM objects need Allow External Access checked in Company Maintenance. Verify field names in the Sage 100 File Layouts for your version.

Want this script reviewed?

Send us your table, event and rule and we will check the script against your Sage 100 version.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Pick the table and event, keep CustomerNo$ as the source field unless your customer number sits in another field, and type the UDF that should receive the flag. Choose whether the script blocks the save. Copy the code, then in Custom Office open User-Defined Script Maintenance, add a script for the same table and event, paste the code and accept. Compile the scripts when prompted. Create the UDF first in User-Defined Field and Table Maintenance, as a string of length 1, or SetValue returns 0 and nothing is written.

Table Pre-Write: the only option here that can cancel the save.
Column Post-Validate: attach to CustomerNo to set the UDF the moment the customer is entered.
Test in a copy of the company, not the live one.

Objects the script uses

oBusObj is the business object the script is attached to; GetValue and SetValue read and write its fields by name. oSession is the Sage 100 session: GetObject("AR_Customer_svc") returns a handle to the customer service object and AsObject turns the handle into an object you can call. On the service object, SetKeyValue sets each key column (ARDivisionNo$ and CustomerNo$) and Find() reads the record, returning 1 when found. oScript is the script context: SetError raises an error that cancels a pre-event, and DebugPrint writes to the debugging window. oSession.UI is 0 when no user interface is present, for example when the business object runs from Visual Integrator or BOI, so always test it before calling MessageBox.

Worked example

Customer 01-ABF has Credit Hold set to Y in Customer Maintenance. With Table Pre-Write and block save Yes, a user who tries to accept a sales order for 01-ABF gets the message and the order stays on screen unsaved; UDF_CREDIT_HOLD$ is set to Y on the header. With Column Post-Validate attached to CustomerNo and block save No, the UDF is filled and the user sees a message box as soon as the customer is entered, but can still save. A VI import for the same customer gets the SetError text in the import log on Pre-Write and no message box, because oSession.UI is 0.

Version differences and gotchas

In Advanced and Premium the script runs on the server, so it cannot touch files or COM objects on the user's workstation; in Standard it runs where the task runs. Changes made in a Column Pre-Validate script can be overwritten by Sage's own validation that follows, which is why Post-Validate is the usual choice for setting values. Sage 100 already has built-in credit checking in Sales Order Options; use this script when you need a different rule or a visible flag on the order. After every product update, recompile scripts and retest them.

Frequently Asked Questions

Where do I add a user-defined script in Sage 100?

Open Custom Office > Main > User-Defined Script Maintenance, choose the table (for example SO_SalesOrderHeader), add a new script with the event you want (Table Pre-Write, Column Post-Validate and so on), paste the code and accept. Compile the scripts when prompted and reopen the task to load them.

How do I stop a save from a Sage 100 script?

Call oScript.SetError("message") from a pre-event such as Table Pre-Write. Sage 100 cancels the write and shows the message to the user, or logs it for VI and BOI. On post-events SetError does not undo anything that already happened, so use a pre-event when you need to block.

Why does my script not change the field value?

Common reasons: the UDF does not exist or has a different name, the field is numeric but you passed a string (or the reverse), the script is attached to a Pre-Validate event and Sage's own validation overwrote the value, or the scripts were not compiled. Check the SetValue return: 1 means success, 0 means failure.

How do I show a message box from a Sage 100 script?

Use retVal = oSession.AsObject(oSession.UI).MessageBox("", "text"), but only when oSession.UI is not 0. When the business object runs from Visual Integrator, BOI or a web service there is no user interface and the call fails, so guard it with If oSession.UI <> 0 Then.

If you want staff to ask Sage 100 about customers, holds and open orders in plain English on your own servers, we can add that layer for you.

Talk to an Expert Explore More Free Tools