A customer recently brought us a requirement to create a two-way integration between GP and a third party POS system. This integration needed to include Customer Master Records, Vendor Master Records, Address records, Items, Sales Invoices, Sales Payments, POP receipts and IV Adjustments. To make it more complex, the two systems needed to be in control of certain types of records. The POS system, for instance needed to be the “master” when it came to Customers, but GP needed to be the “master” for vendors. This integration also needed to be near real-time.
My first thought for this Integration was Dynamics GP Web Services and / or eConnect. These tools provide a tight integration with GP and satisfy the near real-time requirement. GP Web Services needs to be called using an ASP.Net application like a web site or Window’s form application and is event based, like any other ASP.Net app, so something needs to occur in the app before web services code can be called. The POS system was not written in .NET and the only way to get information in and out was through direct SQL statements or DTS package exports. The DTS package, while old school, caught my attention for a moment because this lead me towards using Integration manager. IM is a manual process that will integrate data from a text or Excel files into GP. This, however, did not satisfy the near real-time requirement so it was quickly scrapped.
I needed to come up with a .NET application that runs on some kind of a timer to call the GP Web Services. Sounds like a great scenario for a Windows Service Application. There is a template in Visual Studio that creates an installable Window’s Service based on .NET and this sounded perfect, so I decided to test it out. I created a holding table in SQL that is populated by SQL triggers when a document or master record is create or updated in GP. I then used a .NET timer control to trigger the GP Web Services. This will, in essence, act as a polling application. The code on the timer will check the holding table for any new records then query the tables for the type of record in the holding table and use GP Web Services to bring in or query GP documents to move them into the POS system.
An added bonus feature is that the application can write events to the Window’s event log. Successful and, more importantly, error messages are recorded and easily queried just like Window’s events. An e-mail feature was also added to warn certain users when certain types of records were integrated. Another great feature is that the Window’s Service can use an .xml config file to hold settings. This allows the ODBC connection settings to the third party app, amongst other types of settings, to be stored outside of the source code which makes this application easily modifiable for different client requirements. While this solution is not yet in production, initial testing looks terrific and seems to be a great fit for these requirements.
This is just another custom solution from Gilchrist Scott Development. If you are looking to integrate another application with GP and do not want to bother with manual processes such as Integration Manager, then a Windows Service Application from GSL may be for you. Please contact us at 207-761-2133 for more information.
