Data Mining using Table Finder in Microsoft Dynamics GP

Posted By

For those who came across my original blog piece covering data mining in Microsoft Dynamics GP, you may recall a simple two-step process which, on repeated use, can seem overtly tedious.

Beginning with SmartList Builder 2013, users can breathe a sigh of relief thanks to the Table Finder, one of the eight new features released in the build. In this article we will dig deeper into the wealth of information offered by this handy tool while also highlighting its ease of use.

This may sound familiar to our returning readers; ever found yourself wondering how to extract the data that you are viewing on a Microsoft Dynamics GP Window? Perhaps you want to build a new SmartList, develop an SSRS/ Crystal report, a third party integration or simply quench your curiosity. Stay tuned if your needs fall under any of these categories as I outline the functionality of Table Finder to uncover the collection of tables that drives any Microsoft Dynamics GP Windows.

As with the previous article, the focus here will be on the Customer Maintenance window but the approach extends to all other forms and windows in GP (including third party products.)

The version of Microsoft Dynamics GP we will be using in this exercise is 12.00.1745 (R2) alongside SmartList Builder version 12.00.1722.

Table Finder

Suppose we want to find the SQL tables that store primary customer information seen on the Customer Maintenance window.

Begin by opening the Customer Maintenance form: Cards -> Sales -> Customer

Then navigate to the Table Finder tools option.

Navigate to Table Finder tools

This will open the Table Finder window displaying an array of information pertaining to the Customer Maintenance window.

Table Finder Window

At first site, the volume of information presented can be overwhelming but the trick here is not to panic! Once we have dissected each part of the form, you will be well on your way to building that stellar report.

The top left quadrant displays information regarding the window that invoked table finder, in our case, this is the Customer Maintenance GP window. You are able to manually change the drop down selections to change focus to a different window but it is much easier to launch Table Finder from the specific window in question.

Customer Maintenance Window

Right below that, in the bottom left quadrant, you will find the list of tables referenced in the Customer Maintenance window. Recall Customer Maintenance being a central setup to SOP and AR modules, the information is stored across multiple tables – all of which are listed in this single pane. Take a minute here, once the awe and wonder subsides, we can continue digging deeper.

Tables listSuppose we are looking for data specific to the customer setup. We simply need to scan the list of tables looking for a name that suggests it is the central table to customer data. If your glare halts on the RM Customer MSTR table then we are on the right track – go ahead and select it.

Select RM Customer MSTRYou will notice tha,t upon selecting the RM Customer MSTR table, the right pane updates its display. Let’s review this in detail.

The Table Details provide the technical specifications of the table. These include the SQL table name along with details specific to Dynamics/Dexterity, the Table Name, Technical Name, Product, Series, and Record Count.

Table Details

The Fields portion lists all fields (columns) present in the table. Fields marked with the key icon Key Icon indicate a primary key which can be used to uniquely identify a record in the table. Use the scroll bar to view fields not visible in the default viewport.

SQL Query

Lastly, the cherry on top – in the SQL Script you will find a simple SQL SELECT query that will return all fields and rows for selected table (RM Customer MSTR). You can copy and paste this query to any application you like.

SQL Script QueryThe Preview button Preview button can provide a quick peek at the contents of the table.

Table contents preview

If there ever was a cherry on top of a cherry, then that would be the ribbon at the bottom of Table Finder window. This gives the user the ability to create a setup in SmartList Builder, Excel Report Builder, and Navigation List Builder with the table you have selected.

Table Options in Microsoft Dynamics GPAnd there we have it, all the information we need carefully laid out in one beautiful window. It is ok to get a little emotional here, I may or may not have shed a tear or two. What I can safely say is, I will not be going back to my old primitive ways so long as Table Finder is around.

This is where we cheer!

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing