DBSync's "Sage 100 & PocketAdvantag Bidirectional Integration" is easy to use solution template which integrates data both ways between the Sage 100 & PocketAdvantage app. The template is also useful in migrating historical Sage 100 data into PA and can also be used to do a periodic swipe of historical accounting data into PA. This template provides Bi-directional the flexibility for more complex and dynamic mapping capability.
Data flow of Sage 100 and PocketAdvantage application is shown in the table below.
The Process map of Bi-Directional Integration - between Sage 100 application and PocketAdvantage application - is shown in the image below.
# Creating ODBC data source
- Importing a Job File in Sage 100
- Scheduling a Job in Sage 100
One of the most important prerequisites of Sage 100 and PocketAdvantage system is to set up the ODBC data source connection on the server where Sage 100 is hosted. The below steps explain how to set up the ODBC connection so that Sage 100 opens a connection to DBSync.
- Go to Control Panel -> Administrative services of the server on which the Sage 100 is hosted.
- Select the service ODBC data source 32-bit/64-bit based on server configuration. This will open a pop up as shown in the image below.
- Choose the Tab user DSN and click on the Add button. This will open a pop up with various drivers.
- Choose MySQLODBC5.3 Unicode driver. This will pop open a window to enter connection properties.
- In the MySQL connection window, 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.
The steps below show how to import the job file script provided by DBSync - it is mandatory for the integration to work.
- Login to Sage 100 with valid credentials.
- On the left navigation, select a visual integrator. This will provide a list of options.
- Choose the "Job Import" option from the available values.
- Choose the file provided by DBSync. The file can be downloaded by clicking on the following link.
- From the file dialog box, choose the file. It will show the list of all the jobs that are required for DBSync to perform the integration.
- Select all. And then, click on the Accept button as shown in the following screenshot.
Scheduling all the necessary jobs, from Task Scheduler, will facilitate the data to be migrated from Sage 100 to DBSync. DBSync, in turn, will process 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. It will return the search results.
- Clicking on the search result name of Task Scheduler will open a popup - as shown in the image below.
- Key in the desired job name. Moving the cursor to next text box will auto-populate the company code, Sage 100 user login, and server user logon.
- The user will have to key in the Sage 100 Account Password and Server Password. Then, s/he will have to choose the type to "Visual Integrator ".
- Set the schedule job intervals by choosing the appropriate options.
- 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. Then, choose the V/I Job Name as appropriate.
- Click on Accept to save the job.
A question may arise - what happens to the Sage data after it is read from the Sage 100 application? How does DBSync process the data and write to the PocketAdvantage? Below steps explain how Sage Data is being processed to the PocketAdvantage.
- The Scheduled jobs within the Sage 100 application, helps to read from Sage and processes to DBSync.
- The DBSync holds the Sage data in a queue - i.e. a buffer - before applying the required transformations and writing to the PocketAdvantage.
- The workflows - with the naming convention "Merge" within the DBSync - will help DBSync to sort any duplicate records that are processed into the DBSync's buffer.
- DBSync internally uses a chaining technique for the merging process. The technic enables DBSync to identify unique records and separate duplicate records from the unique ones.
- Then, another workflow will process the data from DBSync buffer to PocketAdvantage.
DBSync Integration of Sage & PocketAdvantage can be triggered by:
- Clicking on the Run-now button from the process listing page of DBSync.
- Using DBSync Scheduler.
Clicking on Run-Now Buttons From DBSync platform
The user can log in to DBSync with his/her valid credentials and click on the Run-now button from the process listing page or from the workflow listing page . The order of execution, of the process, must follow the following path:
Using DBSync Scheduler
- Scheduler available in DBSync Account can be used to schedule the integration. For further details on the scheduler, please click on the following link http://help.mydbsync.com/docs/display/dbsync/Scheduler.