Use cases and challenges when building an integrated accounting system – SAGE 100 and 300.

We are currently living in a “data revolution” where just about any desired data point can be quantified. For Accounting and ERP management, the question becomes: how useful is such an opportunity if data is inaccessible and/or spread across multiple platforms? In this blog, I will use a storyboard approach to summarize my experiences and demonstrate the challenges I faced integrating SAGE 100 and 300 systems.

Integration is Important

Integrated accounting and financial systems enable businesses to rely on real-time information about business transactions. For example, the operator of a small hotel business can remotely track the number of vacant rooms, occupied rooms, and new reservations; simultaneously track gift shop sales and latest inventory records; access real-time reports of the day’s operations and analyze the impact of transactions on the general ledger.

One-Stop Shop

An integrated accounting system serves as a “one-stop-shop” for all accounting information, including financial, managerial and cash-flow accounting. As such, when using an integrated accounting system, maintaining separate accounting procedures for preparing financial, management and cash-flow reports is not necessary.

Real-Time Information

Stand-alone accounting is never in “real-time”, as it requires someone to manually take information from the ordering system and re-enter it in the accounting system. This means that financial information required for strong decision making is not up to date. Data re-entry further invites human error, leading to a greater chance of making decisions based on faulty information.

Integrated accounting provides a complete real-time picture of the business at any time, as information automatically flows into the accounting system as orders are processed. A web-based business management system with integrated accounting allows for complete and accurate financials that can easily be transported.

Integration Process

DBSync seamlessly integrates various applications with Sage 100 and 300 ERP to “push and pull” data including product details, inventory levels, payment and shipping, customer information, sales orders, and status updates. The information exchanged is securely and automatically synchronized between Sage 300 ERP and any other target system.

SAGE 100 INTEGRATION:  

It is important to note that both DBSync Cloud agent or OnPrem service can be used to integrate with SAGE. A user is able to install any ODBC driver on his/her local server (preferably a 32-bit driver). An attempt should be made to supply values for the driver using the available fields as shown in the image above. If it is discovered that a “Test Connection” is unsuccessful then it may be necessary to create a manual connection string that is specific to the newly installed ODBC. Once the ODBC connected to DBSync staging server is established, we make use of SAGE Visual Integrator to make a connection with our staging server through the ODBC bridge.

A typical Visual Integrator (VI) import job making a connection to our ODBC to import Sales Orders into SAGE is illustrated below:

• Once you are at the SAGE homepage, navigate to Visual Integrator and expand Import Job Maintenance or Export Job Maintenance (depending on if you are bringing data into SAGE or exporting data from SAGE).

• The No.2 in the above screenshot illustrates a typical SAGE sales order mapping through a VI job with DBSync staging ODBC server to bring data sales orders into the SAGE system.

SAGE 300 INTEGRATION:

There are two ways to integrate with SAGE 300 using DBSync. One option is to go via the SQL Server connector route, which uses the DBSync database adapter. The other way is through DBSync Swagger adapter. The Swagger adapter is the preferred route as SAGE 300 provides integrator through Swagger UI. The Sage 300 Web API makes it easy to develop services that integrate with Sage 300 data and business logic. The Web API can be used to create integrated services for Sage 300 using many frameworks and tools—including just a web browser. Swagger UI generates visual and dynamic documentation of the Sage 300 Web API endpoints and their models. It can be used to support verbs for endpoint paths. The defaulted company for these operations is SAMLTD. To access the Swagger JSON document and Swagger UI page, navigate to <<protocol>>://<<servername/Sage300WebApi>>, where the protocol is HTTP or HTTPS and server name is the name of your Sage 300 server (for example, https://localhost/Sage300WebApi). Swagger is part of the Open API Initiative (OAI). For more information on open API/Swagger and Swagger UI, please see:

where the protocol is HTTP or HTTPS and server name is the name of your Sage 300 server (for example, https://localhost/Sage300WebApi). Swagger is part of the Open API Initiative (OAI). For more information on open API/Swagger and Swagger UI, please see:

• http://swagger.io
• https://github.com/OAI/OpenAPI-Specification

Relevant Use-Cases 

Over a period of time, many use-cases and data-flows have leveraged DBsync iPaaS.  The most prominent and relevant SAGE integrations asked for and in play, are:

Order to Cash:

The most typical use-case or automation that is seen with any CRM & Accounting integration is Order-To-Cash or Quote-To-Cash data-flow. Here, CRM systems like Salesforce or Microsoft Dynamics 365/CRM typically become the system of record or a master database for all sales and marketing processes that drives the integration for all customer and order information. Once the orders are placed in the CRM and an Opportunity is “Closed Won”, the sale is accounted for by integrating the Opportunity/Order and all related information (Customer, Customer’s BillTo and ShipTo, Product into SAGE receivables). The integration also includes the creation of Customer, Product attributes since these records do not pre-exist. This prevents double data entry of Customer and Product attributes. Lastly, the ‘transaction insert’ operation is executed, constituting of Quotes, Sales Order, Invoices and Sales Receipts. The relevant transaction data flow can be activated within DBSync depending on the requirements or business use case.

A typical CRM Object to SAGE Entity data flow mapping is illustrated below:

Inventory Management & Movement

Inventory Management, Movement and Tracking is one of the most complex and difficult automation in the integration business. It can be the Achilles heel for lots of business who have inventories in multiple locations. As SOs and POs are generated, it becomes paramount to track inventory items’ movements and stock-on-hand updates. Typically in these scenarios, the SAGE or Accounting presides over the other system and more often than not becomes the master database. The incremental updates that are taking place in SAGE need to be reflected in your CRM so the end-user/rep knows stock availability before an order is placed. This particular use-case is very relevant today, as reps/service technicians are in the field with hand-held devices and require continuous real-time integration. Automation has increased stock visibility across the board to our customers, and in some cases has not only has reduced operational hazards but has also reduced billing cycles from several weeks to a matter of days. 

A typical mapping between CRM and SAGE tracking inventory can be seen below:

Other notable automation/integrations with SAGE are AP Automation, Project Management, and Payroll Integrations. Please continue to stay connected for more insightful and detailed discussions on some of the more popular integrations that can be done with SAGE 100 or SAGE 300 systems.