Data Mining in Microsoft Dynamics GP in Two Easy Steps!

Posted By
BDO

Introduction

Have you ever found yourself wondering how to extract the data you are viewing on a Microsoft Dynamics GP window? Perhaps you want to build a new SmartList, develop a SSRS or Crystal report, a third-party integration, or simply quench your curiosity. If you have found yourself in this position, I urge you to keep reading. In this post, I will outline the steps to identify underlying tables that store the wealth of information seen on Microsoft Dynamics GP windows.

For the purpose of this blog post, I shall be reviewing the Customer Maintenance window. Rest assured, the approach can be applied to absolutely any other window or form in Microsoft Dynamics GP.

Step One

Begin by opening the Customer Maintenance form:  Cards → Sales → Customer.

For example, let’s say I want to find the SQL table that retains the main customer data.

Begin with opening the Table Import window under Tools → Integrate → Table Import.

DynamicsGP-ss1

This will open the window below listing all tables that are referenced on the Customer Maintenance form.

DynamicsGP-ss2

Based on the complexity of the form, you are likely to see a variable number of tables listed in the Table Import window. In our case, Customer Maintenance is a central feature of the SOP and AR modules, hence its setup is stored across a number of tables, all of which are referenced in the window above. The key here is to not get overwhelmed – it’s too soon for that, we are only on step one!

Once the panic and confusion have subsided, simply read through the list of tables in the window. We are looking for something that suggests it is the central table to customer data. While scanning the list, if your glare halts on the RM Customer MSTR table, highlighted in the screenshot above, pat yourself on the back; you are ready to progress on to step two!

Step Two

Now we have identified a table that we are interested in exploring – RM Customer MSTR. But what does this mean? RM Customer MSTR is the identifier that Microsoft Dynamics GP uses for the table, but for complex reporting or development needs, we require the SQL physical table name in order to effectively mine the data. In this step, we utilize the Resource Descriptions to obtain a detailed outline of the RM Customer MSTR table.

Switch the focus back to the Customer Maintenance window to open the Table Descriptions window using the following navigation: Tools → Resource Descriptions → Tables.

DynamicsGP-ss3

This brings up the Table Descriptions window, shown below. Can you sense we are getting closer? No? Ok, let’s keep going.

DynamicsGP-ss4

Click on the expansion button (the ellipsis […] beside the Table field) to open the lookup window seen below.

DynamicsGP-ss5

Start with selecting the right combination of Product and Series. For this scenario, the Product is Microsoft Dynamics GP and the series is Sales. We can then either scroll through the list of tables or utilize the Find option to locate RM Customer Master table.

Side Bar: For third-party applications, locate an entry in the Product list that identifies the solution. It is common to have tables listed under the series 3rd Party. On occasion, additional “searching” may be necessary. Simply go through the list of available dropdown options for different combinations.

Back to step two. When we have found the table we are interested in, click OK to select the table.

DynamicsGP-ss6

The Table Descriptions window now displays a wealth of information affiliated with our selected table – RM Customer MSTR. Here we can see that the physical name of the table is RM0010 along with a list of all fields found in the table.

Double clicking on a field will reveal specific field level information.

DynamicsGP-ss7

We can see above, the Customer Number field is a string of length, 15 characters. Click Close to go back to the Table Descriptions.

The Additional Information button displays relationships with other tables and the nature of these relations (Relationship Type). In SQL-speak, a relationship type of One Record indicates an inner join (one to one) while Multiple Records reflects a left join (one to many).

DynamicsGP-ss8

The Table Usage button displays all forms and reports in which the table is referenced.

DynamicsGP-ss

Conclusion

So there we have it! In two simple steps, we have gathered sufficient information regarding the table central to the Customer Maintenance Microsoft Dynamics GP window. We can now use this information to apply it to any solution as we require.

Confession

I have been using this process for several years now. Over time this has gotten to feel a touch tedious. Having said that, words cannot describe the elation I felt when coming across a new feature released in SmartList Builder 12.00.0070. For more on that, check out my follow-up blog to this post.

Until then, happy mining…

 

This article was originally published on November 24, 2015.

case study childrens wish foundation

Download Now

 

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

Download