DYNAMICS database versus company database
The first task of identifying where our data is located is making sure we are using the correct database! Microsoft Dynamics GP 2010 utilizes the Microsoft SQL Server platform as its database engine. When Dynamics GP 2010 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 install. These databases will store all the information entered through the Dynamics GP 2010 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 (DYNAMICS) 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.
What is the DYNAMICS database?
When Microsoft Dynamics GP is first installed and some initial settings are provided, a system database will be created. This database is the DYNAMICS database. It includes such things as records for each company that you create, the organizations registration information, the maximum account framework, and so on.
Tip
Regardless of how many company databases we have, they will all share a single DYNAMICS database.
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 below:
- 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 due 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 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 Includes the companies that each user has access to.
Company database
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 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 DYNAMICS database, we may need to report on some of these company system setups. Here is a quick reference to the more common company setup tables:
- Account Formats Stores the chart of accounts format for the company.
- Posting Definitions Stores how the individual modules post to the General Ledger.
- Company Locations Lists additional addresses for each company.
- Source Document Master & 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 Stores the setup details of the shipping methods for the company.
- Payment Terms Master Stores the setup details of the payment terms created for the company.
- Record Notes Master Stores all of the record level notes for the particular company.
- Comment Master Stores predefined comments to be used across multiple series in Dynamics GP.
- Electronic Funds Transfer Stores the EFT setup information for the company for both Payables and Receivables modules. This includes customer and vendor banking information.
- Period Setup 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.