Microsoft Dynamics GP ERP has a very good and easy to use integration module: Great Plains Integration Manager. With Dynamics GP version 10.0, IM got revolutionary enhancements in the form of eConnect connectors. Now, where these connectors are available, the IM performance issue has virtually evaporated and you can integrate thousands of SOPs per minute, since eConnect implements SQL stored procedures, compared to OLE server for older IM connectors. However, we often see the problem of finding a GP programmer, who is a master with advanced ODBC queries to make the integration manager work with rather rigid and cumbersome text files, produced from your legacy MRP or EDI application. . Let’s move on to the bullets:

1. Microsoft Text ODBC Driver. Unfortunately for Windows XP, Windows 2003, and Vista, this driver has known bugs and is not fixed by service packs. The solution is to avoid defining the format of the fields and allow the controller to guess.

2.SQLQuery. Assuming you have a connection to the Microsoft text driver, see issues #1, the next step is to produce a complex SQL select statement. Let us give you some examples. Imagine the situation, when you are only given purchase expense accounts and you need to modify the general ledger distribution in accounts payable vendor invoice integration. Yes, of course, what you need is the Accounts Payable account distribution offset. To achieve this, create a join clause in the GP IM integration query. The AP is usually sent in a single Accounts Payable account, for you this means that you have to group the Purchase lines together and use the SUM construct to create a single Accounts Payable distribution line.

3. Limitations of the text handler. To give you a good example, if you have exactly identical GL layout lines in the case described in bullet #2, the text handler will not be able to distinguish those two lines and will skip one of them. What you need to do is provide a unique parameter, which will restore the uniqueness of the lines. You should also note that the advanced ODBC option in IM should allow you to implement standard SQL constructs: group, union, have, and others.

4. Additional consideration for eConnect, related to eCommerce web development and integration. eConnect is essentially an SDK, or Software Development Kit, which opens up the GP object for manipulation to the Microsoft Visual Studio C# and VB.Net developer who is familiar with encrypted XML and SQL stored procedures. eConnect will do the job when you’ve set up an e-commerce website, written in PHP, .Net, EJB and other open technologies, and need to integrate with Microsoft Dynamics GP accounting back office.