Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued
As a followup to last week's post about Understanding how Microsoft Dynamics GP works with Microsoft SQL Server, I am adding a few more topics about why Microsoft Dynamics GP works the way it does with Microsoft SQL Server. So here goes ....
Since version 6.00, Dexterity (the development environment for Microsoft Dynamics GP) has had the ability to create SQL transactions with commit and rollback. Over time, this functionality has been used to add transaction handling to the posting routines of the main modules, including the GL, RM, PM, IV and SOP modules. The Batch Recovery window (Tools >> Routines >> Batch Recovery) is used to restart batches which have been rolled back.
Table Naming Conventions
The Table Names in Microsoft Dynamics GP were originally named with eight upper case characters. This was due to limitations in what names were supported in the Ctree and Btrieve database platforms, which in turn were limited by the operating system to 8.3 file names. While these limitations were removed in later operating systems and versions of Ctree and Btrieve, we still use the same naming convention for tables.
The naming convention for tables is based on a 2 or 3 character module code followed by a 5 digit number. It is made up as shown in the example below:
RM00101 which breaks down to RM 0 01 01 where ....
RM is the module code, below is a table with examples of module codes:
Module Module Code Cash Management CM General Ledger GL Inventory Control IV Invoicing IVC Multicurrency Management MC Payables Management PM Purchase Order Processing POP Receivables Management RM Sales Order Processing SOP System Manager SM
0 is the table type, below is a table with the valid table types:
Table Type Value Master 0 Work 1 Open 2 History 3 Setup 4 Temp 5 Relation 6 Report Options 7
01 is the Sequence Number which indicates what logical group of tables this table belongs to. In this case the Customer Master tables.
01 is the Variant Number which is incremented for each table for the logical group of tables. Below is an example of a logical group of tables with the same Sequence Number and incremented Variant Numbers:
Technical Name Physical Name RM_Customer_MSTR RM00101 RM_Customer_MSTR_ADDR RM00102 RM_Customer_MSTR_SUM RM00103 RM_Customer_MSTR_Period_SUM RM00104
There are some tables which have been added to the system since the limitations have been removed which do use long file names, but the majority of the tables are based on this naming convention.
Field Naming Conventions
In the earlier versions of Ctree and Btrieve the field names were limited to 8 upper case characters. Most of the fields which make up the columns in the tables use abbreviated names to be able to fit into the 8 characters. For example:
Technical Name Physical Name Customer Number CUSTNMBR Customer Name CUSTNAME Contact Person CNTCPRSN
This limitation has also been removed and so there are newer fields in the system that are no longer using this format. A long physical name is usually just based on the technical name with the spaces converted to underscores.
As SQL Server does not understand the concept of arrays, they have been implemented in SQL Server using the physical name of the field (as defined in Dexterity) followed by an underscore and then the array element starting from 1. The first element in a Dexterity array is numbered 1.
For example: an array of 12 monthly sales figures would be stored in SQL as SALES_1, SALES_2, SALES_3, .... SALES_12.
SQL Server does not understand Composite fields either. In Dexterity a Composite field is a field made up of multiple component fields so it can be treated as a single field. The best example of a composite field is the Account Number field in Microsoft Dynamics GP which is formatted based on the account framework selected when the application is first installed. Composite fields also use the underscore and sequence number suffix.
Please note that the is possible to have an array of composite fields, but an array field cannot be added into a composite field. If there is an array of composite fields, the array element number will be followed by the component sequence number.
For example: an array of Account Numbers (with 3 segments) will be stored as ACTNUMBR_1_1, ACTNUMBR_1_2, ACTNUMBR_1_3, ACTNUMBR_2_1, ACTNUMBR_2_2, ACTNUMBR_2_3, and so on.
For more information on the naming conventions used within Microsoft Dynamics GP, please review Appendix A of the Microsoft Dynamics GP Integration Guide manual (IG.PDF) installed with Dexterity.
Let us know if you have any other questions about how Microsoft Dynamics GP works with SQL Server.