Learning the Microsoft Dynamics GP Table Structure

Whether you are the IT person, a GP power user, or both and tasked with extracting/importing data into or from GP, fixing data, or modifying reports, you need to get familiar with the Dynamics GP table structure.  Not much has changed in the way of Dynamics GP tables over the years, and it may seem quite intimidating when you first try navigating these waters.  Below are some tips to get you started:

1. Your number 1 go-to for table information in the GP applications is from the Resource Descriptions menu. (Tools > Resource Descriptions > Tables)
2. In the Table Descriptions window, click on the ellipsis button to open a list of the products installed on your system, and select the product of the table you need to locate.

Note: The first time you access this window on your system, there will be an initialization process that will only happen once.  Just let it go through the process.

3. Select the module and the View By option. You can view tables by their Display Name, Table Group Technical Name, Physical Name, or by Table Technical Name.  I normally select by Table Display Name which returns the list sorted in alphabetical order or by Table Physical Name.  The Table Physical Name option sorts the list in numeric order of the alphanumeric table name.  I tend to use Table Physical Name option because I am familiar with the numbering logic of the tables.  If you are just starting out, you may want to sort by Display Name until you get more comfortable with this tool.

4. You can also expand the Hide/Show buttons on the right side of the window to view the Technical Name and the Table Group Technical Name where the table belongs.

Dynamics GP tables contain a similar numbering scheme in each of the core modules, add-on products, and many of the third party products.  There are exceptions depending on the product and the number of tables, but for the most part, the numbering scheme is consistent.

Table name roadmap

Table names are composed of two parts.  The first is a prefix that identifies the series and/or module such as GL(General Ledger) or PM (Payables Management); the second is a 5 digit number.  Some exceptions are as follows:

• CM stands for Cash Management. This prefix is from the very old DOS version of GP, and represents tables in the Bank Reconciliation module.
• AFA stands for Advanced Financials Analysis, part of the financial module that holds the information for GP’s standard, out-of-the-box financial reporting.

Note that many of the Series are composed of more than one product.  For example, the Sales Series has three sets of tables including RM (Receivables Management), SOP (Sales Order Processing), and IVC (Invoicing).  In addition, depending on what other products you own, you may see other tables integrated with a particular Series.  In the example below for the Sales Series, you can see MC and PA tables for Multicurrency and Project Accounting tables that integrate with Sales.

As you search on each Product/Series combination and switch the table View By, you’ll see how all of the products are grouped and will become more familiar with the table organization.  Notice that the standard GP Add-on products and any third-party products installed are listed separately under their own product name.

Often times, an add-on or third-party product does not necessarily have tables listed under each series.  It will only have the tables listed under the series that are relevant to that product.  Some products will list all of their tables under the Project series or Third-Party series.  If you’re having trouble finding a list of tables for a particular product, change the series to either Project or Third-Party, and you will likely find most of the tables listed there.  If you are still having trouble locating the table, try changing it to the series associated with the product that makes sense.  For example, the screenshots below display the HRM Solution Series has tables listed under the Payroll Series, Third-Party, and System Series:

5. Once you’ve identified the table or tables you need, there is so much more information you can learn by clicking on some of the available options from the main window. For example, select the Purchasing Series > View by Table Physical Name, and find the Purchase Order Work table. Double click on the table to open the Table Descriptions window:

6. In addition to the Table Name, Product, and Series, the Table Descriptions window displays the Technical Name, Physical Name, and Table Group information. It also provides the main key, key segments, the fields associated with the table, and other field related information.  Click on the Field Info tab for Additional Field Information.

Notice this field for the PO Status shows the static values for each type of PO Status.  These values are the values displayed in the SQL tables for each PO record.

The Additional Info window shows all of the forms and reports related to this table.

The Additional Table Information window displays any secondary links this table has to other Dynamics GP tables and their relationship type.

7. Use the Find button to search for tables by Table Display Name, Table Group Technical Name, by Table Physical Name, and by Table Technical Name.

8. Finally, if you’re having difficulty locating a table, click on the Print button to print a report of all the information in the Table Names window.

9. You can then use the Find button on the Screen Output window to find the table information.

If you are experiencing difficulty accessing Dynamics GP table information, RSM offers access to Certified Microsoft Professionals, help desk and phone support, knowledge, and experience with third party products and dedicated account management.  Please contact our professionals for more information at erp@rsmus.com or by phone at 855.437.7202.

Nancy Hogan