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

84 Responses to “GP Tables”

  1. First, I would like to say that your website has been a tremendous help to me.

    I have run into an issue perhaps you could assist me with. We have a unique situation where we must add roughly 600,000 new items to GP. We macroed the process and did small scale testing. It could take thousands of hours to do so via macro.

    I was wondering if there is a way to do this via SQL. I have attempted to run the macro for one new item in our test environment while running SQL profile to perhaps come up with a process in which to create an item properly via SQL script. The SQL profiler reveals only select statements and nothing that would insert the new data.

    I have done some considerable research but have turned up nothing. Thoughts?

    I apologize for the placement of this comment/question, I was unsure where it was appropriate if at all.

    Like this

    • Hi Daniel,

      Thanks for your kind words.

      Looks like you’ve already gotten some good advice for this question on the GP Community Forum: https://community.dynamics.com/gp/f/32/p/124895/267020.aspx?WT.mc_id=ForumPost#267020.

      My 2 cents: When inventory items are created there are probably 10 tables updated. The details will depend on exactly what information you need to add for the items. To preserve the integrity of the business logic in GP, the best way to get new items into GP is to import them using one of the already available tools like Integration Manager or eConnect. Integration Manager will be a lot slower that eConnect, because it is also essentially a macro, but it should be faster than doing your own macro and it is a lot easier to work with, as it does not require any additional coding.

      I noticed from your other post that you’re on GP 9.0. That means if you don’t already own one of these tools, you will not be able to purchase them. In that case, your only other option is SQL, as entering this many items manually could be a nightmare. The table import option that Leslie mentioned is essentially the same thing. This could get very complicated, depending on the details of your items and will require significant planning and testing. I am not aware of anything available online that will help you with this. I would recommend you work with your GP Partner, or someone that knows the GP tables and SQL Server very well to help you do this.

      As a separate note, I would suspect that having 600,000+ items in GP may cause some significant performance issues. Obviously I don’t know any details about your infrastructure, but I am thinking since you’re on an unsupported version of GP, you’re also on an older version of SQL Server. I just want to make sure you don’t go through all of this work to get these items in there, only to find out it is going to cripple your GP system.

      -Victoria

      Like this

      • Victoria,

        Thank you very much for the response. I appreciate your input on the topic.

        We will be taking everything you said into consideration as well as the dynamics post.

        Thank you,
        Daniel

        Like this

  2. So, I can already imagine the backlash, but do you have any advice on adding columns to GP tables? (e.g. adding a CREATED_BY to SVC00700 with a default of SUSER_SNAME())

    Like this

    • Hi Matthew,

      No backlash – just some advice. Don’t do it. It is not supported and it will likely break any future updates or upgrades you do to GP. Instead, create a new table to store the data you need, or use Extender to store the additional data.

      -Victoria

      Like this

  3. Hi,

    I found your blog when searching for GP Database Table information. I’m considering a migration from Dynamics SL to Dynamics GP. We do a fair amount of behind the scene, direct access (read only) to our current Dynamics SL system — creating invoices with a custom application, etc.

    The DynamicsSL table names are fairly straight forward whereas it appears that the GP table names are impossible to figure out without full documentation, or coming to your site.

    This has me rethinking the migration to GP. If the table/column names are this cryptic is this something that I really want to move forward with?

    Like this

    • Hi Richard,

      I don’t know if the cryptic table names would be something I would consider a deal breaker. It’s knowledge that can be had, either by learning it yourself, or paying someone for some consulting time to teach you / help as needed. I am guessing there are reasons you’re considering moving from SL to GP? If those are compelling enough, I would not the let database structure stop you. Just my 2 cents.

      -Victoria

      Like this

  4. Hi Victoria,

    Thank you for posting the table details, this has been very helpful.
    Do you know which table holds the data shown on ‘Vendor Account Maintenance’ screen, specifically the Accounts Payable account.
    Thank you very much for any help you can provide.

    Ian

    Like this

    • Hi Ian,

      The indexes of the accounts on the Vendor Account Maintenance screen are stored in the Vendor Master table – PM00200. The Accounts Payable account index is in the PMAPINDX column. Using the indexes you can link to either GL00100 or GL00105 depending on what you need.

      -Victoria

      Like this

  5. Hi Victoria,

    Do you have a listing of the payroll module tables as well?

    Thanks!

    Like this

  6. Hello Victoria,
    I am trying to research sales by vendor in GP. I’ve gone through the reports I would think they’d be in but have not had any success. Is there a standard report that would have this information or will I need to create one? Thank you in advance

    Like this

    • Hi Kyle,

      Can you please explain what ‘sales by vendor’ means in your case? That’s not necessarily a standard term and might mean something different for your company.

      -Victoria

      Like this

      • Sorry Victoria, I’m new to GP and new to some of the terminology. My company has about 200 different vendors. I’d like to be able to select a vendor and see what we’ve sold (in dollars) of that particular vendor within a set time frame. Even if I could just look at the purchases from that vendor I could work it backwards. Thank you very much!

        Like this

        • Kyle,

          Do you need to actually track each sold item to the vendor it was purchased from? Or is there another way to tell more easily? For example, I have seen some companies track the vendor as an item category, or short name…or even make it part of the item number.

          -Victoria

          Like this

          • Victoria,

            I don’t need to see the individual sales from that vendors products. I just want to see the total sales of that vendors products during a timeframe. That timeframe could be 6 months or it could be 10 years, it really doesn’t matter. For example Vendor: XYZ- timeframe 1/1/2016-1/1/2017- $250,000.00 in total sales. I’m not as concerned with individual items as much as I am total vendor sales. Basically I’m trying to track our marketing efforts to determine the growth of a certain vendor. When I say that I can work it backwards if I can see the purchases for that vendor, I just mean if my only option is to use the purchasing view and I can see something like; Vendor: XYZ- timeframe 1/1/2016-1/1/2017- $175,000.00 in total purchases it will give me an idea of our growth with that vendor.

            Like this

            • Kyle,

              I understand you don’t need to see the individual sales on your report, however, you would need to identify them to add them up to show the summaries by vendor on your report. GP does not store anything like ‘sales by vendor monthly summary’. That’s why I was initially asking you to explain what that meant to you. So you would need a different way to find this. I think it’s doable, but would require an understanding of how items and item vendor relationships are entered into GP by your company.

              If you simply want to see the purchases per vendor, take a look at my Payables SQL Views page – there should be something there to help you, like the monthly or yearly vendor totals listed towards the end.

              -Victoria

              Like this

  7. Hi Victoria! We’re having a bit of an issue with our checks after upgrading to GP2010, all of the checks coming from POP have a transaction description of “Receivings Transaction Entry” where in GP10, it was blank. Users want this to change, but I can’t find where it’s coming from. Any thoughts?

    I’m assuming it’s defaulting that in place of the “Description” field that is in PM transactions (invoices) that shows on check stubs. we’d like to have that as an editable field on our POP transactions if possible.

    Like this

    • Hi Mike,

      By default GP checks do not show the transaction description. If you had something like this in the past, I would suspect it was a customization and/or a modified report. Perhaps not everything got upgraded properly, it’s really impossible to know without seeing your old system.

      To your other question – I am not aware of any way to make the description that gets generated by POP invoices editable, as it’s nowhere on a screen.

      -Victoria

      Like this

  8. Victoria,

    In the glHistoryTransactions view available in GP what does the item Ledger_id identity? Is it identifying the database the transaction is recorded in ?

    Thank you,

    Henry

    Like this

    • Hi Henry,

      One new feature added in Dynamics GP 2010 was the ability to set up multiple reporting ledgers to meet compliance standards for IFRS (International Financial Reporting Standards). This is not used by most companies, so not something many people would have heard about. The reporting ledger ID is stored in the Ledger_ID field.

      -Victoria

      Like this

  9. Hi Victoria-do you have table information for the Project Accounting tables? I am having a hard time figuring out where they are mapped in the front end of DGP 2013

    Like this

    • Hi J,

      I don’t work much with Project Accounting, so I don’t have the tables on my blog. Take a look at the links above to see if any of them will help. Specifically for finding where data is stored when looking at windows in GP, I would recommend using the Support Debugging Tool.

      -Victoria

      Like this

  10. Hi Victoria,
    Now i need to update currency index in both header table as well as line table with currency ID in header table through SQL query. What I’m confused was how to find the header for an specific line table in SQL .
    For example GL10000 is the header table,where as GL10001 is the line table. Can i find this header and line table link in SQL tables?
    We can find with STD document number for each table.But i dont need that.

    I have 2 GP servers that I’d like to merged but is facing complexity with their currency setup where the currency index on the 1st server does not match the 2nd server. This also applies for the Exchange Table ID. So what I need is to build a query that will update all tables which have either Currency Index or Exchange Table ID and update its values as per mapping in below table. As part of the update a backup of current tables and verification of records updated will need to be done whether the record is actually the correct currency or not.

    I hopefully waiting for your reply soon…

    Regards,
    sathya

    Like this

    • Sathya,

      This is not something that I think can be solved in blog comments. I would recommend that you work with a Dynamics GP consultant or contact Microsoft’s Professional Services Team to get help with this project. If not done right you could be facing some significant data issues.

      -Victoria

      Like this

  11. Hi Victoria,

    I’m trying to determine which table the check date is stored in for Purchasing Payables or if it is stored. Do you know which table it is in?

    Like this

    • Hi Karen,

      The check date is the DOCDATE in all the payables tables. There are many different tables depending on the status of the check, I have a PM Tables page on this blog that lists the commonly used tables. If you’re looking for something more specific, please give me some more details and I will try to help.

      -Victoria

      Like this

      • Hi Victoria,
        Do you know if it’s possible to get a list of reports that show me a certain field? The field I’m searching for is the ‘DistRef’ field from the PM10100 table?

        Kuntheary

        Like this

        • Hi Kuntheary,

          I do not know of a way to do this. Also, some fields might be called something else in different places, and on transaction data you will have multiple tables depending on the status of the transaction – in this case, one the transaction is fully applied, it will move the distributions to the PM30600 table.

          -Victoria

          Like this

  12. Victoria, I am not sure if you are still looking at these comments. However, if you are, I have an issue and was hoping you could assist. My goal is to run a smartlist of historical payables transactions by GL Trx Date.

    I am trying to link Payables History transactions back to the GL Transaction tables but I am having difficulty. It seems like Voucher Number is the only usable key in PM and there is no way to get back to JE#.

    Like this

    • Hi Timothy,

      If all you want is the GL Posting Date for payables transactions, you can get that from the payables tables – it is called PSTGDATE in both PM20000 and PM30200. You might find this code for all posted payables transactions helpful.

      If you’re looking to link the PM tables to the GL tables, that will be a bit more difficult and might also be dependent on how you are posting transactions in GP (with what level of detail). In general, you should be able to link PM tables to GL tables on the originating document type and document number. If you can write back with some more detail about what specifically you’re looking to show on your report, maybe I can suggest some other options.

      -Victoria

      Like this

      • Victoria, thank you for your quick response. If the PSTGDATE is effectively the same as TRXDATE per GL, that is all I need. I am trying to get detailed payables info (batch ID, extender fields, user defined fields, other payables trx entry form fields) into the GL fiscal periods. Am I taking the correct approach?

        Like this

        • Hi Timothy,

          Yes, the PSTGDATE is what gets sent to the GL as the TRXDATE when payables transactions are posted. I can think of two issues you might run into, not sure if they are applicable in your case:

          1. Your report might not match the GL if the TRXDATE is changed in the GL after it is posted in Payables, but before it is posted to the GL. This should not be happening, but I am just trying to cover all possible contingencies.
          2. If you are using Revenue/Expense Deferrals (RED) on your payables transactions one payables transaction might become multiple GL transactions with different dates. If you’re not using RED, this is not an issue.

          -Victoria

          Like this

          • Victoria, thanks. I am showing both Posted Date and Posting Date. Initially, I was confused but it appears that the Posted Date per AP ends up being the Check Date and the Posting Date (as you mentioned) ends up being the TRX Date

            Like this

            • Hi Timothy,

              Actually the Posted Date is the date that the transactions were actually posted in the AP subledger. If you’re looking for the check date, that would be DOCDATE (Document Date). Often for checks the Document Date will happen to be the same as the Posted Date, but there is no reason it has to be.

              -Victoria

              Like this

              • Hm, I’m quite certain of the fields I described above. I ticked back 50ish random vouchers the hard way: voucher by voucher: Posted Date -> Check Date (this value changes based on AP posting date and actual check date, with check date being the final value), Posting Date -> GL TRX Date, Document Date -> Bolded Doc. Date entry field from Transaction Entry screen

                Maybe the SQL view i am using from our GP partner has renamed the date fields accordingly….

                Like this

                • Timothy,

                  No way to say what a custom view is doing without looking at the code. But what I told you is true for just about every module in GP and certainly for payables. :-)

                  -Victoria

                  Like this

  13. Hai Victoria,
    I need help on smartilst. How can i know the tables/views for sales transaction and receivables transaction report from smartlist…? (I need to create a daily report on sales and collection with some grouping and exceptions)

    Like this

  14. 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 ?

    Like this

  15. 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.

    Like this

  16. 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

    Like this

    • 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

      Like this

  17. 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

    Like this

    • 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

      Like this

  18. 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

    Like this

  19. 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

    Like this

    • 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

      Like this

  20. 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.

    Like this

  21. 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

    Like this

  22. 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

    Like this

  23. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  24. 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

    Like this

  25. 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

    Like this

    • 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

      Like this

  26. 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.

    Like this

    • 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

      Like this

  27. 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

    Like this

    • 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

      Like this

Trackbacks/Pingbacks

  1. Dynamics GP 2013: Finding Data For Report Builder 3.0 | Prophet Business Group | Prophet Business Group | Microsoft Dynamics GP Partner/Reseller | Microsoft Dynamics CRM reseller | IT/Network Solutions | Winnipeg | Manitoba - March 20, 2014

    […] course, you may not need a complete list of all tables in GP. This Common GP Table list breaks down the table commonly used for inventory, payroll and many […]

    Like this

  2. Data Flow and Table Names | Interesting Findings & Knowledge Sharing - May 20, 2012

    [...] each module. If you are creating reports, or crafting SQL queries, it is information you can use h. Victoria also has some very helpful SQL queries availablefor you to download.  Let’s move on [...]

    Like this

  3. 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 [...]

    Like this

  4. 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 [...]

    Like this

  5. 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 [...]

    Like this

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

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

    Like this

  7. 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/ [...]

    Like this

  8. 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/ [...]

    Like this

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

Follow

Get every new post delivered to your Inbox.

Join 1,357 other followers

%d bloggers like this: