Using Scripts to Customize Sage 100
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.
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.
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.
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:
|
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
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") |
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 |
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
sSQL = "Select top 1 CustomerNo, CustomerStatus, DateLastPayment, Set rsItem = oConn.Execute(sSQL) rsItem.close |
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.