GP Tables & SQL Tables

(0) Share

Report

Posted on by Community Member

I am a newbie to GP and am using 2013. Beginning to build basic Smartlists with SmartList Builder. Am confused understanding differences between GP tables and the SQL tables.

Can someone give me a leg up primer on their use and when to use one versus other?

Thanks in advance

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

L Vail 65,271 on at

Like (0)

Report

This is not exhaustive, but a good rule o thumb, use SQL tables for tables that are not part of GP. For instance, you created a view in SQL or a custom table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

i always using GP Tables if i create a smartlist that using only 1 table or 2 tables maximum. because in the smartlist builder, the relationship between tables only can 2 tables.

SQL Tables basically is VIEW in SQL. You can build your own view in sql and view the result in smartlist GP.

Let me give you a sample.

GP Tables : IV Item Maintenance, you can directly using this table when you want to view item master list, but when you want to view other information that related to item master, i suggest you to build a view in sql.

SQL Tables :

Create View View_IV_Item_MSTR

as

SELECT * FROM IV00101 A JOIN IV00101_ADD B ON A.ITEMNMBR = B.ITEMNMBR

In this case, table IV00101_ADD is a custom tables that store additional information of the item.

Regards,

Kurniawan

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

To expand on what Leslie says, when you install GP it is going to create a database called DYNAMICS and then another database for each company your create. In those databases will be series of tables that GP needs to run. Those tables are GP tables but are just SQL tables that are specific to GP. Now if you come along and add tables that are used for some other purpose they would be just plain SQL tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Leslie, Kurniawan, and Richard. Now just have to invest time in figuring out the mapping of tables and fields. They don't seem to make it easy but the various posts and blogs here have helped immensely.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

You may also find this posting to be useful.

dynamicsconfessions.blogspot.com/.../dynamics-gp-table-names.html

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

We have a book that helps you trace transactions through the maze. It's called Information Flow and Posting. While we do not list field names (plenty of sources for those) we do tell you what files are hit when you perform a particular transaction, data entry, postings, voids, etc. The core products (GL, RM, PM, POP, SOP, and IV) are covered.

Check out our web site.

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

I would like to recommend a valuable work done by Ms. Victoria Yudin, summarizing and categorizing GP tables on a module-base, and providing a very useful explanation on key tables' fields which might be used for further understanding of the DB structure and tables relations.

Reference: victoriayudin.com/gp-tables

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Here's another one similar to Victoria Yudin's... www.gptablereference.com - for some reason I find myself using this one more often.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you all very much for this information

Was this reply helpful? Yes No