Locating Dynamics GP data with additional tools
Earlier in this chapter, we discussed 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.
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.
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.
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.
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 chapter.
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 chapter, 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!