Dynamics GP Table Naming Convention

Posted by Tony Khoury, Senior Consultant on October 10, 2016


Whether you are modifying reports, building a more complicated SmartList, or working with SQL in Microsoft Dynamics GP, the underlying table names can be confusing.  Sometimes it feels like a guessing game for us end users.  Here are some tips to go by when looking for your data and which table it’s in. 

Explaining Dynamics GP Table Names

Dynamics GP tables have three names – a technical name, display name and a physical name.

Some table names from the SOP module…

1.  Technical names are comprised of 3 sections (Module_Contents_Main (or Sub_Main)
     a.  The first section is a module abbreviation which can have a 2-4 character module abbreviation.  
          This portion of the name is always expressed in UPPER CASE.

     

     b.  The second section is called the content section.  In this section you will find a word to describe
          what’s housed in the table (like the Batch or Deposit tables above)
     c.  The third section is called the Main or Sub_Main. This section describes the type of contents
          housed in the table. When used, these are also capitalized.  Below are some examples of
          Main or Sub_Main.

          
          

2.  Display names appears usually in windows or reports (Mostly when you are trying to build a report
     using Smartlist Builder or Report Writer).  They do not use abbreviations and are written in
     plain English.  They are often referred to as the Friendly name.
          a.  They typically begin with a one word identifier that tells you which module or series the table
               belongs to. 
          b.  They sometimes end in Master, Work, Setup, or History depending on the contents
               of the table.

3.  Physical names are just that.  They are the physical names of table in SQL.

      

          a.  The first part of physical names are 2-3 character prefixes identifying GP Modules.

          b.  The numbers following the prefixes indicate the type of table.

  1. Master Tables are mostly found in the Cards area of GP…  Customers, Vendors, Inventory, GL accounts…. Master records. An example of a master record would be RM00101 which is the Customer Master record.
  2. Work Tables usually house unposted transactions whether they are GL, AP, AR, SOP, or Bank Transactions.
  3. Open tables house posted transactions.
  4. History tables usually house information that is posted and moved to history. 
  5. Setup tables house the options you have chosen for that particular module.
  6. Temp tables are just that tables that are used to temporarily house data.  Records in those tables can normally be deleted without issue.
  7. Relation tables are Cross Reference tables that store data that spans more than one module.  For instance the SOP/POP table (SOP60100) holds the information about POP documents linked to SOP documents.
  8. Report Options tables house your preferences and settings that you have saved in regards to your reports.
     

Ok, but How Do I Find the Table I Need? 

If you’re still a little confused, don’t worry.  Microsoft Dynamics GP has a helpful feature built in.  Go to the Microsoft Dynamics GP menu > Tools > Resource Description.  In here, you can choose to see information on any of the tables.

Once you find the table you think you need, simply double click on that row and you will get a second window to open listing the fields in that table.

If you need some help, contact our team of Dynamics GP experts at Support@Crestwood.com.  They are great at pointing you in the right direction, report writing & modification, and even building the Smartlist you need.


Share this:
 
 

Comments:

Add your comment: