Ecommerce Connection to Dynamics GP via Integration Manager

There are numerous “turn-key” products, available from ISV partner channel, they are wonderful and do the job.  However, there might be the underwater rocks in taking that direction.  For example, “turnkey” solution may require you to redeploy your existing ecommerce website on the new product platform, while you really want to keep complete control in ecommerce programming by keeping your custom application in production, and rather implement just connection.  Products might give you advantage to send shopping cart immediately upon the required conditions (customer credit card payment, for example), but there might be really little value for your business and your customers to see transaction in real time in GP, they might be OK to wait ten minutes or so (where from your ecommerce web application you may trigger immediate email with “thank you for your business” and details of the transaction, but the shopping cart itself will appear in Dynamics with the next scheduled integration run, which might be every five, ten or even twenty minutes).  Let’s review the scenarios and technology, where you are building integration in the Integration Manager module:

1. Where ecommerce shopping cart goes?  There are two most popular destinations in your GP.  First one is Sales Order Processing Order (where you may decide to do order fulfillment on the warehouse floor via WMS/Barcode scanning) or Invoice.  Second destination is RM Invoice, where you have only one flat record per ecommerce shopping cart.  Both methods allow you to have customer deposit (if you are electronic commerce merchant in B2C scenario) or just sell on account (in B2B model, where you bill your customers later: popular in wholesale, franchising, dealership and distribution)

2. What do I have to do in Integration Manager?  Several things.  First, you have to establish connection between your ecommerce application and GP Integration Manager (usually network connection between your ecommerce server, where you may be on Linux/PHP/MySQL, Oracle or other DB and Operating System platform).  The simplest way is to export newly received Shopping Carts to text file and then process them in IM and place into Processed, Exceptions folders.  The second possibility is to open access to ecommerce server from IM hosting computer via ODBC.  Second, you create the Queries and their Relations to imitate SOP document header, lines (plus in some exotic cases GL distributions, but usually you are using distribution logic incorporate in GP settings).  Next step, you select the destination to be SOP Transaction (or RM Invoice, this way is simple one, but it is restricted to one line per SC, so we’ll give you the recommendations below).  The next step – you will map your Queries fields to SOP Header, Lines (and Distributions) counterparts and test the integration against the test company (usually GP company, specially created for testing and QA and then restored from production company backup).  And the last step is to schedule IM integration.  You may actually decide (especially when you are a smaller company), that scheduling is not really required, and you can have one of the Great Plains users to run integration manually in the morning.  But if quasi real time integration is required, consider reviewing various Windows scheduling shareware or freeware applications with Windows scheduled job (more advanced module is available to schedule IM Integration directly from Microsoft Business Solutions)

3. If your integration logic requires customization.  In Integration Manager you can use such methods as VBA event driven scripts to alter the integration logic on the Integration, Document and Even Line level.  Plus you can use such techniques as Translations (if the field gets the value ‘John’, you make integration manager to translate it automatically to ‘John Bull Wholesalers’ to give you an idea).  Translation logic could be loaded directly from MS Excel table, so it is user friendly and simple enough

4. Integration Manager Connection Technologies.  Traditionally it was Microsoft OLE Server, where you have to consume one GP user license, as the client workstation was required to be running to turn itself into OLE Server.  In this scenario, the integration itself imitates clicking and filling the required and possibly additional fields on the OLE Exposed SOP Transaction form.  OLE potential performance is obviously slow, comparing to such technologies as SQL Stored Procedure.  And this is where we have to mention the new connection technology eConnect, which is coded in encrypted SQL Stored Procedures.  In our opinion eConnect came to its maturity in version 10.0, so if you are on earlier build – consider upgrade to at least 10.0 or better to current version 2010/11.0 (we are writing these lines in January 2011).  eConnect connection doesn’t require additional user license and works theoretically a way faster (practical tests typically prove the dramatic performance improvement, with some cautions on deploying IM with eConnect in SQL cross-platform queries, where ODBC bridge might be a performance bottleneck)

5. How about older versions of Great Plains Dynamics and eEnterprise?  In fact, if you are on such archaic versions as 8.0, 7.5 (including non-Microsoft SQL Server platforms – Pervasive Software 2000/Btrieve or Ctree) and earlier (7.0, 6.0, 5.5, 5.0, 4.0) – and when you have to stick to the old version – Integration Manager might be the only choice (until you would like to program the integration in Dexterity Sanscript – this is definitely more expensive approach, and it is not advised, consider to upgrade to the modern version).  Integration Manager was available since the earlier versions of Great Plains Dynamics, where the integration concept didn’t change – if you decide to communicate with your ecommerce website via text files, this way is version neutral.  Please, note here, that sometimes Great Plains Accounting for DOS, Windows and Mac believe that they are on Great Plains Dynamics version – in fact GPA is predecessor to Dynamics and its integration tools are not the same (this article is not applicable to GPA for DOS, please read our earlier publications, dedicated to Great Plains Accounting)

6. For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us help@albaspectrum.com  We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences).  Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County, San Diego), Houston area of the state of Texas