ERP Consultant Blog

Sage 100 ERP: How to Streamline Processes with Business Intelligence Tools

Written by Paul Cusano | Tue, Jun 25, 2013

Written by Paul Cusano, Sage 100 ERP Consultant Rochester, NY

In today’s work environment it is critical to automate business processes. The benefits are endless but focus primarily on accurate information without the intervention of human error and profitability through the savings of labor hours. What was not possible 20 year ago has become not only the norm but essential for businesses to compete in our complex marketplace. In addition due to the tools at our disposal we no longer expect mediocrity but a high place in whatever industry we compete. How is this possible? Through the many business intelligence tools at our disposal.

One of those tools is the Visual Integrator module that is part of the Sage 100 ERP system.
It has been redesigned and retooled to accomplish integration with many other software systems, both automated and manual.

Businesses that have weekly price changes are a good example to consider in showing how business intelligence tools like Sage 100 ERP Visual Integrator save labor time and reduce errors.  This could be retail businesses or any business that deals in any kind of commodity.

So how would this actually work?

Let’s say that a person in the company maintains a weekly excel spreadsheet that customers listed by category and item. For each customer the price of each item can be affected not only by the price of the item, but the discounts or price breaks for that customer.

Here is how it might work using Sage Software's Sage 100 ERP:


STEP 1 – Creating and Saving the Weekly Pricing File

  1. On your Sage 100 ERP desktop OPEN UP last week’s pricing file in the folder 
  2. Make changes, deletions & edits for the new week.
  3. When all the changes have been made, save the file to a specific location

NOTE: It will display the “file already exists, do you want to replace. Click “Yes”

STEP 2 – Creating the Source file using ACCESS
Note: Access is used to create the automation to replace the old pricing column with the new one

  1. Open up the Access Program 
  2. When the “Output To” file display click “OK”
  3. Say YES to replacing the file.
  4. Wait a few seconds and an Excel file will open. 
    • Once opened go to “File/Save As” and save the file as a CSV file to a specific location
  5. Say YES to replacing the file.
  6. Say Yes to when this screen displays. 
  7. Close out of the Excel by clicking on the upper right hand “X”
  8. Say No to the message “Do you want to save changes”


STEP 3 – Importing pricing into Sage 100 ERP
NOTE: It is important to be in the correct company and to select the correct job!!

  1. In Sage 100 ERP log into the correct company
  2. Go to the Job Select menu located in the Visual Integrator module at: Visual Integrator/Main/Job Select and select the VI import job 
  3. Click ACCEPT and a bar will display indicating the records are being imported:
  4. A screen will display indicating a summary of the import.
It will display Records Read, Records Imported, Records Skipped, Records Failed and Records Imported with Invalid Fields

If Records Failed and Records Imported with Invalid Fields is “0” or “1” then click “No” to view the job log


If either of those are more than “1” then click “Yes” to view the job log

Any records that FAIL will be highlighted in RED
Any records that are INVALID will be highlighted in BLUE
To find out why the record either FAILED or is INVALID double click on the line
The data that failed will be in RED

This is just one small example of how automation can save hours each week, not to mention creating accurate and reliable data for salespeople. Using Visual Integrator to integrate with other software will be in our next discussion.

Blog contribution by Source One Systems

Source One Systems has specialized in provided business technology solutions for over 20 years. We have intentionally chosen to specialize in Sage Software’s Sage 100 ERP, formerly MAS 90/200 product family. Our focus has always been to provide a complete solution to meet the business management needs of our clients. We are driven by the professionalism of the CPA profession and have combined it with the relationships and detailed understanding of our client’s management processes at all levels of the sales and purchase cycles. Trust and accountability is at the core of all installations. With over 25 years combined experience using the most cutting edge technology the support and service we provide is unparalleled. Our focus is gaining an understanding our clients needs, not our own. We will not sacrifice our client’s needs at the expense of profitability or responsiveness. We believe you will not be purchasing an accounting software solution but a relationship that will be crucial in implementing whatever solution you choose.

Contact us today!

SourceOne Systems Serving all of New York.

Phone: 315-437-1130