(For more resources related to this topic, see here.)

System databases versus company databases

The first task of identifying where our data is located is making sure we are using the correct database! Microsoft Dynamics GP 2013 utilizes the Microsoft SQL Server platform as its database engine. When Dynamics GP 2013 is installed on our environment and a new company is created, the installation process creates several databases on a server that has been designated during the installation. These databases will store all the information entered through the Dynamics GP 2013 application, and we can use SQL Server Management Studio to access the underlying tables that store this data.

Microsoft Dynamics GP has two types of databases, a system database and company database(s). For first-time report developers and seasoned writers, knowing which of these databases stores a particular piece of information we need is crucial for an accurate report.

System databases

Prior to GP 2013, the system database was named the DYNAMICS database, and this default name could not be changed. Now, with the new installation of GP 2013, the system database can be named as something different than DYNAMICS. This functionality was introduced as a way to allow multiple sets of GP installations to reside on the same SQL server instance. This is especially important for companies providing GP hosting services for multiple companies on a single SQL server instance.

When Microsoft Dynamics GP is first installed and some initial settings are provided, a system database will be created. This database is the system database that can contain up to ten characters. It includes things such as records for each company that you create, the organization’s registration information, and the maximum account framework.

While many of us can expect to work in environments where only one system database exists, and where that system database uses the standard ‘DYNAMICS’ name, we should still be careful not to hard-code references to this database. While we may have been able to take this shortcut in reports for earlier versions of GP, this will surely cause issues when we least expect. Whenever querying company data, use the DBNAME field from the SY00100 table in the company database to ensure the right system database name is being used.

From a reporting standpoint, there is certain information located in the system database that we may need to report on at one time or another. We have provided a quick reference for this information in the following list:

• Multicurrency System Setups: This includes the setup of the currencies, the exchange rates, and the currency symbols for those organizations that process transactions in any number of foreign currencies.
• Intercompany Setup: This is where the intercompany relationships are stored and the specific dues to/due from accounts are mapped.
• Organizations Structures: This will include the organizational levels and entities that have been created for those organizations that use this feature.
• User Master: This table stores information about the users in the ERP system, including their user ID and username.
• User Tasks: This table stores the tasks that users set up in Dynamics GP. These are the tasks that are displayed on the users’ home screens in GP.
• Company Master: This stores company setup information, such as whether security is enabled, the company ID is in the form of the company database ID in SQL Management Studio, primary address information, tax schedule defaults, and any number of options for the company.
• Security Setups: This includes all of the security tasks, security roles, and user security assignments.
• User-Company Access: This includes the companies that each user has access to.

Company databases

Each company that we create in Dynamics GP has its own company database. As information such as transactions, accounts, and customer or vendor data is entered through GP, this information is recorded in individual fields. These fields comprise the smallest unit of data stored. All of this data makes up a record, and a record is grouped with similar records and stored in a table.

For obvious reasons, this data is segmented by a company database so that each company can maintain unique records. In addition to this transactional data, numerous additional company setups exist in the company database. As with the System database, we may need to report on some of these company system setups.

The following is a quick reference to the more common company setup tables:

• Account Formats: This stores the chart of accounts format for the company.
• Posting Definitions: This stores how the individual modules post to the General Ledger.
• Company Locations: This lists additional addresses for each company.
• Source Document Master and Audit Trail Codes: Every transaction is assigned both a source document and an audit trail code. This table can be used to report on the full description of these codes.
• Shipping Methods Master: This stores the setup details of the shipping methods for the company.
• Payment Terms Master: This stores the setup details of the payment terms created for the company.
• Record Notes Master: This stores all of the record level notes for the particular company.
• Comment Master: This stores predefined comments to be used across multiple series in Dynamics GP.
• Electronic Funds Transfer: This stores the EFT setup information for the company for both Payables and Receivables modules. This includes customer and vendor banking information.
• Period Setup: This stores the fiscal period setup for the company.
• Sales/Purchases Tax Tables: These tables store the tax detail and tax schedule records as well as the tax summary amounts.

Dynamics GP table naming/numbering conventions

Dynamics GP has a rather interesting and sometimes challenging table naming structure. When developers or consultants first see this, they are overwhelmed to say the least. Because Dynamics GP is actually a collection of modules, some of which were developed by outside organizations and later assimilated into the core product, we will find that even the standard table naming and numbering do not always apply depending on which module contains the data we need. Nevertheless, by learning the standard structure and naming convention of the core modules, we will notice that it does make some sense. With this knowledge in hand, as well as some of the resources we will cover later in this article, we will even have a head start on understanding where data resides in tables underlying non-core GP modules that might not follow the standard naming convention.

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:

• Technical name
• Display name
• 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 following figure and the following sections of this article, 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:

Prefix	Module	Prefix	Module
AA	Analytical Accounting	MRP	Material Requirements Planning
AF	Advanced Financials	MXLS	Audit Trails/Electronic Signatures
ASI	SmartList	NLB	Navigation List Builder
BM	Bill of Materials (Mfg)	OC	Sales Configurator (Mfg)
CFM	Cash Flow Management	OSRC	Outsourcing (Mfg)
CLM	Certification Manager	PA	Project Accounting
CM	Checkbook Master	PDK	Personal Data Keeper (Proj. Acct.)
CN	Collections Management	PM	Payables Management
CP	Capacity Requirements Planning (Mfg)	POP	Purchase Order Processing
DD	Direct Deposit	PP	Revenue Expense Deferrals
EC	Engineering Change Management (Mfg)	QA	Quality Assurance (Mfg)
ERB	Excel Report Builder	RM	Receivables Management
EXT	Extender	RT	Routings (Mfg)
FA	Fixed Assets	SC	Sales Forecasting (Mfg)
GL	General Ledger	SLB	SmartList Builder
HR	Human Resources	SOP	Sales Order Processing
ICJC	Job Costing (Mfg)	SVC	Field Service
IV	Inventory	SY	System/Company Setup
IVC	Invoicing (Sales)	UPR	Payroll
MC	Multicurrency	VAT	Intrastat
ME	Electronic Reconcile (EFT)	WC	Work Centers (Mfg)
MOP	Manufacturing Order Processing	WO	Manufacturing Orders

As we can see from the earlier list, in some modules, tables do not share a single common prefix. For example, in the Manufacturing module, 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. Other modules use a similar alpha code prefix for all tables in the module. Consider, for example, the Project Accounting module where all project transactions, billing, and revenue recognition tables are represented with the same alpha code. As we said earlier, not all modules will follow the standard naming convention, and this is no different when it comes to alpha prefixes for table names. With experience, we will come to learn which modules have a consistent alpha prefix and which ones are broken down even further into more granular prefixes.

Table type

In addition to knowing the module in which our data is stored, we must also know a bit about the kind of data which we are looking for. Let’s take a look at the various types of data that exist in GP tables:

Setup Tables

Almost all modules in GP have setup 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 Tables

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 Tables

These tables contain the transaction-level data from Dynamics GP. These range from 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. The three transaction tables are as follows:

• Work: Unposted transactions can generally be found in work tables. The name is appropriate as these transactions can be considered as “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 receivable 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 Tables

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.

Other Table types

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. These less commonly used table types are as follows:

• 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 clicks on 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.

Identifying the Table type by the table naming convention

In terms of the physical naming convention for GP tables, the table 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:

Table Type	Number
Master	0
Work	1
Open	2
History	3
Setup	4
Temporary	5
Cross Reference	6
Report Options	7

As we’ve stressed already throughout this article, the numbering scheme used to identify table the type will work fairly well for most modules. Not all modules utilize all table types, so we should not expect to see this consistency among all modules. For example, in the Sales module, sales transactions remain in Work tables (such as SOP10100 and SOP10200) until they are posted, at which point they move to History tables (such as SOP30100 and SOP30200).

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 article, 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 we just saw, 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 that what we have discussed is only a general naming and numbering convention for GP tables in their SQL databases. Unfortunately, as we’ve already illustrated in earlier sections, 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. In just a few moments, we will look at some of the tools and resources that can help us find the right table.