Freigeben über

Finding Table and Field Information in Microsoft Dynamics GP

  • Artikel

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 https://victoriayudin.com/gp-reports/

Mark Polino has Table Reference spreadsheet on his blog. Look at https://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 https://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

File Lists.zip

Comments

  • Anonymous
    October 09, 2008
    David, Amazing collection of information!   Victoria

  • Anonymous
    October 10, 2008
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/10/david-musgrave-on-table-auto-stored.html

  • Anonymous
    October 10, 2008
    Posting from Vaidy Mohan's blog http://vmdyngp.blogspot.com/2008/10/all-about-zdp-resource-info-and-vstools.html

  • Anonymous
    October 10, 2008
    Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2008/10/lots-of-dynamics-gp-table-resources.html

  • Anonymous
    October 10, 2008
    Exceptional post-thanks so much! Chris

  • Anonymous
    October 27, 2008
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/10/often-overlooked-yet-powerful-table.html

  • Anonymous
    October 28, 2008
    New Rule: If you tell me that you can't find table resources for Dynamics GP I will hit you. Well

  • Anonymous
    February 22, 2009
    Posting on Rose Business Systems Blog http://rbsgp.blogspot.com/2009/02/dynamics-gp-top-technical-support.html

  • Anonymous
    May 28, 2009
    So, you're saying no translation from meaningful names to XXNNNNN is anywhere else in the database?  Then where does it come from?

  • Anonymous
    June 01, 2009
    Hi Jack The tables are defined using Dexterity and are stored in the application dictionaries, such as Dynamics.dic.  The table definitions include the Technical Name (the name used be developers), the Display Name (the name shown to users) and the Physical Name (the name used at the database level). The information shown in the Table Resources window or the Support Debugging Tools, Resource Information and Resource Explorer windows are all read directly from the Dynamics.dic using Dexterity commands. It is not stored in any SQL tables. David

  • Anonymous
    July 30, 2009
    Thanks for the great post! I've been trying to find table info for GP Manufacturing without much luck.  If you have any resources on that that you could point me towards,I'd be very greatful! Regards, John

  • Anonymous
    October 17, 2011
    There's a pretty good table reference here: dyndeveloper.com/dynModule.aspx

  • Anonymous
    January 06, 2014
    Where is the information

  • Anonymous
    January 06, 2014
    Hi Sara What information specifically are you looking for? This article does not contain the information, but gives multiple methods for you to find the information. Thanks David

  • Anonymous
    March 04, 2016
    I am trying to create a form trigger into the SRVCADV.DIC dictionary to access data from a form that displays in GP as "Contract Entry/Update". I am using 949 which I understand as being the dictionary number for Field Service. I can't find this forms name. Have I the correct number for this dictionary, the correct dictionary, and form name? I am getting the warning message that the trigger didn't register. Thanks!{Name: Startup}local integer result;result = Trigger_RegisterFormByName(949, "SVC_Contract_Maintenance", "Contract Maintenance","", script Open_SVC_Contract_Maintenance_Info);if result SY_NOERR then warning "SVC_Contract_Maintenance Form trigger registration failed.";end if;

    • Anonymous
      March 04, 2016
      Hi GeorgeLook in the Dynamics.set to confirm the product ID for the WennSoft dictionary.To look at the 3rd party dictionary easily create a combined dictionary (https://support.microsoft.com/en-au/kb/930350)You can also use GP Power Tools' Resource Information to easily identify dictionary resources in any dictionary.Add +str(result) to your warning so you can see the error number.Also note that Cross dictionary code can ONLY work in runtime mode. you should make sure you have code to check if you are in runtime mode and that the product is installed before trying to register your trigger. For more info on Cross dictionary development techniques see: https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/07/23/cross-dictionary-dexterity-development/David

  • Anonymous
    September 06, 2016
    I am using SQL Server Management Studio to look at the data in the GP10 tables. Can you please provide a list of diagram of table descriptions? For example, where are the customer orders? Where are the customer mailing addresses? I need this information in order to debug the data.

    • Anonymous
      September 06, 2016
      Hi JamesYou should be able to find this information yourself. The Software Development Kit (SDK) contains Entity-Relationship (E-R) diagrams for the various modules and the methods on this article provide many ways to find the information.Sounds like you are looking for the SOP_HDR_WORK (SOP10100) for unposted and SOP_HDR_HIST (SOP30200) for posted transactions. where SOP Type (SOPTYPE) = 2 for Orders. The ship to address is stored on this header, additional addresses are store in the RM_Customer_MSTR (RM00101) and RM_Customer_MSTR_ADDR (RM00102) tables.Good luckDavid