Home › Blog › Using Scripts to Customize Sage 100

Using Scripts to Customize Sage 100

Jan 20 2015
POSTED IN Industry News & Events

Sage 100 (Sage MAS 90 / MAS 200) is an exclusive ERP solution, which provides businesses with management capabilities and a low cost of entry.  It offers an integrated suite of feature-rich modules with the additional option of using scripts to add even more value.

Sage 100 not only provides normal finance accounting, logistics, manufacturing, and payroll modules, but also offers useful tools and functions, such as paperless office, visual process flows, Sage Intelligence Reporting, customer relationship management, and eBusiness Manager. Many 3rd party solutions enrich the capabilities of Sage 100, like EDI seamless integration, multicurrency, warehouse management, shipping automation, invoice automation, CRM integration, etc.

In addition to delivering unparalleled flexibility, ease-of-use and breadth of functionality, Sage 100 is easily customized, quickly implemented and cost-effectively supported. Unlike other ERP software which needs certified programmers to change the source code for some customizations, Sage 100 provides tools called customer office and customizer which provide the GUI (user friendly interface) for end-users to do the customizations themselves. Please refer to our previous blog Sage 100 Customizations for Sales which discusses the customization process for creating user defined fields by using the customer 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.

1-7

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.

2-5

On the left Customizer toolbar, you can select ‘Add External Link’ and drag your mouse with a  box for the new button that shows up on the on the right panel of the screen (refer to the red box above). A screen will pop up as below.

3-4

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 #’

Untitled-3

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

5-3

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

6-2

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
order=SO_Invoice_bus_SalesOrderNo

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
i=0
Do While (not objwkbk is nothing) and (i<1000)    
i=i+1    
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
Loop 

objWkbk.close
set objWkbk = Nothing

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

objExcel.DisplayAlerts = False
objExcel.Quit
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 & "," & _
SO_SalesOrder_bus_CountryCode) 

objFile.Close

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
objDoc.PrintOut()

MsgBox("Finished!")

'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
Dim rsItem sConn = "DSN=SOTAMAS90;UID=TEST;COMPANY=" & MAS_SCR_CMP & ";PWD=TEST"
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!”
else        
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

rsItem.close
oConn.close   
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.

Recent posts

Aug 04 2020

Digitization of the Construction Industry

In a recent article, MNP’s Kerry Mann, National Leader – Enterprise Resource Planning, discussed the costs, risks and challenges of...
— Continue Reading
May 26 2020

Cutting through the misconceptions

What are the hallmarks of a true cloud solution? Industry research indicates roughly 69 percent of organizations are moving business-critical applications...
— Continue Reading
Comfortable working place. Close-up of comfortable working place in office with wooden table and laptop laying on it
May 26 2020

How cloud computing can help plug a slow profit leak

When it comes to legacy technology, doing nothing has a cost Do you have aging technology that’s difficult and costly...
— Continue Reading
Comfortable working place. Close-up of comfortable working place in office with wooden table and laptop laying on it

Categories