GP Tables

Below are pages with table information for Dynamics GP as well as some additional resources for finding table information. This is not meant to be a listing of all tables or all modules, just the tables and fields we most often use when reporting. 

Pages with Table Information for Dynamics GP


More Online Resources for Dynamics GP Reporting



40 Responses to “GP Tables”

  1. Hi Victoria,

    First of all, I am very thankful to you for sharing a very useful knowledge in this blogs. You have listed all the tables of each module but you didn’t mention about Manufacturing tables. Could you please provide me the list ?

  2. Hi,

    I want to know which table contain the additional information (Windows : internet information) for the inventory >cards>item> item maintenace –Description : the blue i

    thank you.

    aissam.

  3. Hi Victoria,

    I am currently working with GP 9.0 and implementing Project Accounting series. Among the prerequisite activities is the initialization of the the inventory master. the reason we need to start at zero, is because the company i am working for right now did not use the Inventory Control module before.

    The list of inventory items is very long and if we will enter it the standard way it will take a some time before data entry is finished. I was wondering if there was a way to make things faster like through an SQL script or through Integration Manager. Furthermore, which inventory tables need to be updated to initialize the inventory control module.

    Thanks in advance for your help.

    Steve

    • Hi Steve,

      I would not recommend initializing the Inventory module using tables, you should do this through the GP user interface. Similarly, there are so many tables involved with inventory items, that importing data directly into tables would be a nightmare and could cause issues if it’s not done properly. Integration Manager can definitely help with this. If you are not familiar with GP’s Inventory module and its setup, I would recommend a few things:

      • Get some help and/or training from your GP partner
      • Go through GP online training and/or manuals
      • Take a look at my book – I have a section on setting up the inventory module and also a section on importing inventory items using Integration Manager

      -Victoria

  4. Hi Victoria,
    I am not sure if this is the right place to post this question. We are using GP 10. I was wondering if it would be possible to give someone “Read only” access to everything in GP? If so how would I do that? Thanks for your help.

    Steve

    • Steve,

      There is a way to do it, but it’s going to be manual and tedious because there is no global ‘read only’ option or setting. Security in GP is by windows – you either have access to a window or you do not. If you have access to a window, you can do anything that the window allows (this can be somewhat mitigated with field level security, but I do not believe that will be of help with what you’re asking). So to accomplish read only access you need to restrict a user to inquiry windows and reports. There is no default role with this this, so you would have to create your own security role (and possibly tasks) that only allow access to inquiry windows and/or reports.

      -Victoria

  5. Hi Victoria

    Do you have a view that can show me my items numbers descriptions and the GL account codes associated with them?

    Thanks
    Vic

  6. Hello;
    I am new to the company and GP. I noticed the ODBC access to GP tables are locked. The IT / consultants are reluctant to grant me permission due to risk of jeopardizing the integrity of the GP tables. Is it not possible to have “read only” ODBC access to avoid ruining the table and field structures? I used to run “Read only” queries against SSA LN in other companies without any issues.
    Many thanks in advance

    • Hi Rahmanfard,

      Using a Dynamics GP login it is not possible to get to the GP SQL data, so separate security would need to be set up to accomplish this. This should be possible to set up as read only, however there may be many other considerations. For example, depending on what you need to access, it may be quite time consuming to set up permissions, as this may need to be done one table or resource at a time (there are typically over 1,000 tables and 20,000 stored procedures in a GP company database). In addition, many companies have strict policies about who can access the ‘raw’ data and how.

      -Victoria

  7. Thanks for all this very helpful site and your time and talents. Can you point me to references for the Manufacturing series modules / tables and fields in GP Manufacturing? Please and thank you.

  8. Victoria,

    I also feel like that. Because i tried every possibilies to get proper output.But some data apper in IV30301 and some data apper in IV10201.

    Thanks for your immediate support & time.

    Tanuja

  9. Hi Victoria,

    I have one question on your query.

    1) What is the used of I.RCPTNMBR = IP.DOCNUMBR in above query ?

    Can I used table name IV10200, IV10201, because some records is missing in IV30301 table.

    Thanks & Regards,
    Tanuja

  10. Hi,

    I want to create a report which is provide a vendor wise sales.I am
    using following tables in my query. But it was not give me a proper data for vendor wise sales.

    Please guide me which link is better to join these tables.Otherwise you can suggest me which are the tables I have to use to get this report.

    table IV30300
    table IV30301
    table SOP30300
    table IV10200
    table IV10201

    Thanks & Regards,
    Tanuja

    • Tanuja,

      This is a pretty difficult report and may depend greatly on exactly how data is being entered into GP. This will not actually give you any amounts, you’ll have to add a number of columns, depending on what data you need, but as a start, try something like this:

      SELECT SH.CUSTNMBR Customer, S.SOPNUMBE Invoice,
      S.ITEMNMBR Item, P.VENDORID Vendor,
      I.RCPTNMBR Receipt, P.PONUMBER PO
      FROM SOP30300 S
      INNER JOIN
      SOP30200 SH
      ON SH.SOPTYPE = S.SOPTYPE
      AND SH.SOPNUMBE = S.SOPNUMBE
      LEFT OUTER JOIN
      IV30301 I
      ON S.SOPNUMBE = I.DOCNUMBR
      AND S.LNITMSEQ = I.LNSEQNBR
      INNER JOIN
      IV30300 IP
      ON I.RCPTNMBR = IP.DOCNUMBR
      AND S.ITEMNMBR = IP.ITEMNMBR
      LEFT OUTER JOIN
      POP10500 P
      ON P.POPRCTNM = I.RCPTNMBR
      AND IP.LNSEQNBR = P.RCPTLNNM
      WHERE S.SOPTYPE = 3

      -Victoria

      • Hi Victoria,

        I tried above queary, but it didn’t work with my requirments.

        My required fields are as follows.
        Item No
        Item Desc
        Item Class
        Site ID
        Invoice No.
        Customer Customer No.
        Vendor ID
        Vendor Country
        Item Class
        Qty Sold
        Sales Amt
        Trade Disc.
        Unit Cost

        I used following tables in my query.
        IV10200,IV10201,IV30300,IV30301,SOP30300,SOP30200,RM00101,IV00101,IV00102,PM00200

        I got 90% result with above tables, but i could not able to get 100%.
        Please can you give me other hint for this report.
        I tried this report from last one month, but I am not able to get it properly. Please help me or suggest me what can I do for it.

        Thanks & Regards,
        Tanuja

        • Tanuja,

          I would say at this point we’re a little past what I would be able to help with in a blog post. There are some very complicated relationships between these tables and depending on how data is entered into GP and the logic needed for the report, this could get very complex.

          -Victoria

  11. Hi Victoria

    Can you tell me where i can find the default bill to email and default ship to email in gp 2010. I have to update all of our customers emails

    thanks
    Vic

    • Vic,

      Since there are so many e-mail addresses now…can you specify where exactly you enter these e-mails in the GP user interface?

      -Victoria

      • Victoria

        Thanks for quick response!! Under the customer card under internet information. You select with which address ID then you can enter internet info for that particular address.

        thanks
        Vic

  12. Hi Victoria,

    I do have another question for you….but it doesn’t have to do with the tables in Canadian Payroll (that mystery is still in discovery mode), but since you are the only one I seem to have found that has experience in Cdn Payroll for GP, do you know how to deal with Garnishments and/or Family Support Payments with Cdn Payroll…the kicker is we are not using the HR module, just the Cdn Payroll and it seems garnishments are setup via HR. Is there a work around for this if you are not using HR with Cdn Payroll?

    Dawn

    • Hi Dawn,

      I am sorry if what I wrote lead you to believe I have experience with Canadian Payroll, I do not. :-( I am not sure how much knowledge you’re going to be able to find out on the internet for this – have you talked to GP Support to see if they can help you with these questions? While not free, that may be the best way to go.

      -Victoria

  13. Hi,

    The Table info is very useful…thank you…do you have any further information on Canadian Payroll tables and their transaction flow? Doesn’t seem to be a lot of documentation or discussions on Canadian Payroll. Any insight into Cnd Payroll tables and transaction flow would be most useful.

    • Sorry Dawn,

      I do not have any information about Canadian Payroll…I see you also have a post about this on the GP Forum, you may need to check with GP Support on this one to get the information you need.

      -Victoria

  14. Hi Victoria,

    This is my first comment at your site. I have registered it last year. I have learned a lot from this site about GP tables and writing views and proceedures. I have a query and that is “IS there any best way you know that I can study the relationships between tables easily?”

    Thanks,

    Waseem

    • Hi Waseem,

      Glad to here you are finding my blog useful! There is really no shortcut for learning the GP tables. Sounds like you are on the right track and already using online resources that are out there, other than that it’s just experience and practice.

      -Victoria

Trackbacks/Pingbacks

  1. Dynamics Confessor Blogspot - GP Technical Blogs - Microsoft Dynamics Community - May 19, 2012

    [...] each module. If you are creating reports, or crafting SQL queries, it is information you can use http://victoriayudin.com/gp-tables/.Victoria also has some very helpful SQL queries available for you to download. Miscellaneous Tables [...]

  2. Packt Publishing–Dynamics GP 2010 Reporting - Real Life Dynamics User (RLDU) - GP Technical Blogs - Microsoft Dynamics Community - September 7, 2011

    [...] GP databases are structured.  Between what this book teaches and Victoria Yudin’s list of GP Tables, you should have all the tools you need to get started writing your own [...]

  3. Packt Publishing–Dynamics GP 2010 Reporting « Real Life Dynamics User - September 7, 2011

    [...] GP databases are structured.  Between what this book teaches and Victoria Yudin’s list of GP Tables, you should have all the tools you need to get started writing your own reports. Eco World [...]

  4. Dynamics GP Tables « The World of an IT Leader - August 2, 2011

    [...] http://victoriayudin.com/gp-tables/ [...]

  5. Dynamics GP Table Names - Interesting Findings & Knowledge Sharing - November 18, 2010

    [...] Victoria Yudin has some great information on the popular tables from each module. If you are creating reports, it is information you can use. http://victoriayudin.com/gp-tables/ [...]

  6. Dynamics GP Table Names - Dynamics Confessor Blogspot - July 6, 2010

    [...] Victoria Yudin has some great information on the popular tables from each module. If you are creating reports, it is information you can use. http://victoriayudin.com/gp-tables/ [...]

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 )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 486 other followers