If you have ever tried to write a report using MS Dynamics GP tables, you’ve almost certainly come up against the question “How on earth do I find the information that I want?”  Believe it or not, there is a way to narrow down the tables that you need.  At InterDyn Artis, we can help.  Here’s how:

Table Resources

MS Dynamics GP > Tools > Resource Descriptions > Tables

1. Click the ellipsis button to open the table names screen.
2. Choose the product and series.
3. Choose the sort.

a.       Table Display Name – descriptive name. This is the name used in Smartlist Builder and Excel Report Builder.

b.      Table Technical Name – similar to the descriptive name.  This is the name used in the GP Report Writer.

c.       Table Physical Name – alphanumeric name.  This is the name that used in SQL, MS Query, Excel, Visual Studio and any other report writer that connects directly to the SQL database.

d.      Table Group Technical Name – table groups with similar types of information.  These tables are compared during checklinks.

In the core modules, the table numbering indicates the type of data that is in the table.  Products developed by third party developers do not follow the MS Dynamics GP numbering schema, but it is useful for the bulk of GP modules.  As a rule of thumb, if the data is/can be entered on a separate screen, it is in a separate related table.

Naming schema:

Alpha module abbreviation – 5 digit numbering sequence.

Alpha codes:  GL for General Ledger, RM for Receivables Management, SOP for Sales Order Processing, etc.

Numbering sequence:  First 2 digits indicate the type of data; last 3 digits narrow down the related tables.  In the numbering sequence, the lowest numbered table in sequence will have the most or the most general data, with more specific data

Numbers less than 10000 = Master data (customer, vendor, employee, accounts, inventory items, etc.)

Numbers in the 10000’s = Work transactions (unposted).

Numbers in the 20000’2 = Open transactions (posted but not completely applied).  Accounting modules only.

Numbers in the 30000’s = History transactions (posted and completely applied).

Numbers in the 40000’s = Setup data.

Numbers 50000 or higher are not useful for reporting.

RM00101 is the main customer master.  Other tables in the 001xx series have specific information that you can see on other screens. 

RM10xxx tables hold Work transactions (unposted ). Distribution history is often kept in the Work table until the transaction moves to history.

RM20xxx tables hold Open transactions (posted but not moved to history).

RM30xxx tables hold History transactions.

 

 

If you have any questions on how to write a report using MS Dynamics GP tables please contact InterDyn Artis.

By InterDyn Artis, a Microsoft Dynamics GP Partner in North Carolina.