Sage 300 Newsletter – October 2016
Keeping You Up-To-Date With Information About Sage 300
Sage 300 Integration Forms and Source Interfaces
Sage 300, with its strong accounting and financing features, mainly serves and helps small to medium businesses run their daily tasks. At the same time, Sage 300 is also a well-structured 3 tier application, which allows integrations and customizations to help businesses to do more.
System Integration in essence is all about pulling and pushing data between systems. A system integration process sits in between 2 or more systems, retrieves data from a source system (or multiple systems) and pushes data to a destination system (or multiple systems), sometimes with data translation.
A lot of times, when we talk about Sage 300 integrations, we already tailor the general system integration practice used in IT industry for Sage 300 product, so we can implement the integration process in a timely manner.
Integration Application Forms:
Sage 300 system integration process can be in a form of a desktop application for users to use, or can be in a form of an automated service application that runs in the background without users’ involvement.
These 2 forms are the common integration projects we implement to our client base in recent years, since they provide more data accuracy between systems and users convenience. More importantly, they save our clients’ data processing time and reduce data error to a great extent, with less or no users’ involvement in data processing and handling.
Below screenshot is an example of a custom developed desktop application that imports from a CSV file to Sage 300. It creates GL transactions and AP invoice transactions in Sage 300 from 1 screen with a few button clicks, which is easy and convenient for system users.
An automated service application runs on a computer where Sage 300 is installed and performs the import of Sage 300 transactions from a data source. The data source can be a database, or data files, or web services that provides data. Since it is automatically run and users do not need to do the actual export and import tasks. The service application does all the work for the users. We have developed and implemented service applications that automatically creates all Sage 300 transactions, including sales orders, purchase orders, AR invoices, etc.
Examples of service application:
However, a lot of times, due to system limitation, we notice that many integration processes are related to export-import processes. In the source system, specific business data is entered, processed and saved to some kind of data repository, e.g. SQL database, ORACLE database, FoxPro database. Data files are created and exported in formats like Excel, XML, CSV or Text files in source system, using Sage 300 accepted structure. Sometimes system users actually create and\or manipulate those files so the files are in Sage 300 accepted structure.
Then when it is time to import to Sage 300 to generate Sage 300 transactions, the users use the Sage 300 native import and export feature to run the import. This type of integration process is dependent totally on the data files, which are normally created by an export function of the source system. In a word, it is relying on a program. Until a file is created successfully in Sage 300 accepted structure and without data error, the import part of the integration will not work. The users need to work on multiple files and multiple Sage 300 screens to complete the tasks, E.g. GL import has to run in GL entry screen, AP invoice import has to run in AP invoice entry screen.
Integration Source Interfaces:
We can always use existing export files from the source system for integration use and create Sage 300 transactions from there. Such export files should contain data in a structured format (CSV, XML, Excel, or Text file of certain delimiter) and contain the data needed for import use. An integration application then reads from the file and extracts the field data per file definition. The files do not need to be in Sage 300 structure, since a custom application can be developed for integration purpose.
Below is the screenshot of a CSV file:
Evidently, when we work on an integration project for Sage 300, we need to extend our input interface requirements from the source systems to more than only data files.
A more direct way to get the data from data repository is more efficient in system process, without dependencies from an export program or human involved process. One of the very common data interfaces is database interface. The idea is to pull data directly from source systems’ database, since database schema and source data structure do not change very often as data repository.
Below are the options we consider when we architecture data pulling directly from source database:
- Create SQL views from source system, if source database is SQL. Linked Server of SQL can be implemented, if Sage 300 SQL server is not the same as the source system’s database
- ODBC connection to pull from source database
- Define and develop an integration table per integration needs in source database, when source database does not allow query many tables to collect data
Below screenshot is a database interface example. Some fields (in green box) are from Sage 300 and other fields (in red box) are from client’s specific system data. Now we have a flat table with business data to work with for client’s business needs, fairly simple to create a report, or store data, or exchange data.
Opportunity for our clients and prospects:
If you are still manually running file export and import to bring the data in Sage 300, there might be a good chance to simplify and improve your data processing work, by using a desk top application with less button clicks.
If you need to import a lot of transactions or create multiple different types of transactions in Sage 300 with no user’s involvement, an automated service application is preferred.
If you would like to integrate a business system with Sage 300 and are not sure if the export files are capable, most likely we will be able to integrate from a database interface level.
If you need to identify if a specific business process can be integrated with your current Sage 300, our Net at Work Sage 300 team can help identify a solution.