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!

4 Responses to “New resource to find Dynamics GP table and field information”

  1. Hi Victoria,

    Thanks for posting the details of how to use it!

    Leslie

    Like

Trackbacks/Pingbacks

  1. Dynamics GP - Learn and Discuss - July 23, 2010

    New Resource to find Tables & Fields Information – Victoria Yudin…

    Victoria points to us a new way of easily finding Table & Field Information in GP . And this also…

    Like

  2. New Resource to find Tables & Fields Information - Victoria Yudin - Microsoft Dynamics GP Learn & Discuss - July 23, 2010

    [...] Resource to find Tables & Fields Information – Victoria Yudin Victoria points to us a new way of easily finding Table & Field Information in GP.And this also gives us a strong reason of why we all should use this wonder tool called Support [...]

    Like

  3. Microsoft Dynamics GP Learn & Discuss - July 23, 2010

    New Resource to find Tables & Fields Information – Victoria Yudin…

    Victoria points to us a new way of easily finding Table & Field Information in GP . And this also…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,545 other followers

%d bloggers like this: