This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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 ….

Transactions

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.

 

Array Fields

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.

 

Composite Fields

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.

David

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.