Where Is My Data and How Do I Get to It?

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics GP 2013 Reporting - Second Edition

Microsoft Dynamics GP 2013 Reporting - Second Edition — Save 50%

Create valuable insights for your organization with Microsoft Dynamics GP 2013 reporting tools with this book and ebook

$35.99    $18.00
by Chris Backhouse Christopher Liley David Duncan | October 2013 | Enterprise Articles Microsoft

This Article, by David Duncan and Christopher Liley, authors of Microsoft Dynamics GP 2013 Reporting, Second Edition, explains that, as a developer or consultant who has been assigned the task of filling user requests for reports should be aware that ultimately the very first question after deciding on the reporting tool is, "Where is my data and how do I get to it?"

Knowing where to begin is a critical first step in the development process. The aim of this article is to provide helpful tips for finding and locating data in the Dynamics GP 2013 ERP system and company databases. Although we'll discuss some reporting tools that do not require us to know the SQL database structure for Dynamics GP companies, it is still helpful to understand how GP stores its data.

In this article, we will discuss the following:

  • Differences between the system database and company databases
  • Conventions that are helpful to know and understand when it comes to Microsoft Dynamics GP 2013 data and how it is stored
  • Using Resource Descriptions as a tool for finding data from within GP 2013
  • Utilizing additional tools, such as the GP 2013 SDK and Support Debugging Tool, to find our data

(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.

Microsoft Dynamics GP 2013 Reporting - Second Edition Create valuable insights for your organization with Microsoft Dynamics GP 2013 reporting tools with this book and ebook
Published: October 2013
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Locating Dynamics GP data using the Resource Descriptions windows

One of the most useful tools for locating data when being tasked with writing reports against Dynamics GP data is the Resource Descriptions tool within the application itself. Resource Descriptions are broken into three distinct windows: Tables, Windows, and Fields. Typically, we find ourselves using a combination of these three windows to locate the specific data we are looking for. There is not really a right or a wrong way to use these windows. Each report developer or consultant could argue that the way he or she uses the windows is the correct way; but ultimately, as long as we are able to identify and find the data we need, we will be that much closer to creating an accurate report that fills our users' needs.

The various Resource Descriptions windows are located by navigating to Microsoft Dynamics GP | Tools | Resource Descriptions.

Tables

Table Descriptions let us select a Product (Microsoft Dynamics GP, Fixed Assets, and so on) as well as the Series (Financial, Sales, and so on). We are then provided with a list of all the tables for the selected product and series. By default, the list of tables is by Display Name. We do have the option to change how the tables are sorted by changing the View By field. The options are Table Display Name, Table Group Technical Name, Table Physical Name, or Table Technical Name. The method we choose will depend on how familiar we are with the table structure. For example, we might select Table Physical Name once we are familiar with the naming convention guidelines provided earlier in this article. There is also a Find button that allows us to search for the table based on any one of its three names.

To access the Table Descriptions, follow these steps:

  1. Open Microsoft Dynamics GP.
  2. Click on the Microsoft Dynamics GP Menu from the toolbar.
  3. Select Tools.
  4. Select Resource Descriptions.
  5. Select Tables.
  6. Click on the Ellipses button and find your table. In the following screenshot, we are searching for the PM Vendor Master File table, which is found under the Microsoft Dynamics GP Product and Purchasing Series.

Once we have found the table we are looking for, we can drill into that table and get additional information for that table. This view includes all of the fields in the selected table, their physical names (as would be seen in the SQL tables), the storage type of the field, and the position.

We can access this additional information by double clicking on the table record in the Table Names window. This opens a new window called Table Descriptions; as shown in the following screenshot:

From the Table Descriptions window, we can further drill into each field and get information such as the Format Type, the Keyable Length of the field, and if applicable, any Static Values if it is a dropdown field.

We can access this detail by double-clicking on a field, or selecting the field and clicking on the Field Info button to open the Additional Field Information window, as shown in the following screenshot:

Returning to the Table Descriptions window, the last pieces of information we can access from this window are additional info such as any secondary tables, the secondary tables' keys, what the related fields are for the tables, and what the relationship type is. We can also see the usage, which gives us all the forms and reports that use this table.

Most of this information can be found by returning to the Table Descriptions window and clicking on the Additional Info button. This opens the Additional Table Information window as seen in the following screenshot:

Also, by clicking on the Usage button from the Table Descriptions window seen in an earlier screenshot, we can access the Table Usage window as seen the following screenshot by clicking on the Usage button:

Typically, we use Table Descriptions when we know the name of the table needed and we need to find additional information about that table.

Fields

The Field Descriptions window gives us the ability to again select both the Product and the Core (Series), which contains the field we are looking for. Once both of these fields are selected, we are provided with the field list. Similar to how we could look at field information in Table Descriptions, we can look at field information here as well.

To access Field Descriptions, follow these steps:

  1. Open Microsoft Dynamics GP.
  2. Click on the Microsoft Dynamics GP Menu from the toolbar.
  3. Select Tools.
  4. Select Resource Descriptions.
  5. Select Fields.
  6. Select a Product and a Core.

For example, in the following screenshot, Microsoft Dynamics GP has been selected as the Product, and System has been selected as the Core value. This shows us a list of fields that meet this criteria and a list of the tables that contain the selected field:

The one added benefit of using this window over others accessible via Resource Descriptions is that once we select the field we need, a list will be provided showing us all the tables that contain that field.

This window is typically used when we are not sure exactly which table(s) a requested field resides in. We can use this window to tell us all the table display names that contain the selected field, then we can go to Table Descriptions and find the physical (SQL) table name based on the results returned in the Field Descriptions window.

Windows

Windows Descriptions provides us with the ability to view the windows where the data is being recorded or viewed. As with the other Resource Description windows, we select the Product and Series. Products relate to the application code dictionaries, that is, Microsoft Dynamics GP, Fixed Assets, and so on. Series relate to the series the modules reside in, for instance, General Ledger and Bank Reconciliation reside in the Financial Series. Once we locate the window we are looking for, Purchase Order Entry for example, we will be provided with a list of both the fields in that window and the tables used by the window. We can also use the Form name in this window to find the related fields and tables.

To access Window Descriptions, follow these steps:

  1. Open Microsoft Dynamics GP.
  2. Click on the Microsoft Dynamics GP Menu from the toolbar.
  3. Select Tools.
  4. Select Resource Descriptions.
  5. Select Windows.
  6. Select a Product, Series, and View By from their dropdown lists.

As the following screenshot shows, selecting Microsoft Dynamics GP as the Product, Purchasing as the Series, and by Window Display Name as the View By option presents us with a list of all windows available in this unique combination:

We typically use Window Descriptions when we know where the requested information is being entered or displayed in Dynamics GP but we are unsure of the field name(s) or the table name(s) used to store the data. As with Field Descriptions, once we have a list of possibilities, we can cross-reference to Table Descriptions.

The Table Descriptions window is a bit constrained in size, and when dealing with a Product and Series with numerous tables, it can often be time-consuming to scroll through the list to find the right window. Don't neglect the Find button in the upper-right hand corner of this window. This works well, assuming we have already made our Product and Series selection in the appropriate drop-down boxes. After selecting this button to open the Find window, we can type in the Display Name we are looking for and the window will auto-focus on this row in the scrolling window. Remember, the Display Name corresponds to the name of the window as it appears in the menu bar attached at the top of the window in GP.

The Table Import utility

Another very useful resource for finding the data that we need to begin writing our reports is the Table Import utility. From any window in Dynamics GP, we can open the table import utility and be provided with all the friendly table display names that are used by this window. For example, opening the Table Import window from the Sales Transaction Entry window yields the following information:

To access the Table Import from any window, follow these steps:

  1. Open Microsoft Dynamics GP.
  2. Open the Dynamics GP Window you are looking for.
  3. Select Tools.
  4. Select Integrate.
  5. Select Table Import.

Once we know which table we want to go find our data in, we can again cross-reference the Table Resource descriptions to find the physical SQL table name. This utility is very useful if the user tells you that they need a report that includes information they are entering on a particular screen.

Microsoft Dynamics GP 2013 Reporting - Second Edition Create valuable insights for your organization with Microsoft Dynamics GP 2013 reporting tools with this book and ebook
Published: October 2013
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Accessing data at the table level using SQL Management Studio

Once we have identified our physical table name, we can begin to use SQL Management Studio to view the data in the table. To accomplish this, we can write simple select statements to view the data. This can be very useful, as it allows us to see actual data in the underlying table. So, if a user tells us they need information they are entering in Sales User defined fields, we can view that data directly in the table to be sure we are capturing the requested data. A helpful tip for this is to open the Sales User-Defined Fields Entry window in Dynamics GP for a specific document number. Then, in SQL Management Studio, we can select the record from the table (in this case, the SOP10106 table) that equals the same document number we are looking at in the screen. This allows us to ensure we are looking at the correct data.

One thing to keep in mind if we are working on a production database or even a test database with large amounts of data is that we can use commands, such as NOLOCK, TOP 1, TOP 10, to keep us from putting inadvertent locks on the table and to limit the amount of data returned by our queries. We don't necessarily need to select all records from a Sales Transaction table to see the data in that table and begin writing our report. It is much more efficient to use small selections of data at this point in our data gathering.

A sample of these TSQL statements can be seen in the following screenshot:

In SQL Management Studio, there is another very useful command that we have at our disposal. This command is called SP help and is very easy to use. We simply open a new query window and type in sp_help and our table name (sp_help SOP10100). This will return information such as the owner of the table and the created data, along with all of the columns in the table, their names, types, whether they are computed, their length, and if they are nullable amongst other things. We will also be provided with the indexes on the table. All of this information can be helpful to us when we begin writing our reports.

Locating Dynamics GP data with additional tools

A logical way for finding data for our report by starting at the database level and working our way down to the field level within the SQL database tables. While this approach utilized the GP naming and numbering convention, as well as the Resource Descriptions and Table Import tools within Dynamics GP, several other tools exist outside of the standard GP application that can help us better understand where our data might be located.

Dynamics GP 2013 Software Development Kit

One useful tool for report writers and developers to use when trying to determine which tables should be used in building a report is the Software Development Kit (SDK), for short. Unlike the Resource Description windows, this tool resides outside of Dynamics GP and requires a separate download. This means that you don't need a workstation installation of Dynamics GP to use this tool, nor does it require you to consume a GP user license when accessing the information it contains.

Downloading the Software Development Kit

New releases of the SDK are timed to coincide with new releases of the Dynamics GP application. For Dynamics GP 2013, the SDK can be installed from the original Dynamics GP 2013 CD software. Look under the Tools folder of the install media folder for the core GP and other SDK installation executables.

On PartnerSource, however, several additional SDKs can usually be found, many of which contain information related to additional products and tools that can be integrated with the core Dynamics GP application. Although the GP 2013 Product Release page has not been updated with any of these additional SDKs at the time of writing this many of them are still available on the GP 2010 Product Release page. For example, users can find SDKs for Business Portal 5.0 for GP 2010, as well as for eConnect for GP 2010 on the GP 2010 Product Releases page. Our focus will be on the SDK designed for the core application.

Run the installation executable to begin the SDK installation. Accepting defaults will install the program so that it can be opened from the Microsoft Dynamics folder under the Start menu.

Using the Software Development Kit

The Software Development Kit is broken up into several sections containing information related to customizing Dynamics GP 2013 and working with the various database objects that exist in each company's SQL database. Among these sections, we will find the following:

  • Table Integration, Database Diagrams: This section provides diagrams of tables related to the selected module. While information about specific fields is not found in this diagram, this documentation can be helpful in displaying a list of tables related to a specific module.
  • Table Integration, Design Documents, and Transaction Flows: Documents in this section describe how data flows from one table to the next as transactions are conducted in GP.
  • Additional Products: Additional modules, such as Manufacturing and Project Accounting, are not included with the information described in the preceding sections. Check this section to find documentation describing the tables and fields related to these modules.

We can use the SDK when we want to get a better understanding of how data flows through the various tables. Data in GP flows through many SQL tables as transactions are conducted in the overlying GP application. Often, we are asked to develop reports that capture data at a certain point in the transactional process, and we can use the SDK to help us determine where to locate this data.

The Support Debugging Tool

The Support Debugging Tool is another valuable tool to have in our toolbox as we seek out our data in GP and its accompanying SQL databases. Over the last few versions of Dynamics GP, the Support Debugging Tool has quickly become the go-to tool for IT departments and Help Desks for debugging GP issues. Although the Support Debugging tool offers a wide range of functionality designed for debugging issues, we can use several components of the Support Debugging Tool during the report writing and development process.

Downloading the Support Debugging Tool

Although the Support Debugging Tool has been around for several iterations, the latest version, Build 17, was timed to coincide with the release of Dynamics GP 2013. This tool, primarily developed by David Musgrave of the Microsoft Dynamics GP Asia Pacific Support team, is a free tool but it can only be downloaded by Microsoft Partners via PartnerSource.

For a list of resources and links related to the Support Debugging Tool, check out the "Support Debugging Tool Portal" at the following link: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx

After downloading the tool, the install process requires users to copy and paste a chunk (.cnk) file (which is a self-extracting data dictionary file that is used to distribute customizations and third party products) to the Dynamics GP root directory (which is usually located at C:\Program Files (x86)\Microsoft Dynamics\GP2013), launching Dynamics GP, and selecting to include the new code when prompted. Upon installation, the 'sa' user will need to log into GP 2013 and grant the appropriate security rights as only members of POWERUSER will have access by default. After this, the tool is accessible from the GP application via the Tools menu.

Using the Support Debugging Tool

The primary Support Debugging Tool component that we will explore is the Resource Information window. This can be accessed from the Support Debugging Tool by navigating to Options | Resource Information from the navigation menus. This functionality is an extended version of the Resource Descriptions window that we covered earlier in this article.

Like the Resource Descriptions window, this tool provides insight into display, technical, and physical names for all windows, tables, and fields in the Dynamics GP application. Unlike the Resource Descriptions window, however, this tool combines three windows (Window Descriptions, Table Descriptions, and Field Descriptions) into one! Additionally, we can come to the Resource Information window with limited information and the Support Debugging Tool will fill in the rest for us!

Let's use an example to consider one way in which this tool can be used to find our data.

Suppose we want to include the Text Field 1 field from the Sales User-Defined Fields Entry window (Transactions | Sales | Sales Transaction Entry | User-Defined button) in our report:

From the Support Debugging tool, Debugger menu, we can open the Resource Information window and select Forms, Windows & Fields in the Resource Type drop down. Near the bottom of the window, we can type the Display Name of the window for which we need more information.

Remember, the display name of a window is the name as it appears at the top of the window while it is open in GP. For example, in our earlier screenshot, we see the display name of the window shown in the blue bar at the top of the window. In the following screenshot, we have entered this display name (Sales User-Defined Fields Entry) in the Display Name field under the Form, Report or Table Information heading and are about to tab off the field to reveal additional information about this window:

After we tab off of the Display Name field, the Associated Tables button becomes available and the Support Debugging Tool auto-populates the Technical and Display names for this form in the Form, Report or Table Information. Selecting the Associated Tables button opens a window displaying information about SQL tables containing data used by this window. This is similar to using the Table Import trick that we discussed earlier in this article, but as the following image shows, this method provides more detailed information for the user:

Back on the Resource Information screen, with the technical and display name information displayed, we can select the Lookup button beside the Technical Name field in the Form, Report or Table Information grouping to open the Resource Explorer for this form. This allows us to select the Main form, and shows us a list of all of the fields in this window. We can scroll through this list until we find the field that corresponds to the one we are looking for. In this case, the name does not exactly match what appears in the window in GP, but User Defined Prompt 1, as seen selected in the following image, is close enough to the User Defined Entry window and the Text Field 1 that we know we have found the right field.

Double-clicking on the line containing our field causes the Resource Information window to appear again, this time with the Field Information fields populated. Additionally, we notice that the button for Tables Containing Field at the bottom right-hand corner of this window can be selected. Selecting this button shows us a list of SQL database tables that contain a field called USERDEF1:

Of course, numerous tables contain a field as generically named as USERDEF1, but we can easily scroll through this list and determine that the table that we are probably looking for is the Sales-User Defined Work History table or SOP10106.

By using the Support Debugging Tool we can significantly reduce the amount of time spent searching for our data. The Support Debugging Tool allows us to begin with a limited amount of data as well as it is a great tool to use as we initially set out on our quest for data. If you haven't tried out the Support Debugging Tool yet…what are you waiting for? Check with your Microsoft Partner for more information!

Summary

In this article, we discussed both various ways to identify the data we need to create our reports as well as the various tools that assist us with this task. Once the challenges presented have been identified, the next logical step is finding our data. Keep in mind that although some reporting tools do not require a pre-knowledge of the SQL database or GP company database structure, we can still use our knowledge of GP data structures to build better reports.

To that end, we've used this article to explore some of the conventions surrounding how GP data is stored. We began at the database level by exploring the differences between the system and company databases, including the new enhancements to naming system databases in GP 2013. Then we moved on to understanding the naming and numbering convention for tables found within these databases. At first glance, the numbering convention used for GP tables can be confusing, but by this point, we are familiar with some general naming conventions that, can help us in most cases.

Finally, we looked at some tools that can be used for finding this data. Some tools, such as Table Resources, Table Import, and SQL stored procedures can be used with any standard install of GP. Other tools, such as the Software Development Kit for Dynamics GP 2013 and the Support Debugging Tool can also be used with GP data, however they require separate installations.

Resources for Article:


Further resources on this subject:


About the Author :


Christopher Liley

Chris Liley is a Principal Consultant with Microsoft Partner I.B.I.S., Inc.in Norcross, GA and a Microsoft Certified Information Technology Professional for Dynamics GP.  He is a graduate of Georgia State University with a B.B.A in Accounting.  Chris has worked with Dynamics GP since 2001.

Chris’ experience ranges from financial analysis, software implementations, data conversions, integrations to designing and developing customizations in both the functional and technical area of consulting for Dynamics GP.  Chris also has extensive experience designing Business Intelligence solutions.

David Duncan

David Duncan has been a consultant and business analyst for over five years. He has driven organizational success through well-defined reporting solutions that provide valuable and insightful information to key stakeholders. He is the co-author of Microsoft Dynamics GP 2010 Reporting and has also served as a technical reviewer for Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013, both by Packt Publishing. David, who holds several certifications for Microsoft Dynamics GP and SQL Server, has extensive experience in designing and providing business intelligence and reporting tools for organizations that use Dynamics GP and many other Microsoft SQL Server-based applications.

In his current position as a business analyst, David works with end users at all levels of his organization to provide them with reporting solutions to meet their needs. David enjoys sharing insights gained from these solutions, and he is even happier when he can share these insights using Excel PivotTable and Excel Power Pivot functionality!

David holds a degree from Clemson University. He resides in Rocky Mount, N.C. with his wife, Mary Kathleen, and their newborn daughter, Mary Eliza.

Books From Packt


Developing SSRS Reports for Dynamics AX
Developing SSRS Reports for Dynamics AX

Microsoft Dynamics GP 2010 Implementation
Microsoft Dynamics GP 2010 Implementation

iReport 3.7
iReport 3.7

Microsoft Dynamics GP 2013 Cookbook
Microsoft Dynamics GP 2013 Cookbook

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Microsoft Dynamics GP 2013 Implementation
Microsoft Dynamics GP 2013 Implementation

Developing Microsoft Dynamics GP Business Applications
Developing Microsoft Dynamics GP Business Applications

Microsoft Dynamics NAV 2009: Professional Reporting
Microsoft Dynamics NAV 2009: Professional Reporting


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
6
t
i
g
y
N
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software