Tuesday, July 6, 2010

Dynamics GP Table Names

A client recently told me they were very confused by the table naming conventions for Dynamics GP. I’m so used to those legacy table names that I don’t appreciate what a wonder it must be for a new user trying to write reports.

In fact, any class I train that involves pulling data from tables (Extender, SmartList Builder, Excel ReportBuilder, ListBuilder, Report Writer, SSRS, Crystal Reports, etc.) sooner or later results in the question “How do we know which table to use”. This query spawned the on-line class I will be doing for GPUG Thursday http://www.gpug.com/events/GPAcademyGPData070810 entitled “Finding the GP Data you Need”

What do the table names mean?

There is actually a very good naming convention for Dynamics GP data tables. This is only a convention, however. It is followed by the GP programmers pretty diligently, but not so much by some 3rd party programmers. Here’s the basics, the first 2 or 3 characters will indicate the module name, the numbers indicate the type of table.

The modules (prefix)

Some of the more popular module abbreviations are in the table below.

PrefixModule
GLGeneral Ledger
AFAdvanced Financial Analysis
PMPayables Management
RMReceivables Management
SOPSales Order Processing
POPPurchase Order Processing
IVInventory
IVCInvoicing (NOT SOP)
UPRUS Payroll
CMCash Management (Bank Rec)
LKLinked Transactions
MEEFT
PAProject Accounting
FAFixed Assets
AAAnalytical Accounting
DTAMulti-dimensional Analysis
SYSystem or Company
AHRAdvanced HR
HRHuman Resources
BMBill of Materials
DDDirect Deposit
EXTExtender
MCMulticurrency
SVCField Service
ASISmartList Favorites
ERBExcel Report Builder
EXTExtender
SLBSmartList Builder
WDCField-Level Security

The Table Types

After the prefix, the number indicates the table type. Knowing these numbers will help you zero in on the correct table. The table below sets out the numbering convention used by the Dynamics GP programmers.

Table NumberDescription
00000Master Tables
10000Work Tables
20000Open Tables
30000History Tables
40000Setup Tables
50000Temp Tables
60000Relation Tables
70000Report Options Tables
80000Posting Journal Reprint Tables

One of my earlier posts explains the difference between ‘work, open, and history’ http://dynamicsconfessions.blogspot.com/2009/11/moving-from-work-to-open-to-history.html.

Some information on the remaining tables follows.

Master Tables are mostly what you find under the ‘Cards’ area. These are your Customers, Vendors, Inventory Items, GL Accounts and the like. For instance, the information you see on the Customer Maintenance window is stored in the RM Customer MSTR or RM00101.

Setup Tables include choices you have made to initiate a module. For instance, the information entered on the Payables Management Setup window are stored in the PM Setup File or PM40100.

Temp Tables are tables that are used temporarily by the system and the records in those tables can normally be deleted without issue. For instance the Net Profit Temporary table or AF50000 is used by the Advanced Financial Analysis module to hold the Net Profit amount that will be used on one of the statements. Once the statement has been printed, the number is irrelevant.

Relation or Cross Reference Tables are tables that are used to store information that spans more than one module. For instance the SOP/POPLink table (SOP60100) holds the information about POP documents linked to SOP documents. Another example is the Sales Customer Item Cross Reference (SOP60300). This table reflects the information as to how customer item numbers relate to regular item numbers. There are not that many tables in the 60000 range.

Report Options Tables contain all of the information you enter in any of the report options windows. The image below comes from the Payables Trial Balance Report Options window (PM70500).

image

Posting Journal Reprint Tables contain all of the information you need to reprint your posting journals. So don’t feel like you MUST print all of those reports. They are here waiting for you should you ever need to reprint them.

So now you know. If you were looking for a posted inventory transactions, you would start with the IV20000 table and go from there.

Victoria Yudin has some great information on the popular tables from each module. If you are creating reports, it is information you can use. http://victoriayudin.com/gp-tables/

Have fun with it! Until next post!

Leslie

9 comments:

Jeff Rowles said...

Wondering if anyone can help direct me to list of table and field changes in GP 2010. These are not included in "What's New in GP 2010" document and can't seem to easily find on web. (I am specifically looking for changes associated with Payroll and Human Resources modules). Any help would be much appreciated.

Dynamics Confessor said...

Have you installed the SDK for GP2010? You can find it under the \tools\SDK folder on the install DVD.

Leslie

Unknown said...

hi, i'm new with Microsoft Dynamics.
i just want to create a new report on SSRS using report writer as the source layout.
the report i'd like to create is RM Detail Historical Aged Trial Balance.
i've print out the report definitions (table relationship), and i found it uses RM_HATB_Customer_TEMP.aging
is there any ways to know which table it physically use?
thanks!

Unknown said...

Hello;
I am looking for tables that contain information on Manufacturing Orders Production and posting.
Many thanks in advance.

Dynamics Confessor said...

Download Mark Polino's spreadsheet, it lists tables and fields from the Manufacturing module, and all other modules too.

http://www.box.net/shared/duvbizv2m5

Kind regards,

Leslie

Beat BUCHER said...

Hi Leslie,
you could add to your table prefix list the code CPY for the Canadian Payroll (as opposite to the UPR for the US) .. :-). And also the PDK for the Personal Data Keeper, which quite a few company uses with Business Portal.
Have a great time,
Beat

OSr Group said...

Your post is really very helpful.
Plywood Manufacturer in India

Anonymous said...

I’ve got a question for anyone about the existence of a date table in Dynamics GP 2010.

I’m trying to create a SQL report that pulls in only certain GL account numbers as of a certain date. I’ve created this report on Management Reporter but the main downfall to Management Reporter is that I can’t choose a certain date or date range that I want the numbers to pull as of.
I’ve been creating this report in SQL instead of Management Reporter because I was under the impression that I could choose a date range. So far I haven't found the table (I'm hoping exists) where you can pull into a SQL report to grant the ability to choose a specific date or date range. My thoughts are that there has to be one because you can choose a certain date range for a Trail Balance Report. If there is a table for date range, does anyone happen to know the table name?

Thank you,
Lindsey

MIPC said...

Hi Lindsey, did you ever get answer with the location and name of the Date Table/Dimension. I too could do with the date table from the same GP company database for a Power BI report.
Do let me know please how you resolved this. Thanks David