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.