A recent post on LinkedIn asked about the reasons that most of the column names in Microsoft Dynamics GP are cryptic abbreviations of 8 upper case characters. My friend and fellow Microsoft MVP, Jen Kuntz (congrats on being re-awarded) responded and in that response tagged me.

I wrote up another comment, and then decided to turn that comment into a blog post….. here it is. 😁

Microsoft Dynamics GP, originally Great Plains Dynamics was written in the late 1980s and used Ctree as its Database platform. At the time Windows 3.0 (with Win32s libraries) ran on top of MS-DOS and only supported 8.3 file names. The Ctree ISAM (flat file) database platform also only supported 8 character column names. This meant that both table/file names and column/field names were limited to 8 uppercase characters.

When support was added for Btrieve (later rebranded as Pervasive.SQL or P.SQL) it still had the same  limitations. Then for GP version 3.0, MS SQL Server support was added.  Also, Dexterity (the language behind GP) was updated to allow longer names and upper and lower case. However, all the existing names for tables and columns in the data dictionary could not be updated without breaking the product.

From version 8.0 of GP, support for Btrieve/P.SQL was dropped and Ctree was relegated to only being used for non SQL temporary or memory tables stored locally in a workstation’s %TEMP% folder. From a developer’s perspective this made it easier to code as we knew that we could leverage SQL functionality when needed and no longer needed to write code that worked one (slow) way on Ctree or Btrieve or another (faster) way on SQL.

You can tell newer columns added to old tables or new tables as they usually don’t follow the 8 character limit, except if reusing old fields already defined in the data dictionary. Table names (even new ones) still usually follow the naming conventions mentioned in the Appendix A of Dexterity Integration Guide (IG.PDF) installed with Dexterity. Below is a quick guide for table physical names.

At this time, I will mention that Dexterity uses a bottom up approach for the data dictionary and that when a field is created, it is given both a Technical name (what the developer uses) and a physical name (what is implemented in the tables). Developers rarely use the physical names and don’t care what they are as long as they are unique within the dictionary. Even when writing pass through SQL code or SQL where clauses, Dexterity developers just use the physicalname() function to convert the nice descriptive Technical names to the horrible Physical names. Because of this there has never been any reason to invest the time, effort or expense to refactor all the code in GP.

Even after 30 years of working with Dynamics GP, I don’t know the physical names for all but the most common tables and fields. There is just no need for me to know them.

When I do need to know them, I use the GP Power Tools Resource Information window to easily convert the short physical names for tables and columns to their more descriptive technical names used by Dexterity developers.

Also, I wrote a tool a while back, called SnapShot, which can generate SQL views for every table in the Dynamics GP product using the longer technical names for tables and columns. I am planning to incorporate that functionality into GP Power Tools so that it does not get lost.

For example: RM00101.CUSTNAME becomes RM_Customer_MSTR.[Customer Name]

So, the bottom line for the reason comes down to history and the legacy of a product that was created in the era of 8.3 DOS filenames.

Hope you find this informative.

David

This article was originally posted on http://www.winthropdc.com/blog.