DBSync for Sage 100 & PocketAdvantage is an easy to use Integration Template to integrate Sage Customer, Item, Vendor, SalesRep, Invoice with PA Customer, Item, Sales Rep and Transactions respectively is one way and other way is Orders of Pocket Advantage getting integrated to Sales-orders in Sage 100. This template is also useful in migrating historical Sage 100 data into PA and vice versa. This template provides Bi-directional sync with pre-built field to field map along with the flexibility for more complex and dynamic mapping capability.
Data flow of Sage 100 and PocketAdvantage application is shown in the below table .
![](/docs/download/attachments/11698391/DataFlow_Sage.png?version=1&modificationDate=1461766404000&api=v2)
The Process map of Bi-Directional Integration between Sage 100 application and PocketAdvantage application is shown in the below image.
![](/docs/download/attachments/11698391/ProcessMap_Sage.png?version=1&modificationDate=1461766833000&api=v2)
Pre-Requisites for Integration
# Creating ODBC data source
- Importing a Job File in Sage 100
- Scheduling a Job in Sage 100
Setting up ODBC DataSource
One of the most important prerequisite of Sage 100 and PocketAdvantage system is to setup the ODBC data source connection on the server where Sage 100 is hosted . The below steps explain how to setup the ODBC connection so that Sage 100 opens a connection to DBSync .
- Go to Control Panel -> Administrative services of the server on which Sage 100 is hosted
- Select the service ODBC data source 32-bit/64-bit basing on server configuration, which then opens a pop up as shown in below image .
- Choose the Tab user DSN and click on Add button which opens a pop up with various drivers
- Choose MySQLODBC5.3 unicode driver which pops out a windows to enter connection properties
- In MySQL connection windows enter the following Parameters Data Source Name: dbsyncqueue , TCP/IP server : dbsync-queue.cvglyvvn64dx.us-east-1.rds.amazonaws.com, user:dbsync, Password:, Database:Sage
![](/docs/download/attachments/11698391/ODBCBridge.png?version=1&modificationDate=1462279151000&api=v2)
The below are the steps to import job file script provided by DBSync which is mandatory for the integration to take place .
- Login to Sage 100 with valid credentials
- On the left navigation , select visual integrator which gives the list of options
- Now choose "Job Import" option from the available values.
- Choose the file provided by DBSync which can be downloaded from this link.
- From the file dialog box , choose this file which will show the list of all the job which are required for DBSync to perform integration
- Select all and click Accept button as shown in the below screen shot.
![](/docs/download/attachments/11698391/jobimport.png?version=1&modificationDate=1462279029000&api=v2)
Scheduling all the necessary jobs from Task Scheduler will facilitate the data to be migrated from Sage 100 to DBSync so that the DBSync in turn processes the data to PocketAdvantage. Here are the steps to schedule a job so that they get scheduled at the set time intervals
- Login to Sage 100 with valid user credentials
- Search for the Task Scheduler from the search which returns the search result
- Clicking on the search result name of Task Scheduler will open out a popup which is shown in the below image
- Key in the desired job name , move the cursor to next text box will auto populate the company code, Sage 100 user logon, server user logon.
- The user will have to key in the Sage 100 Account password and Server password and choose the type to "Visual Integrator ".
- Set the schedule job intervals by choosing the appropriate options .
- Now , choose the tab "Tasks".
- Select the V/I Job Type to Export (in case of reading data from Sage 100 or choose Import in case of writing to Sage 100) and choose the V/I Job Name to appropriate
- Click on Accept so that a job is saved.
![](/docs/download/attachments/11698391/TaskScheduler.png?version=1&modificationDate=1461852013000&api=v2)
Post Process Of Sage Data
So here the question arises what happens to the Sage data after being read from the Sage 100 application, how does DBSync process the data and writes to the PocketAdvantage .Below are the few steps which explain the process of Sage Data being processed to the PocketAdvantage.
- The Scheduled jobs with in Sage 100 application helps to read from Sage and process to the DBSync
- The DBSync holds the Sage data in the queue i.e. buffer for applying the required transformations and writing to the PocketAdvantage
- The workflows with the naming convention "Merge" with in the DBSync will help DBSync to sort any duplicate records which are processed into DBSync buffer.
- DBSync internally uses chaining technique for the merging process so that DBSync will identify the unique records and separate the duplicate records from the unique ones.
- Then the other workflows created will process the data from DBSync buffer to writing to PocketAdvantage.
DBSync Integration of Sage & PocketAdvantage can be triggered by
- Using Run-now button from process listing page of DBSync.
- Using DBSync Scheduler .
Using Run-Now Buttons From DBSync platform
The user can login to DBSync with his valid credentials and hit the Run-now button from the process listing page or from the workflow listing page . The order of execution of the process has to follow the below order
- SageToQueue
- CustomerToFTP
- ItemsToFTP
- TransactToFTP
Using DBSync Scheduler