Dynamics Community Forum Thread Details

Dynamics GP 2015 Database Schema

I'm working on getting some SQL queries setup to run some custom reports using the GP database. However, I'm finding it very hard to get table definitions and table relations. This makes writing the queries darn near impossible. I've been using the Select Tools >> Resources >> Tables, and use the Table Resource to view tables but this is very time consuming and doesn't show relations. I'm used to working with a full database schema like the one below. I've also found this information (http://dyndeveloper.com/dynModule.aspx), but it doesn't seem to be too helpful. Any information and help with the table structure and relations would be great!

er_5F00_db_5F00_schema_5F00_drupal_5F00_7_5B00_1_5D00_.png

Hi WaterBoy

first off, you should start with the excellent Excel workbook that is available from Mark Polino's web site : mpolino.com/.../table-column-changes-dynamics-gp-2015-microsoft-dynamics-gp-community

Get the Excel workbook under the 'Free Stuff' menu (it was updated for GP2013, but should be 98% the same for 2015. Use the link from the blog entry above to get the changes for 2015 published by Microsoft).

The next tools you should acquire yourself and install in GP 2015 is the newly updated GP Power Tools (ex-SDT) from David Musgrave.. This is an invaluable tool that will help you to quickly identify which resources use what tables in GP and their structures without having to dig in to SQL or the resource description tool from GP.

www.winthropdc.com/products_GPPT.htm

If you already own the Rockton SW GP Toolbox, that includes too a resource inspection tool. RSTB is a little bit different from the GP PowerTools, as it offers more options targeted towards the accounting & system admin side of GP. A little more expensive, but very nice and still affordable tool.

Third, you could tap into the SDK documentation from Microsoft that comes with the DVD. Unfortunately, no all modules are covered by the documentation, but you get pretty much 90% of the main modules. The documentation has a database table schema like your picture above, that shows the relationship between the various tables. Takes 5 min to setup on any computer and doesn't require GP to be installed.

Quick Links