Dynamics GP SQL Scripting and Reporting

Even if you are on older version of Great Plains Dynamics on MS SQL Server and even Pervasive SQL 2000/Btrieve or Ctree, you can still access the database directly in SQL Server Management Studio, or via ODBC connection (Linked Server, especially when you are on ODBC compliant DB, such as Btrieve).  Let’s try to give you entry level introduction into SQL scripting for Dynamics GP or Great Plains Dynamics/eEnterprise:

1. Warning.  Let’s first tell you what you should probably avoid in your initial experiments.  Dynamics GP is one of the mid-market Corporate MRP, ERP and Accounting systems, where you can access database not only for data selection, but also for data updating and insertion (in comparison to such ERP applications as Microsoft CRM, Dynamics AX Axapta or SAP Business One, where you cannot do direct update or insertion, or better say, you technically can do that, but it is either prohibited or absolutely not recommended).  Most of Great Plains objects, such as Customer record, for example, are stored in several tables and GP doesn’t use referential integrity on the SQL level (referential integrity is provided by so-called Great Plains Dexterity business logic and stored in Dynamics.dic file, and other Dexterity compliant dictionaries).  This is why there is very high possibility that if you update or delete the record in only one table (RM00101 – customer master, as the most obvious for you) – you may not be aware about other tables (RM00102 – address master, just for example, there are more tables in customer master cluster), and this might lead to data compromise for this specific customer (Check Links routine might try to restore the status quo, but it is not all-mighty).  This is why we strictly recommend you, as SQL coder, to start with all kinds of Select statements and avoid Update and Insert, at least until you know exactly what you are doing and what is the impact on the data integrity

2. SQL Select statement and Dynamics GP.  Obviously Select statement means reporting.  But it is very powerful one, and you can use such constructions, as SQL View, SQL Stored Procedure (just use Select statement there, do not do Update or Insert, see paragraph above; SP can give you ultimate power, as you can create temporary tables to achieve really exotic joins).  If you create really smart SQL View, it is report by its nature (with pretty basic report format in SQL Query result, which could be exported into text file or Excel); and we strongly recommend you to use these Views and Stored Procedures for professional reporting tool, such as Crystal Reports, Microsoft SQL Server Reporting Services (SSRS), MS Access reports, Excel reports and other ODBC or Native SQL Server driver compliant reporting products.  To give even more hints, if you deploy such constructions, as SQL Linked Server (or Open Row Set, where linked server is created on the fly and destroyed at the end of the query), you can join Dynamics GP SQL Server residing tables with other DB sources, such as Text File (CSV, tab, or special character delimited), MS Access, Btrieve/Pervasive SQL, Ctree, FoxPro, Oracle, MySQL/PHP/Linux, XML or you name it, assuming that it is ODBC compliant or has native driver.  If you are on older Pervasive SQL 2000 Great Plains version (7.5, 7.0, 6.0, 5.5, 5.0, 4.0) and you do not own MS SQL Server license internally, we recommend you to deploy MS Access to look at GP tables content

3. Tables structure and fields description.  OK, great news, very cool, but how do I know the table name and how to join it with associated tables?  Well, unfortunately Great Plains Dexterity design was oriented to cross-platform compatibility (read as possible quick migration from Btrieve to MS SQL Server, Unix, Oracle, Solaris, Macintosh Operating System, where table naming convention should be very simple, and not human intuition friendly, they are difficult to guess what they really store by their names, such as SOP10100, RM00200, GL20000).  The good news is – there are several ways to research table structure and even each fields and in some cases default values for drip down list oriented fields.  In our opinion the simplest way is to login Dynamics GP user workstation, then Microsoft Dynamics GP -> Tools -> Resource Description -> Tables (Fields or Forms).  Here you need to have some background in Accounting, ERP, MRP, HR, wherever is your targeted tables cluster, have your accounting people to help you in guessing, for example Employee master table should be in Payroll series to give you the idea.  Second option is to install Dynamics GP SDK (Software Development Kit), where there is the complete diagram of the tables, fields and their relations.  In our opinion, as Dynamics GP programmers, it is obviously helpful, but it is not something that you can use for quick discovery (print it out and take it for weekend or vacation time research, if you that kind of software developer and you would like to see the complete and organic picture)

4. SQL and Integration routines, including real time or quasi real time ecommerce (such cool shopping carts, as Magento, ASP.Net Storefront), EDI, or even one time data migration from your legacy applications, such as Oracle Financials, Peachtree, MYOB, QuickBooks, please cool down with the idea to use direct Insert SQL statements, even if you feel that you are at this point very experienced and knowledgeable about Great Plains table structure and data flow.  There is dedicated tool, named Integration Manager, where you can setup in the end user fashion major integrations from external ODBC compliant data sources (again, Oracle and the others, listed above and obviously text files).  IM validated business logic and there is no way for you to make a mistake and violate data integrity.  Integration Manager is our recommendation, when you are trying to integrate the ongoing data from custom programmed Warehouse Management System, Supply Chain Management routines, or when you are integrating in-house programmed Manufacturing or production routines (often based in Microsoft Access)

5. Dynamics GP data repair via SQL update statements.  This is what is probably the most valuable for your employer.  However, we still recommend you to be very conservative and do not do it on your own.  The better approach is to login Microsoft Customer Source and search Dynamics GP knowledge base.  Chances are high that you will find knowledge article, describing exact data repair steps with SQL queries.  One of the most popular GP data repair requests is unlocking the batch, stuck in posting (where the likely case was user computer crash, due to computer virus or extensive internet searching while the batch was in posting process), you can count on the Customer Source (or Partner Source if you are launching Dynamics GP consultant career) and fix your SY0500 and SY0800 table records

6. If you decide to do your first trial data repair via SQL Update.  Here we recommend you small trick on how to backup and then if required restore Dynamics GP or old Great Plains table.  In Query Analyzer you can issue the following statement select * into [new table name] from [original table name].  Then you are trying the data repair on the original table (having all the users to log off, or do not use the module).  If your repair fails, you can roll back by issuing Alter table [new table name] drop column DEX_ROW_ID. Then delete the original table and run insert into [original table name] select * from [new table name].  At this point all your unsuccessfully repaired data should be rolled back

7. SQL Insert Statement and Dynamics GP.  This is the most potentially dangerous method.  If you would like to see the simple example, here is the one for you to try.  Let’s assume that you are importing GL chart of accounts and you do not want to bother with Integration Manager (or you do not have the software license for IM).  In this scenario, please review GL account master table (GL00100) and then try to design the SQL insert statement filling up required fields.  When you are done with the design and actual execution, login Dynamics GP user interface, go to (for the versions 9.0, 10.0 or 2010 Dynamics GP) -> Maintenance -> Check Links, select series Financial, logical tables as Account Master and run it.  Based on the information in the table GL00100, Check Links will populate matching records in all the associated tables (in account master table cluster).  Please, again, note that even you might be OK with General Ledger Chart of Accounts, do not rely on the same magic power for other GP master records and transactions

8. Some observations or how to be more successful in Dynamics GP SQL scripting and in-house technical support.  Great Plains Dynamics is Accounting application with rich business logic, extending to such areas as MRP, Manufacturing, HR, Inventory Control, Logistics, Distribution, SCM, ERP.  If you got your college degree and industry certifications in SQL related areas, we recommend you to get either evening classes in accounting, or read the books in basic accounting, ERP and MRP (wherever you feel your gap is).  For IT professional it is not really a big deal to get some exposure to Corporate ERP theory

9. To request further support, please call us 1-866-528-0577, help@albaspectrum.com We need to discuss your cards in order to recommend you the best solutions, which is not contingent to our preferences.  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