Skip to the main content.





Paya Nuvei Logo 2


AvidXchange Logo USE


5 min read

Sage 100 ERP Consultant Tip: Using Scripts to Customize Sage 100 ERP

Sage 100 ERP Consultant Tip: Using Scripts to Customize Sage 100 ERP

Sage 100 ERP Consultant Tip on How to Customize Sage 100 ERP with Scripts

The ability to customize your system is to your specific needs is so important to get the most out of your processes based on your business needs.  With Sage 100 ERP (formerly Sage MAS 90 / MAS 200) you have this ability within a flexible, feature rich and low cost of entry business management system.  Sage 100 ERP also offers the additional option to use scripts to customize the system to your own requirements.

Sage 100 ERP includes standard financial accounting, logistics, manufacturing, and payroll modules, also including important productivity tools such as, paperless office, visual process workflows, Sage Intelligence Reporting, customer relationship management, and eBusiness Manager. You can also leverage additional 3rd party functionality that integrates directly to enrich the capabilities of Sage 100, like EDI seamless integration, multicurrency, warehouse management, shipping automation, invoice automation, CRM integration, etc.

Sage 100 ERP delivers ease of use, depth of functionality and features in an easy to implement and cost effective solution.  Other ERP software require certified programmers to customize their systems, but Sage 100 provides tools called Custom Office and Customizer which provide a user friendly interface for end-users to create and maintain the customizations themselves. Please refer to our previous blog Sage 100 ERP Customizations for Sales which discusses the customization process for creating user defined fields by using the Custom Office and Customizer. In this blog, I will continue discussing the customization in writing scripts.

Scripts are mini programs that run in Custom Office and add value to Sage 100 with little effort. They can be written in Java Script, VB Script or Providex script. In most cases, the script code is very straight forward, such as re-calculating the price or changing values. In some special cases, the scripts may need some program experience in VB Scripts and SQL language. I will discuss some special requirements with the sample scripts in the following content.

There are two ways to launch the screen for writing the scripts. The first way is to open Custom Office -> Main -> Customizer Selection -> Module name (ex. Sales Order) -> Function Name (ex. Sales Order Entry) -> Panel Name (ex. PHEADER). Then you get the scope selection for security control as below.


The scope is to apply the change to the selected users and companies. There is an ‘All’ option for applying to all users or companies. After selecting the scope, you will get the customizer screen as below.


After clicking the ‘Link Settings’ tab and selecting the Type to ‘MS Script’, you then can start to write the scripts for special requirements.
The following examples show the scripts for the special requirements. All of them are using the first way to launch the scripts by adding an external link button.

1. Modify values

Task: click a button to update the Customer PO # based on the customer no with the order date only if the customer number is ‘ORANGE’ in the Sales order header.
The screen design is below – adding a button ‘Auto PO #’


Adding three variables with one return for the customer PO # at the External Link Definition of the button ‘Auto PO#’ as above.


By clicking the edit button, the following screen will be opened for adding the scripts.


2. Import data from external files

Task: import 2 fields from the csv file into the user defined fields (Tracking no and Freight Amount)

1)Add an external link button at Sales order total screen
2) Define the variables – Sales order no, User defined Tracking # (return value) and Freight Amount (Return value)
3) The scripts are below:

Dim order

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
Dim objPath
Dim objWkbk
objPath="G:\Program Files\Sage\Sage 100 Advanced ERP\TrackingData"

Dim TrackingNO, freightamount  
TrackingNO = ""
freightamount = 0.00

filename = objPath & "\" & "TrackingFileXXXX.csv"
Set objWkbk=objExcel.Workbooks.Open(filename)

Dim i
Do While (not objwkbk is nothing) and (i<1000)    
if objWkbk.ActiveSheet.cells(i,1)="" then      
Exit Do     
End if     
if InStr(objWkbk.ActiveSheet.cells(i,1), order) then        
TrackingNO = Cstr(objWkbk.ActiveSheet.cells(i,2).value)               freightamount=objWkbk.ActiveSheet.cells(i,4).value       
Exit Do

End if
set objWkbk = Nothing

if TrackingNO ="" then      
Msgbox ("Not find order # " & order & " at " & "Purolator's csv files" )
SO_Invoice_bus_UDF_ACTFREIGHT = freightamount     
SO_Invoice_bus_UDF_TRACKINGNO= TrackingNO
end if  

objExcel.DisplayAlerts = False
set objExecl = Nothing

3. Export to office template files (excel, word)

Task: export Sales order ship to information
1) Add an external link button to the screen at Sales Order Header
2) Setup variables including Sales order no, customer no, and ship to fields
3) The scripts is below

Set objFSO = CreateObject("Scripting.FileSystemObject")
dataFile = "G:\Program Files\Sage\Sage 100 Advanced ERP\ShipToAddress\"
& SO_SalesOrder_bus_SalesOrderNo & ".csv"
Set objFile = objFSO.CreateTextFile(dataFile)
objFile.Writeline("I" & "," &_
SO_SalesOrder_bus_SalesOrderNo & "," & _
SO_SalesOrder_bus_CustomerNo & "," & _
SO_SalesOrder_bus_ShipToName & "," & _
SO_SalesOrder_bus_ShipToAddress1 & "," & _
SO_SalesOrder_bus_ShipToAddress2 & "," & _
SO_SalesOrder_bus_ShipToAddress3 & "," & _
SO_SalesOrder_bus_ShipToCity & "," & _
SO_SalesOrder_bus_ShipToState & "," & _
SO_SalesOrder_bus_ShipToZipCode & "," & _


4. Print document directly to printer

Task: print work document to the printer
1) Add an external button to the screen
2) The script is below for reference.
Set objWord = CreateObject("Word.Application")
objWord.Caption = "Test"
objWord.Visible = False

Set objDoc = objWord.Documents.Open("c:\test.doc")

'now print to default printer


'close word application
objWord.Quit 0

5. Open PDF file to the screen

Task: open the existing invoice PDF files which was saved by paperless office

1) Add an external button to the screen

2) The script is below for reference.

Dim wsh
Pdfpath = “C:\”
Pdffile = AP_InvoiceHistory_bus_InvoiceNo & “.pdf”
Set wsh = CreatObject(“WScript.Shell”)
retVal = wsh.Run(Pdfpath & PDFFile, 1, False)
Set wsh = Nothing

6. Search information from database 

Task: retrieve customer information from sales order

1) Add an external link button at the Sales order header screen

2) Setup the variables including sales order no, customer no, AR division no, and additional customer fields (Customer status, Last Payment Date, Last Payment Amount)

3) The scripts are below for reference

Dim sConn
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "MSDASQL"
oConn.Open sConn

sSQL = "Select top 1 CustomerNo, CustomerStatus, DateLastPayment,
LastPaymentAmt "
sSQL = sSQL & " From AR_Customer  "
sSQL = sSQL & " Where CustomerNo = '" &
SO_SalesOrder_bus_CustomerNo & "' and  ARDivisionNo ='" & SO_SalesOrder_bus_ARDivisionNo & "'" 

Set rsItem = oConn.Execute(sSQL)
if rsitem.EOF then        
msgbox “no customer information!”
SO_SalesOrder_bus_UDF_CustomerStatus = rsItem.Fields(2)       SO_SalesOrder_bus_UDF_DateLastPayment = rsItem.Fields(3)       SO_SalesOrder_bus_UDF_LastPaymentAmt = rsItem.Fields(4)
end if

Set rsItem=Nothing
Set oConn=Nothing

A second way to launch the script is to attach the scripts to the events of the table / fields update. These VBscripts are powerful in handling some special business processes which may not need the user to be involved. The details will be discussed in my next blog.

As a trusted technology partner, Mantralogix delivers enterprise software solutions to both Canadian and U.S. businesses. We are happy to receive your inquires for any types of services which you are looking for and will focus on ensuring your requirements are met. Please call us at 1-866-320-8922 or email us at

About Mantralogix

Mantralogix specializes in the implementation, support, and training of world-class ERP solutions from Sage, including Sage 100 ERP, Sage 500 ERP, Sage 300 ERP (formerly Accpac), and Sage ERP X3, Sage PFW, and Sage BusinessVision Accounting (BV).  We also help with CRM, HRMS, custom development, hardware, and IT consulting and outsourcing. Mantralogix has in-house expertise in designing, developing and implementing solutions for financial, manufacturing and distribution verticals.  Contact us today at 866-320-8922 or visit

Another version of this article was previously posted on Matralogix’s Blog on January 20, 2015:

ERP Implementation Partner Selection Tips and eBook

ERP Implementation Partner Selection Tips and eBook

How to Find the Right ERP Implementation Partner Working with the right ERP implementation partner will be critical to the success of your ERP...

Read More
Are You Outgrowing Your ERP Manufacturing Software?

Are You Outgrowing Your ERP Manufacturing Software?

3 Tips for Finding the Best ERP Manufacturing Software for Your Business If your manufacturing company is outgrowing your software the search for...

Read More
5 Ways a New Manufacturing ERP Helps Your Business Grow

5 Ways a New Manufacturing ERP Helps Your Business Grow

Grow Your Business with a New Manufacturing ERP Solution The economy is constantly changing, and manufacturing companies have been hit hard by recent...

Read More