This posting is part one of a two-part series on calling Microsoft Dynamics GP eConnect procedures directly from Transact-SQL (TSQL) code. This post will focus on an introduction to eConnect procedures. Part two will provide more details (and examples) on using the procedures.
In the last few years, our development team has been altering our approach to using Dynamics GP’s eConnect functionality. eConnect is a fairly robust Application Programmer’s Interface (API) for pushing data into GP. It seems that much of our custom development for Dynamics GP focuses around building interfaces to bring external data into Dynamics.
While eConnect offers multiple methods for consuming its capabilities, at its core, it is set of stored procedures that can be called to create information in GP in a validated (Microsoft sanctioned) fashion. This post will focus on the concept of using eConnect stored procedures directly in your Transact-SQL coding.
The first thing you will need if you are going to develop SQL code that consumes the eConnect procedures is access to the “eConnect Programmer’s Guide”. If you have installed the eConnect Runtime on your PC, you find it on the start menu:
If you don’t have eConnect Runtime installed locally on your PC, you can simply do a web search for it.
You will find this reference material invaluable for knowing what options you have for pushing data into Dynamics GP. It applies to your eConnect programming whether using Web Services, .NET, or, in this example, TSQL.
The two areas I use frequently from this guide are:
- XML Schema Reference. The schema reference provides the overall framework by module series of the eConnect procedures (nodes) that exist. As you drill down into the schema, you will see the high level functionality for a module/series, and then specific functionality for a particular transaction type. NOTE: When using this reference, pay attention to the order of the individual nodes once you have drilled into a specific transaction type. This order is the order you will need to follow if you are manually calling the stored procedures yourself (not using XML documents, for instance). As an example, in the screen shot below, you would need to call the “taSopLineIvcInsert” prior to the “taSopHdrIvcInsert” procedure.
- XML Node Reference. The node reference provides specific details on each procedure that exists in eConnect and the specific parameters that the procedure accepts as input or output. The reference also provides information on static fields, like item type, where you need to provide an appropriate value that will translate to the GP screen’s drop-down combo box. Once you have your reference materials in front of you, you can work through the appropriate nodes that you will be using for your particular integration. You don’t have to use all of the nodes available to you. For instance, in the “SOPTransactionType” schema, if you don’t intend to create or update items, you don’t need to worry about calling the “taUpdateCreateItemRcd” node. Same would apply to many of the other nodes in this schema. You will, however, always need to call “taSopLineIncInsert” and “taSopHdrIvcInsert” at a minimum.
After you have reviewed the eConnect nodes that you will be using, you will need to dig into the elements of each node. The schema help file does a good job of showing you the fields that are required, but note that sometimes a field may not be listed as “Required” even though it really will be required for your integration. For example, in the “taSOPLineIvcInsert” node (aka SQL procedure), the element (aka parameter) “LOCNCODE” is not required, but if you are using an item that is “Sales Inventory,” you really will need to populate this field.
In part two of this series, we will get into some recommendations on using the eConnect procedures from within TSQL and provide some examples as well.
For additional tips and tricks regarding Dynamics GP and ERP products, and to be sure to see part 2 of this blog, subscribe to our blog.