Home > General - Builders > Finding GP Tables – the basics

Finding GP Tables – the basics

Here is part of a recent post from the Suggestions /Questions  page:

“I understand how the builder works but can’t figure out which tables to connect. I can find ways to see part of the information but not all. ” 

The writer continues to talk about what exactly she is trying to get.  Now I debated this… her request was pretty easy, I could have just replied with the table names and link fields.  BUT, I strongly believe the in the theory of “to teach a man to fish”   And hey, this is a blog…

So, sorry Sandra, I have decided to make your question the subject of two posts. This post will focus on tables in general.  The next post on the type of table you are looking to link – distributions table – which, since GP IS an accounting software, deserve a separate discussion.

First suggestion is a trick for GP10 and above.  I didn’t find this — a co-worker (thanks Coleen) found it when she was new!  

  • Go to the window in GP that displays the data you are trying to find.  (Make this the simplest window that contains this data.  Don’t go to an inquiry window with tons of other data!)  Also make sure you are on the same type of transactions (Work, open or history)
  • On the top menu of the window select Tools >>Integrate >>Table Import
  • You will see a screen with the list of tables used on this window!

OK – so now you have a list of table names.  Ok actually they are Display Names…  It is important to mention that GP doesn’t have just one name for each table. They have 3 different names:

  • Display Name – this is the name we work with by default in the Builders
  • Technical name – this is what is actually used by Report Writer and other programing languages.  It doesn’t have spaces it uses _ instead
  • Physical name – this is the name in SQL.  Most blogs refer to this table name since most blogs are for SQL or VB programmers.  

Now you can go check out this list of tables under Tools >> Resources >> Tables.   Here is a great write-up on using this window: Table Resources  Notice the Product & Series just like we need when adding tables in GP.  Remember you can see all of the field data here too.

I actually often look for the Physical name on this screen – since there is a logic to the Physical names as explained by Leslie Vail’s post  GP Table names – Physical or SQL names  (And if I totally lost you above when I mentioned Work, Open and History – make sure you read her post: Moving from Work to Open to History – or to just get a refresher by module!)   

You will also learn that there are certain naming conventions within the other name types too.  SQL master tables often have MSTR  or Master in their other names too. (The problem is that these other name types tend to be a little more “flexible” with the naming conventions!)

Advanced techniques for the programmers and the brave :-)   (many of these tools standard GP users do not have access to.. but if you do – go for it!) David Musgrave’s  Finding table and field names

Now – as for link fields – I am going to refer you back to my post: Adding additional tables to your SmartList (Successfully)

That should be enough to get you started…  I think I will need to do a follow-up on tables for other products (HR, Project, etc…)  but that is a subject for another day.

Happy Building,
Devon

07/30/2010 – Author’s Note – take a minute to also review the other links and resources from our friends on the Blogging World in the comment section. 

Most resources the merrier I say!   Thanks everyone for your contributions & additions.

  1. July 28, 2010 at 12:41 am

    Hi Devon

    Thanks for highlighting my blog post.

    Have you tried using the Support Debugging Tool’s Resource Information window.

    If you are in Form mode and select a field on a window on a form. You can then click on the Associated Tables button to see the list of associated tables filtered for tables containing the selected field. This normally narrows the tables down to one or two that contain the data you are looking for. Selecting the table provides all the technical and physical names for table and columns so you can access the data from Smartlist Builder or SQL Server.

    This whole process takes less than a minute and has a nice user interface.

    Try it out.

    http://blogs.msdn.com/developingfordynamicsgp/pages/support-debugging-tool.aspx

    David

    PS: Also see how to get all the table and field data out of your system.
    http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/07/07/getting-table-and-field-data-out-of-dexterity-dictionaries.aspx

    • July 28, 2010 at 8:21 am

      David,
      I LOVE the Support Debugging tool! (I am going to use it in a few minutes to find all the tables with Serial Number for a client.) Can customers download it or do they need a partner to get it for them?
      Thanks!
      Devon

      • Stanley
        July 28, 2010 at 8:44 am

        Customer’s cannot download it them self. They can ask their Partner to send it to them and it is up to the Partner. I spent near 3 month trying to get it from our Partner, but that’s partly because of other issues.

      • July 28, 2010 at 9:27 am

        Yeah, that is what I thought. That is why I didn’t highlight the Supporting Debugging tool more. I hate to post “here’s a cool tool, but sorry – you can’t have it!”…

      • July 29, 2010 at 10:30 pm

        The tool is free and can be used by anyone, but is only available for download from PartnerSource.

        This was the only way I could get the tool released publicly at all.

        The reason is that the tool’s Advanced Mode features are very powerful and can mess up the system if used incorrectly (same as SQL Enterprise Manager). So we wanted partners to know when their customers were using the tool.

        Also, when the tool was first released the benefits for Administrators at customer sites was not as easy to demonstrate as it is with the later builds.

        David

  2. July 28, 2010 at 3:49 am

    Hey Devon,

    That’s great information for somebody beginning to find GP Table Information!

    You will see some other useful references related to this in the “Tables Information” section on GPWindow.com
    http://www.gpwindow.com/DEVELOPMENT/Tables_Information/

    Some of the articles you and readers of your blog might find useful are – Mark Polino’s GP Tables Excel Sheet and Table References by Module by Victoria. You can find all the links from the link above.

    • July 28, 2010 at 8:23 am

      Thanks Jivtesh – the more the merrier when it comes to resources for finding data in GP.

  1. July 29, 2010 at 1:08 pm

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