David MeegoThis is an updated version of an article I wrote a while back for the Microsoft Dynamics GP community website.  The links to the original article, as well as the associated Knowledge Base (KB) article are provided at the bottom of this post.

The idea was to provide as many tools and techniques as possible to help people work with the Microsoft Dynamics GP data model.  Please read all the methods before selecting the ones that work best for you.  To avoid confusion I have left the ten original methods in the same order and added some new techniques at the end of the list.  I have also added links to additional resources and articles.

 

Techniques for Finding Table and Field Information in Microsoft Dynamics GP

To get information about tables and fields in Microsoft Dynamics GP, you can use any of the following methods:

  1. Open the Microsoft Dynamics GP window that contains the data you are interested in, and then select Tools >> Integrate >> Table Import to see the tables associated with the Dexterity Form.
     
  2. Open the Microsoft Dynamics GP window that contains the data you are interested in, and then select Tools >> Customise >> Customise Current Window. Once you’re in Modifier Layout mode, look at the window object properties. Usually the most important table for a form is linked as the AutoLink table. Then close the layout window and look at the Tables tab of the Form Definition window to see the attached tables (these will be the same as shown in method 1).
     
    Note: Using this method can create additional windows in the Modifier that have not actually been modified. You should check whether the window already exists in the Modifier before using this method. That way, you will know whether the window just created can be deleted.
     
  3. Try using SQL logging by adding the following lines into the DEX.INI file:
     
    SQLLogSQLStmt=TRUE
    SQLLogODBCMessages=TRUE
    SQLLogAllODBCMessages=TRUE
     
    Then delete the DEXSQL.LOG just before performing the actions you are interested in and look at the DEXSQL.LOG file immediately after. The following Knowledge Base (KB) article has the details:
     
    How to create a Dexsql.log file for Microsoft Dynamics GP (KB 850996) Secure Link
     

  4. Load the SDK (Software Developer's Kit). The kit contains transaction flow documents that show which tables to use for specific transactions. It also contains PDF E-R (Entity-Relationship) diagrams that show the tables and their relationships.

    Links for the SDK can be found on the Developer Articles & Links page.

  5. Select Tools >> Resources >> Tables, and use the Table Resource window to look up tables.

  6. Use a tool such as SnapShot for Microsoft Dynamics GP. This tool can display and export information about tables. However, its main function is to provide platform and account framework independent data transfer, backup, and migration. 

  7. Load Dexterity (from the Tools folder on the second Microsoft Dynamics GP CD), open the dictionary, and look at the form definition to get the attached tables. This will be the same list as provided in methods 1 and 2.
     
  8. Open the window and print the associated report to the screen. Next select Tools >> Customise >> Modify Current Report. Then look at the tables attached to the report from the Report Definition.
     
    Note: Using this method can create additional reports in the Report Writer that have not actually been modified. You should check whether the report already exists in the Report Writer before using this method. That way, you will know whether the window can be deleted.
     
  9. This is probably the most powerful of all the methods listed and is normally available only to the Dexterity developer. Add the following lines into the DEX.INI file in the application folder to turn Debug mode on. (Please do not use these settings for live systems.)
     
    ScriptDebugger=TRUE
    ScriptDebuggerProduct=0
     
    The zero represents the product ID for Microsoft Dynamics GP (as shown in the DYNAMICS.SET launch file). If you are interested in another product, you can use the product ID for that product.
     
    Launch Microsoft Dynamics GP. You should now see a Debug menu on the right side of the menu bar. Get to where you want to start logging and profiling. Select Debug >> Profile Scripts, Debug >> Clear Profile, Debug >> Log Scripts and select a filename. Then perform the actions you want to log. Next select Debug >> Log Scripts to stop the logging, Debug >> Save Profile to save the profile, and Debug >> Profile Scripts to turn off profiling.
     
    Now look at the script log and the script profile files. The script log shows all the Dexterity calls with their parameters and hierarchy. The script profile shows you the scripts called, how many times they were called, and how much time was spent inside the call. Here is the trick: The bottom half of the script profile shows all the tables that were touched and what actions took place.
     
    Note: This step only logs Dexterity-based table actions. If a stored procedure is called, Dexterity cannot see what is happening. Therefore, Dexterity will not log those table actions.  The following KB articles explain this in more detail:
     
    Using the Script Debugger in runtime (KB 850487) Secure Link

    How to use the Dexterity Script Debugger to trace bugs and performance issues in Microsoft Dynamics GP (KB 910982) Secure Link
     
  10. You can turn on SQL Profiling from SQL Server Enterprise Manager (SQL 2000) or SQL Server Management Studio (SQL 2005) to trace what actions Microsoft SQL Server is performing.
     
    How to create a SQL Trace with Profiler on Microsoft SQL Server 2000 (KB 857246) Secure Link

    How to use SQL Profiler to create an SQL trace in Microsoft SQL Server 2005 (KB 912281) Secure Link
     
  11. * Install the Support Debugging Tool for Microsoft Dynamics GP. Once installed, open the tool (Microsoft Dynamics GP >> Tools >> Support Debugging Tool). Then use the Manual Logging facility to capture the DEXSQL.LOG, Dexterity Script Log and Dexterity Script profile in one step without needing to change the DEXI.INI file.  This replaces methods 3 and 9 with a single mouse click.
     
  12. * Install the Support Debugging Tool for Microsoft Dynamics GP. Once installed, open the tool (Microsoft Dynamics GP >> Tools >> Support Debugging Tool). Then use the Resource Information window (Options >> Resource Information) to lookup resources.  You can change the Resource Type to Tables & Fields. Then you can use the lookup button to open the Resource Explorer window (re-used from Field Level Security) to browse the tables from all installed products/dictionaries.

    Note: In the Resource Explorer window use the Show Expanded Fields checkbox to display the individual fields of composite and/or array fields.

    You can also type in a table's Physical Name or a field's Physical Name to have the Resource Information window identify the resource and provide the Technical Name, etc.
     
  13. * Use the DEX.INI settings in method 9 to activate the Debug menu in runtime mode. Once Debug menu is showing, press Ctrl-F1.  Your mouse cursor will change to show that you are in debug selection mode. You can press Ctrl-F1 again or Esc to cancel debug selection mode.  While in debug selection mode, click on a field a window and the Open Script window will open.  This window will allow you to identify the technical names of the field, window and form selected. Press Cancel to close the Open Script window. 

    Note: Fields with (L) as a prefix are local fields and cannot be stored directly in tables.
      
  14. * As a final option, you can use the file lists attached to this post or the other resources below.

* = New methods for this posting. 

 

Additional Resources 

Originally published at Finding Table and Field Information in Microsoft Dynamics GP and also as the following KB article:

How to obtain the table and field information for windows in Microsoft Dynamics GP (KB 894335) Secure Link

 

Victoria Yudin has provided table information for each of the series in the product. Look at http://victoriayudin.com/gp-reports/

Mark Polino has Table Reference spreadsheet on his blog. Look at http://msdynamicsgp.blogspot.com/, look at GP Downloads section on the right hand side.

Steve Gray on VSToolsForum.com has provided a number of articles on this blog. Look at http://vstoolsforum.com/, look at the Resources section for Dynamics GP Table Structures.

 

File Lists for all files in all products for v9.0 & v10.0 are attached at the bottom of the article.

Please post a comment and let me know if this article is useful to you.

David