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

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 info@mantralogix.com.

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 www.mantralogix.com

Another version of this article was previously posted on Matralogix’s Blog on January 20, 2015:  http://insights.mantralogix.com/blog/using-scripts-to-customize-sage-100-erp