There is actually a very good naming convention for Dynamics GP data tables. However, this is only a convention. While you will not be arrested by the table-naming police for not following the convention, adhering to a standard makes it easier on everybody. The published naming conventions were followed by the Dynamics GP programmers pretty diligently, but not so much by many third-party developers. Here are the basics: the first two or three characters will indicate the module name, and the remaining numbers indicate the type of table.

Some of the more popular module prefixes are listed in the following table:

After the prefix, the number indicates the table type. Knowing these numbers will help you zero in on the correct table. The following table sets out the numbering convention used to indicate the table type.

Developers often put their company's initials at the beginning of the table name. For instance, table WDC41101 is the Advanced Security Setup table, which is part of the Advanced Security module. The Advanced Security module was developed by Winthrop Dexterity Consultants (WDC).

An explanation of how data flows through the WORK, OPEN, and HIST tables deserves its own section; the remaining table types are covered next.

70000 – Report Options tables

Report Options tables contain all of the information you enter in any of the report options windows that defines what information you would like to appear on a particular report. The following screenshot comes from the Payables Trial Balance Report Options window; information recorded on this window would be stored in the Report Options series of tables (PM70500).

If your customization will be interacting with transactions, you need to understand how the transactions flow through the transaction tables. Generally, there are three phases to a transaction: Work, Open, and History. How transactions move through each of these phases varies by module, with no two modules working exactly the same way, but the concepts are the same.

With a broad brush we will paint the Work phase of a transaction as an unposted transaction. In my world, the term posted means to be committed to the ledger. Once a transaction is posted, it cannot be deleted nor can it be unposted. Before it is posted, you can still work on it. Therefore, work transactions are not posted. Transactions in the Work phase are stored in the 10000 tables. For example, if you were looking for an unposted inventory adjustment, you would start by looking in the IV10000 table.

The next phase of a transaction is the Open phase. What we know about an Open transaction is that it has been posted, but it is not yet in history. Examples of Open transactions include a vendor invoice that has not been paid by the company or an amount due from a customer as an account receivable. Often, transactions that are in the Open phase are called outstanding transactions. Open transactions are stored in the 20000 tables. If you were hunting down an unpaid vendor invoice, you would look in the PM20000 table.

The final phase of a transaction is the History phase . A transaction in history has been closed and settled. No amounts are outstanding, nothing is unapplied, and it is a fully completed transaction. How a transaction moves to history is different for each module; whether a transaction can come out of history and move back into the Open phase also varies by module. What we do know about a transaction in history is that it has been posted and is no longer considered open. History transactions are stored in the 30000 tables. A void sales order would be found in the SOP30200 table.

Victoria Yudin has some great information on the popular tables from each module at http://victoriayudin.com/gp-tables/.

You can find a more thorough explanation of how transactions flow through the Work, Open, and History tables at http://tinyurl.com/d5townx.

Whenever Dexterity is used to create a SQL table, a number of stored procedures are automatically created and are used to optimize database performance when performing table operations. The names of most of the auto-generated stored procedures that apply to normal table operations typically begin with zDP_, followed by the table's physical name and a suffix that indicates the purpose of the stored procedure. The following table lists the purpose and numbers of these procedures:

For example, the following table shows that the stored procedures are auto-generated for the PM Class Master file (PM00100). This table has three keys; keys 1 and 2 are unique, key 3 is not.

Other automatically generated stored procedure prefixes include:

Back in the Dark Ages, we had only eight characters to work with for naming tables and fields. Who needed more than eight characters, right? By following the strict but sensible naming convention described in the previous section, thousands of integrating products have been created including tens of thousands of tables. Using the old naming convention, very few duplicate table names have cropped up.

The new table naming convention includes real words and abbreviations that we all can understand instead of the eight characters we were limited to before. Sounds good, but can you imagine working with an application containing 3,000 tables that are not named in any consistent manner? As with most things, when it comes to naming database tables, consistency is a virtue.

While there is still no table-naming-convention police that will visit your office, we think you will find the table-naming convention described next to be a best practice. This new naming convention is both easy to understand and easy to implement. Dexterity programmers will recognize this as the table's Technical Name .

Table names will comprise of a module abbreviation, followed by a term that describes the contents of the table, followed by a subtype abbreviation (if appropriate), and then by a main type abbreviation:

MODULE_Contents_SUB_MAIN

So the General Ledger chart of accounts would be translated to:

GL_Account_MSTR

The common module abbreviations are the same ones described in the Original table-naming convention section. It's from this table that you would get the GL portion of the previous table name.

The following table shows common subtable abbreviations and the type of subtable. For the Asset Financial Detail Master table of the Fixed Assets module, the new physical name would be FA_Financial_DTL_MSTR. The DTL portion of the name would come from the following table:

The following table shows common Main table abbreviations. For the Invoicing Transaction History table, the physical name would be IVC_HDR_HIST. The subtype of HDR comes from the previous table and the Main table type of HIST comes from the following table:

Although SQL allows a table name of 128 characters, the Dexterity limit is 80 characters. Adhering to the most restrictive case of 80 characters will help ensure the portability of integrating applications. As a side note, if we need more than 80 characters for a table name, perhaps we need to reconsider our approach.

The previous tables certainly do not represent the exhaustive list of all possible table abbreviations, but they will get you off to a good start. Your fellow developers will thank you for your consistency. Don't forget to add your company's initials at the beginning of the name; this will both help reduce duplicate table names, and make your module's tables easier to spot.