New resource to find Dynamics GP table and field information

Since I have posted a lot of Dynamics GP table information and SQL code, I often get asked the question “what table holds ____ “? Sometimes I just know the answer off the top of my head (yeah, I think that’s scary, too), but once in a while I have to look it up. I have various ways of doing this, sometimes it involves glancing through the list of tables, other times I have used the Support Debugging Tool created by David Musgrave. 

Now there is another great resource for this provided to us again by David Musgrave. You can read all about it in David’s post on Getting Table and Field Data out of Dexterity Dictionaries, however I thought I would take you through an example of how to use this. This example assumes that you have already installed the Support Debugging Tool - if you have not, that’s your first step. Now to our example (I am using GP 2010 for this, but it should work the same for GP 10.0):

• Log into GP as ‘sa’. (Yes, there are workarounds to allow not having to use ‘sa’, but that’s not what this post is about.)
• Open the Support Debugging Tool by clicking Ctrl+D or navigating to GP > Tools > Support Debugging Tool.
• If you have not done so yet, turn on the Advanced Mode features by going to Options > Dex.ini Settings and checking the box for Enable Debugger Advanced Mode Features towards the bottom of the window:
• Click OK to get back to the main Support Debugging Tool window.
• Go to Options > Runtime Execute to open the Runtime Execute window. (If you have a System Password setup, and everyone should, you will need to type it in.)
• Copy and paste the code from David’s post and change the Product to Microsoft Dynamics GP:
• Click Execute in the bottom right corner.
• You will get a pop up window letting you know that two files called TABLES.TXT and FIELDS.TXT are about to be created in your GP\Data folder. Click Yes to continue. Once done you will get a message letting you know how many tables and fields were exported:
• If you want to save this code for use in the future, enter a Script ID and Script Name at the top of the Runtime Execute window and click Save. Otherwise, you can just close this window and choose to Discard your changes.
• Now you have two text files with your data. The easiest thing to do is open Excel and open one of the files, this will automatically open the Text Import Wizard. In my experience you can just click Finish on the very first step and Excel will do the rest.
• Repeat the process for the second file, save them in Excel format and now you have searchable files of the tables and fields for your GP installation readily available.
• David has provided samples of the TABLES.TXT and FIELDS.TXT files in his post so that you can take a look at what kind of data you’d be getting. One huge benefit of going through the steps above instead of just using the samples provided is that any ISV products you have installed will be included when you run this on your system.

I would like to thank David for making this available to all of us!