Sage 100 Consultant Tip - How to Use Customizer and Crystal Reports to Add a Field and Run Reports
Sage 100 Consultant Tip - Add a Custom User Define Field (UDF) to a Sage 100 Panel with Customizer and Report on it with Crystal Reports Question: ...
5 min read
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
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
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.
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
Using Sage ERP X3 to Reduce Inventory Carrying Costs
Sage ERP X3 Helps Reduce Inventory Carrying Costs Accurate material planning is one of the biggest challenges in the Distribution and Manufacturing...
Can’t Live Without Excel? Sage ERP Software and Excel Work Together!
Are you constantly updating Excel spreadsheets, use multiple financial systems or are you doing a lot of manual entries Leveraging an enterprise...