Dynamics GP Development: Dexterity, eConnect, SQL Scripting notes

Programming for Microsoft Dynamics GP, formerly known as Great Plains Dynamics, eEnteprise (please note that its predecessor Great Plains Accounting for DOS and Windows is different application and its modification options are different and in fact very limited) – if this is you research subject, we offer you this small publication, based on our Great Plains Customization experience, including large projects in eCommerce, Warehouse Management and Barcoding, EDI, Random Weight or Catch Weight, Barcode Labels algorithms, based on Item Number and Serial/Lot number extension, Consignment, Healthcare, Placement, Picking and Packing, Shipping and Receiving, Beverage mixing mills, Freight Forwarding industries.  Great Plains Dynamics GP is good fit for mid-size businesses in USA and English speaking countries, plus it is reasonably localized in Mexico, Caribbean and Spanish Speaking Latin America, it is popular in South East Asia and Oceania (and French speaking Quebec in Canada).  If you are trying to implement (and partially localize Great Plains) for such countries and regions as Brazil, China, Russia, East Europe, Japan, Korea, we feel that we sort of have to discourage you, as Dynamics GP Dexterity doesn’t support UNICODE (Chinese, Japanese, Korean and other hieroglyph based languages), plus in Russia, Kazakhstan, Central Asia Microsoft Business Solutions has Dynamics AX Axapta (and Navision Dynamics NAV) assuming that your overseas subsidiary is mid-size business, and AX and NAV are too large in their implementation scope and budget wise, we recommend SAP Business One, which is localize and certified in most countries with local Government Audit and Tax Authorities.  But let’s come back to the topic: Great Plains Customization tools:

1. Great Plains Dexterity.  Even if you feel that you do not want to review this initial tool and in essence original architecture of Great Plains Software Dynamics, we feel that it is so important, as it is still a core of the Microsoft Dynamics GP ERP, MRP and Accounting applications.  If you have little knowledge about ERP shells, that were popular in 1990th, we would like you to remind SAP ABAP story.  ABAP was and still is the shell and its mission is to abstract SAP system from Operating System and Database Platform.  This abstraction was considered as crucial in the time of Mainframe computers and Mini (Sun) and Micro (IBM PC) computers competition.  Windows, Macintosh, Unix, Solaris, plus database platform parade: Ctree, Btrieve, IBM DBII, Oracle, DBASE, Ingress, Progress, plus consider computer Graphical Operating Systems: Microsoft Windows, MAC and Solaris battle.  GP Dexterity was the way to go those days and it was coded in C programming language (unfortunately Unicode was not known those days, otherwise Great Plains internationalization might be different today).  Dex allowed Great Plains Dynamics to coexist on chosen (but technically it could be transferrable to other C programming language compliant computer platforms without problem) platforms: Microsoft Windows, Apple Macintosh, plus on DB platforms: Ctree/Faircom, Btrieve and Microsoft SQL Server.  Dexterity is limitless in Great Plains modification ability.  If you are experienced Dexterity programmer, you can modify any selected GP form, Report, add new Forms and Reports, Tables, Scrolling Windows, String Resources.  Great Plains was written in Dexterity and you theoretically can rewrite it at your discretion.  However, being reasonable, Dex requires dedication, and if you need to implement Great Plains Modification project with limited budget, then likely you cannot afford your IT department programmer learning curve (it has semi proprietary scripting language Sanscript to give you good starting point for additional research).  Dexterity is the tool, that we recommend to outsource to your chosen Microsoft Dynamics GP Technology Partner.  For those of you who would like to do additional research on Great Plains compatibility with UNUCODE, please review NJ Star products and add-ons.  They should allow you to enter Chinese characters into GP user interface

2. eConnect.  If you are geared toward Dynamics GP ecommerce application custom programming and you already have ecommerce website developed in C#, VB.Net or another programming languages (including Linux, PHP, MySQL), which is ODBC compliant and has the options to call .Net DLL libraries (eConnect SDK could be exposed through these technologies), please review this SDK, which was originally in 2003 dedicated to Great Plains eCommerce programming and real time integration.  eConnect is in essence replication of Great Plains Dexterity DYNAMICS.DIC dictionary master records, and work transaction creation, modification and deletion business logic.  If you research eConnect deeper, you will find that it is based on Microsoft SQL Server Encrypted Stored Procedures, replicating Dexterity logic in more Object Oriented Manner, comparing to mother Dexterity.  With eConnect we often hear GP Batch Posting limitations questions.  Please, note that Batch approval and posting in Great Plains architecture is the function of Great Plains user.  If you need to post GP batches directly from your ecommerce website, please consider Alba Spectrum Posting Server.  Please note that eConnect is strictly integration tool and SDK, you cannot alter Great Plains interfaces or add new interfaces with eConnect, instead you should rescue to Dexterity, or consider web based screens, which could be programmed with Microsoft Dynamics GP SDK (this tool is in its turn call Dex methods to communicate with Dynamics GP interface and DB)

3. SQL Scripting.  Dynamics GP SQL Stored Procedures and views are very popular in Great Plains customization environments.  However, before you go there, we would like to cool down your optimism.  First of all, if you are trying to insert new master records, such as Customers, Vendors, Employees, Addresses, GL accounts – be aware that eConnect methods are coded by original Dynamics GP programmers, who are familiar with GP Source code and the question is  – why you would need to replicate the same methods in custom GP programming.  If you plan to log records into Historical or Open transaction tables: SOP30200, SOP30300, etc, Great Plains batches and transaction posting is very complex and there is the chance that you may violate GP posting logic in your data feeding directly to Great Plains History tables.  It is much safer to bring transaction into Dynamics GP Integration Manager integration and then post them from there or program real time transaction integration in eConnect.  If you think that above remarks are OK to continues, please install Dynamics GP SDK from CD #2, where you can find complete tables structure and fields description

4. Custom Dynamics GP Reporting.  We recommend you to consider GP ReportWriter (SOP Invoice Blank for is the most typical candidate), SSRS, and of course Crystal Reports.  CR reports as good practice should be based on SQL View or Stored Procedure, as Reports design tool is good in report presentation, and Data Set is up to you – Great Plains Programmer

5. Great Plains EDI.  There are several Dynamics GP ISV add-ons available on Electronic Document Delivery.  If your EDI requirement s are really basic, and you do not have or do not want to spend money to purchase EDI mapping Dynamics GP Add-on, you should talk to your IT department is they could produce formatted EDI compliant select statement or Stored Procedure to do the job.  If they are not aware, feel free to call our office

6. Additional Dynamics GP customization and programming topics.  We would like to hear from you, Great Plains users and IT programmers what else you would like to know about Great Plains Modifications, Reporting, EDI, Barcode Scanning, Customization, Integration. Dynamics GP is Corporate ERP platform and we would like to help you to enable it.  Back in 2004 Microsoft introduced so-called Project Green, intended to merge Great Plains, Solomon, Navision, Axapta into compatible building blocks to be ready to choose for future businesses.  The intension was really innovative, but in our opinion the mission should take more time to be realized.  We are participant in Microsoft Dynamics international implementation project and this is why your comments are especially welcomed

7. eCommerce Dedication.  Please, read our articles published on Dynamics GP ecommerce topics.  You should consider ecommerce B2B and B2C Shopping Cart requirements.  eCommerce is very advanced topic, we would like to announce that it is outside of the scope for this small article, but feel free to give us as a call or email us

8. How to get help?  Please call us: 1-866-528-0577, if you are outside of USA: 1-630-961-5918 or email us [email protected]  We speak English, Spanish, Chinese, Portuguese, Russian.  This article is intended to USA, Australia, South Africa, New Zealand, United Kingdom, Ireland, Canada, Ethiopia Dynamics GP prospects and existing customers.  If you are located in different zone (including India, Pakistan, Saudi Arabia, Yemen, Oman and other Arabic speaking countries), please check with us