Using The Table Finder Tool In Dynamics GP To Determine Where The Data Resides In The Database

In the past, when troubleshooting data issues in Dynamics GP, it could be difficult to determine where certain data was stored in the database.  Microsoft has gone to great lengths to obfuscate table names.  They’ve done such a great job at this that even after having worked with Dynamics GP for nearly a decade, there are still times where I am left scratching my head wondering where a certain field on the front-end is stored in the database.

Fortunately, this head scratching is now a thing of the past, for the most part.  In more recent versions of GP (2013 and above I believe) Microsoft has included the Table Finder tool, which is available from nearly every window within GP.

The example below will demonstrate using the Table Finder tool on Vendor Maintenance within the Purchasing module.  To access Vendor Maintenance simply navigate to Cards>>Purchasing>>Vendor.

Once the Vendor Maintenance window is displayed click the Tools option at the top and select Table Finder.

Once the Table Finder window opens you will see various options for locating data.  At the very least you will need to have an idea of what you are looking for but in most cases, even if you’re not sure, you can navigate through the list of options to see where the data resides.

The screenshot below illustrates the PM Vendor Master File, which is essentially the data stored in Vendor Maintenance for each Vendor in GP.

As you can see, I have selected PM Vendor Master File under the Tables section.   Once selected, under Table Details, you will see the physical table in the database where the data for that element is stored.  In the example above you can see that Vendor Master data is stored in the PM00200 table. Additionally, a script is also provided which can be copied into SQL Management Studio to query the data directly.

Though the screenshot above shows only a simple SELECT * FROM PM00200, if you were to uncheck various boxes under Fields the script will update to reflect the columns that are still checked.

Another nice feature of the Table Finder tool is the ability to generate SmartList reports, as well as Excel and Navigation Lists, directly from the selected data.  Once you have selected the desired table, simply click the button to create the desired report type.  In the example below I will create a quick SmartList Report off the PM Vendor Master File.

Once you click the button to Create SmartList you will be prompted to provide an ID and Name.

Once the ID and Name have been specified, click Save.

Upon clicking Save, GP will create the new SmartList report and launch SmartList Builder which will allow you to select the Default values to include in the SmartList before saving.

As mentioned above, the Table Finder tool is available from nearly every window within GP.  If you’ve worked with GP in the past you are likely familiar with most of the master tables.  I find this tool to be most useful when really digging down into multi-level windows within GP where data becomes more normalized and multiple tables are referenced from a single window.