Dynamics GP table naming/numbering conventions
Dynamics GP has a rather interesting and sometimes frustrating table naming structure. When developers or consultants first see this, they are overwhelmed, to say the least. However, once you learn that there is a rhyme and reason to the madness, it actually makes a lot of sense and it is quite easy to follow and locate the tables that we need.
Tables versus table groups
When data is entered into windows via the Microsoft Dynamics GP application, that data is stored in tables in the underlying SQL database. In most cases, data entered via a single process can be stored in two or more tables. In such cases, it is common for these tables to be grouped together by a certain naming convention. For example, entering journal entry information may update the Transactions Work table (contains General Ledger transaction header information), the Transaction Amounts Work table (contains the General Ledger transaction distributions), and the Transaction Clearing Amounts Work table (contains the General Ledger clearing transactions distributions). These make up what are called Table Groups. These table groups are also referred to as logical tables.
Each Microsoft Dynamics GP table has three names: a technical name, a display name, and a physical name. The technical name is used solely by the software and will usually be seen in some alert messages instead of the display name. The display name is the name that will appear in most of the alert messages generated by the system and is typically the name used for a given table when referring to it in speech, for example, Vendor Master. The physical name is the name that will be found in the SQL database when looking in Microsoft SQL Server Management Studio.
Physical table naming/numbering conventions
When working within the context of a SQL database to generate reports, developers and consultants will make use of the table physical names. A quick scan through the various tables in a standard GP install reveals a bewildering array of table numbers. How can there possibly be any rhyme or reason to these table names? Surprisingly, it does actually follow a certain pattern. For the most part, the table numbering follows a special convention. This schema packs a lot of information into a small number, and it can help developers and consultants know where to begin looking for their data.
As Dynamics GP has grown into a more comprehensive accounting solution, it has expanded, in part, by incorporating third-party applications into the solution. While many of the third-party programmers tried to stick within the relative bounds of the GP physical table naming conventions, as we will soon see, this is not always the case. So, while many of the tables that belong to the core GP modules maintain a fairly standard numbering convention, we will find that this does not hold true for all GP modules.
Generally speaking, GP table physical names contain a two or three digit alpha prefix followed by a five digit number. The three digit prefix represents the module for which the table holds data. The numbers that follow identify what type of data is held in the table. For example, is it posted transaction data? Or, is it information related to the module setup?
As we see in the next image and following sections of this chapter, the numbering schema for a Dynamics GP physical table can be broken down to reveal information about the kind of data that is found in that table.
Alpha code
Let's begin by taking a look at the alpha-prefix for these tables. We have put together a handy reference of some of the most common prefixes and the modules that they represent:
In some modules, such as Manufacturing, tables are broken down even further with Routing tables represented by one set of digits while Material Requirements Planning data is found in tables represented by another set of digits. More commonly, however, entire modules are generally represented by a single alpha code, such as is the case with Project Accounting where all project transactions, billing, and revenue recognition tables are represented with the same alpha code.
Table type
Once we've identified the module in which our data might be stored, our next thought should be towards the type of data that we are trying to find. Before we return to the numbering convention, let's take a look at the various types of data that exist in GP tables:
- Setup: Almost all modules in GP have set up windows that allow users to define default options or other settings for how that module will be used. The options selected in these windows can be found in the Setup tables.
- Master: Some modules, such as Payables Management, allow users to record master records. These master records represent permanent records, such as vendors, for the company. Typically, master records must be entered prior to using a module, as transactions will utilize these master records.
- Transaction: These tables contain the transaction-level data from Dynamics GP. These include the most basic of GP transactions, the journal entry, to transactions entered in sub-ledgers, such as the distribution records of a posted Receivables invoice. Transactions entered in various modules will, depending on their status, be stored in one of three types of transaction tables:
- Work: Un-posted transactions can generally be found in work tables. The name is appropriate, as these transactions can be considered a "work-in-progress". They have not been committed to the sub or general ledgers via posting processes, so we should factor this into our thinking when deciding whether or not to include these records in our reports.
- Open: Generally speaking, records in these tables have been posted, but are awaiting an additional action before they can be considered "closed" or "history". In the General Ledger module, the open table represents all journal entries for the current open year. In other modules, such as Receivables Management, open tables contain data for receivables transactions that have not yet been fully applied.
- History: Transactions that are "completed" generally end up in the history tables. Again, this depends on the module. For example, in Payables Management, fully applied invoices are moved to history. In Purchase Order Processing, however, a routine exists to move completed purchase orders to history. Until this routine is run, records will not be moved to history.
- Cross Reference: Some tables represent data that spans multiple modules. For example, GP users can link purchase orders to unfulfilled sales order line items via Sales Order Commitment. The prefix of the table that contains these links indicates that this is a Sales Order Processing table, but in actuality, it contains data from Purchase Order Processing, as well.
- In addition to these main table types, other table types exist that are less commonly used for reporting purposes. Nonetheless, it is helpful to understand what these table types contain:
- Report Options: Before users can generate a report from the Reports menu, a series of options must be designated for that report. These report options are recorded in a series of report options tables.
- Temp: As the name implies, data is only stored in these tables temporarily. Temporary tables can be used in a variety of situations, such as when a user presses the "Post" button for a transaction. Although rare, it may be necessary to use a temp table when designing a report that should contain data from the time of posting. For example, a sales invoice can be generated at the time the user posts the invoice and can be based on data stored in a temp table at the time.
In terms of the physical naming convention for GP tables, the data type is represented by the first digit following the module code. The following table contains the various table types and their associated number in the numbering convention:
Sequence
The next two digits in the numbering convention make up the sequence number. This number indicates the logical table to which the table belongs. As we discussed earlier in the chapter, logical tables are related tables, or table groups, that share similar data. Not only do these table groups share similar data, but they also share the same data type and sequence number when it comes to the physical table numbering convention.
For example, let's consider the following set of logical tables:
- PM00200 (Vendor Master)
- PM00201 (Vendor Master Summary)
- PM00202 (Vendor Master Period Summary)
- PM00203 (Vendor Accounts)
- PM00204 (Purchasing 1099 Detail)
These tables comprise the Payables Vendor Master Logical File table group. We can easily see this by the numbers that follow the module code. First, we see that these tables share the same data type—remember, 0 means these are Master tables—and second, we see that these tables share the same sequence number. Although we need other tools to help us determine the name of the table group, we are able to easily scan through a list of table physical names in SQL Management Studio and see that these tables are in the same table group.
Variant
The final two digits of the physical numbering convention represent the logical group variant. Within a logical group, numbers are incremented sequentially. This is evident in our example using the Payables Vendor Master Logical File above; as we see the final two digits of each table increment by one.
In table groups related to transactions, the variant often distinguishes between header tables, detail tables, distribution tables, and other related tables.
Keep in mind, what we have discussed is only a general naming and numbering convention for GP tables in their SQL databases. Unfortunately, to the frustration of developers and consultants everywhere, these conventions do not hold true in all cases! At the very least, knowing this convention can point us in the right direction. We can rely on other tools and resources to help us pinpoint the right table when these conventions fall short.
Locating Dynamics GP data using the Resource Descriptions windows
One of the most useful tools for locating data when being tasked with writing reports against Dynamics GP data is the Resource Descriptions tool within the application itself. Resource Descriptions are broken into three distinct windows: Tables, Windows, and Fields. Typically, we find ourselves using a combination of these three windows to locate the specific data we are looking for. There is not really a right or a wrong way to use these windows. Each report developer or consultant could argue that the way he or she uses the windows is the correct way, but ultimately, as long as we are able to identify and find the data we need, we will be that much closer to creating an accurate report that fills our users' needs.
The various Resource Descriptions windows are located by browsing to Microsoft Dynamics GP | Tools | Resource Descriptions.
Tables
Table Descriptions let us select a Product (Microsoft Dynamics GP, Fixed Assets, and so on) as well as the Series (Financial, Sales, and so on). We are then provided a list of all the tables for the selected product and series. By default, the list of tables is by Display Name. We do have the option to change how the tables are sorted by changing the View By field. The options are Table Display Name, Table Group Technical Name, Table Physical Name, or Table Technical Name. The method we choose will depend on how familiar we are with the table structure. For example, we might select Table Physical Name once we are familiar with the naming convention guidelines provided earlier in this chapter. There is also a Find button that allows us to search for the table based on any one of its three names.
To access Table Descriptions follow these steps:
- Open Microsoft Dynamics GP.
- Click the Microsoft Dynamics GP Menu from the toolbar.
- Select Tools.
- Select Resource Descriptions.
- Select Tables.
- Click the Ellipses button and find your table. In the image below, we are searching for the PM Vendor Master File table, which is found under the Microsoft Dynamics GP Product and Purchasing Series.
Once we have found the table we are looking for, we can drill into that table and get additional information for that table. This view includes all of the fields in the selected table, their physical names (as would be seen in the SQL tables), the storage type of the field, and the position.
We can access this additional information by double clicking the table record in the Table Names window. This opens a new window called Table Descriptions:
From the Table Descriptions window, we can further drill into each field and get information such as the Format Type, the Keyable Length of the field and if applicable, any Static Values if it is a dropdown field.
We can access this detail by double clicking a field or selecting the field and clicking the Field Info button to open the Additional Field Information window:
Returning to the Table Descriptions window, the last pieces of information we can access from this window are additional info such as any secondary tables, the secondary tables' keys, what the related fields are for the tables, and what the relationship type is. We can also see the usage, which gives us all the forms and reports that use this table.
Most of this information can be found by returning to the Table Descriptions window and clicking the Additional Info button. This opens the Additional Table Information window as seen in the following image:
Also, from the Table Descriptions window, we can access the Table Usage window by clicking the Usage button on the Table Descriptions window:
Typically, we use Table Descriptions when we know the name of the table needed and we need to find additional information about that table.
Fields
Field Descriptions gives us the ability to again select both the Product and the Core (Series), which contains the field we are looking for. Once both these fields are selected, we are provided with the field list. Similar to how we could look at field info in Table Descriptions, we can look at field info here as well.
To access Field Descriptions, follow these steps:
- Open Microsoft Dynamics GP.
- Click the Microsoft Dynamics GP Menu from the toolbar.
- Select Tools.
- Select Resource Descriptions.
- Select Fields.
- Select a Product and a Core.
For example, in the following image, Microsoft Dynamics GP has been selected as the Product and System has been selected as the Core value. This shows us a list of fields that meet this criteria and a list of the tables that contain the selected field:
The one added benefit of using this window over others accessible via Resource Descriptions is that once we select the field we are looking for, a list will be provided showing us all the tables that contain that field.
This window is typically used when we are not sure exactly which table(s) a requested field resides in. We can use this window to tell us all the table display names that contain the selected field, then we can go to Table Descriptions and find the physical (SQL) table name based on results returned in the Field Descriptions window.
Windows
Windows Descriptions provides us with the ability to view the windows where the data is being recorded or viewed. As with the other Resource Description windows, we select the Product and Series. Once we locate the window we are looking for, Purchase Order Entry for example, we will be provided a list of both the fields on that window and the tables used by the window. We can also use the Form name in this window to find the related fields and tables.
To access Window Descriptions, follow these steps:
- Open Microsoft Dynamics GP.
- Click the Microsoft Dynamics GP Menu from the toolbar.
- Select Tools.
- Select Resource Descriptions.
- Select Windows.
- Select a Product, Series, and View By from their dropdown lists.
As the following image shows, selecting Microsoft Dynamics GP as the Product, Purchasing as the Series, and by Window Display Name as the View By option presents us with a list of all windows available in this unique combination:
We typically use Window Descriptions when we know where the requested information is being entered or displayed in Dynamics GP, but we are unsure of the field name(s) or the table name(s) used to store said data. As with Field Descriptions, once we have a list of possibilities, we can cross reference to Table Descriptions.
This window is a bit constrained in size, and when dealing with a Product and Series with numerous tables, it can often be time-consuming to scroll through the list to find the right window. Don't neglect the Find button in the upper-right hand corner of this window. After selecting this button to open the Find window, we can type in the Display Name we are looking for and the window will auto-focus on this row in the scrolling window. Remember, the Display Name corresponds to the name of the window as it appears in the menu bar attached at the top of the window in GP. Of course, this requires knowing and selecting the Product and Series to which the window actually belongs prior to opening the Find window.