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

 

136 Responses to “GP Tables”

  1. Lenny Reply April 14, 2023 at 4:25 am

    Thank you so much for sharing all this info, especially the “GP Tables.” I’m trying to learn how to use Power BI while building Dynamics GP reports/queries/dashboards that are useful to my org. You have already helped me out a bunch.

    -Lenny

    Like

  2. Greg Reply April 22, 2019 at 9:13 pm

    Is there a way to rewrite this to retrieve daily period/ending GL balances?

    Thanks so much!

    Like

    • Victoria Yudin Reply April 24, 2019 at 5:08 am

      Hi Greg,

      The short answer is yes, the long answer is that how it’s done will greatly depend on exactly what you need, there is no one answer or solution that will work for every situation.

      -Victoria

      Like

  3. Sean Reply April 17, 2019 at 5:18 pm

    What table has the Department Description Field?

    Like

  4. Tommy Reply February 5, 2019 at 12:56 pm

    Is there a way through SQL or SalesPad or through GP to unvoid an order? I accidentally voided a few orders and would like to reverse that if possible! Thank you in advance 🙂

    Like

  5. Ruben Medeiros Reply August 1, 2018 at 11:13 am

    After clicking into an open doc number in GP, there is a description field. Is this an available column in any of the PM tables?

    Like

  6. Dave Reply October 20, 2017 at 3:40 pm

    Hi Victoria,

    Great site – so much useful information here!

    We are currently using GP 2010 with the AA (Analytical Accounting) module.

    We are building a Data Warehouse and I am looking to export GL transactions (current and historical) into the DW. Using one of your SQL scripts/views and some joins to the AA tables I have been able to get GL transactions with the dimensions / values / amounts as columns (I can share these queries with you if you think that would be helpful for others)…

    I’d like to include ITEM ID in these exports (similar to the way the AA dimensions are included) – is there a way to join to the ITEM on a POP or SOP line item from the GL transaction table(s) GL20000 or GL30000 ?

    Any help would be greatly appreciated

    Thanks
    Dave

    Like

    • Victoria Yudin Reply October 23, 2017 at 3:43 am

      Hi Dave,

      Both POP and SOP post to the GL in summary for the transaction. So that if there are 5 items on a transaction with the same account, you will not see 5 lines in the GL for it, you will only see 1 line. Because of this, there is no direct link you can make between SOP and POP GL distributions and line items on the transactions.

      -Victoria

      Like

      • Dave Reply October 23, 2017 at 6:33 pm

        What about the other direction? If I could start with the SOP or POP Invoice and then total the line items I could break out the GL transactions by item that way – it’s definitely tougher that way I’ll need to make it happen somehow.

        Thanks

        Like

        • Victoria Yudin Reply October 24, 2017 at 4:34 am

          Dave,

          Unfortunately, you are going to have the same issue. There is no direct definitive link between the individual line items and the GL distributions. You can often figure it out using the default GL accounts on each item, but there is nothing to force those to be correct for each transaction.

          -Victoria

          Like

  7. Tanner Reply August 14, 2017 at 11:20 am

    Thank you for everything you’ve put on here, great information. I use your site whenever I need to write a query and determine what tables to use. I’m wondering if you’ve ever considered uploading table information for the Project Accounting module?

    Liked by 1 person

  8. cid (@iamrico22) Reply July 11, 2017 at 3:49 pm

    Hi Victoria,

    Your blog helped me a lot. Do you happen to know which SQL tables are affected when you add/update the freight on a Sales Transaction Entry (Order)?

    Liked by 1 person

  9. Alex Reply October 17, 2016 at 9:54 am

    Ok, thank you Victoria. I will do that for sure.

    Have a great week,

    Alex

    Liked by 1 person

  10. Alex Reply October 16, 2016 at 8:18 pm

    Hi Victoria, I recently posted a question about exporting and importing Payroll Tax Table files but I can’t seem to find the blog post. Apologies in advance if this is a duplicate. Basically, I want to export the Payroll Tax Table files from the sample company in GP 9 to the actual company I’m working with in GP 9. What are the steps I would need in order to do this? Basically, I want to fill the tax details myself using my own data but it won’t let me do it manually without first importing the tax files. Many thanks for your assistance in advance.

    Alex

    Liked by 1 person

  11. Bruce Collier Reply March 24, 2016 at 7:01 am

    Fantastic site. You have no idea…

    Liked by 1 person

    • Alex Reply October 23, 2016 at 12:45 am

      Hi Victoria, I just wanted to provide an update to my issue with the payroll tax tables. I was able to resolve it by creating a csv file based off the table structure of the payroll tax tables I needed. I then imported it to GP and was able then to work and modify as needed the payroll tax tables. Thanks for all of your assistance with this matter- I just wanted to provide an update that I was able to resolve the issue.

      Best,
      Alex

      Liked by 1 person

  12. Mark Plaideau Reply March 3, 2016 at 6:59 pm

    Victoria, its been a while. Thankfully, I haven’t needed you help much.

    Is it possible to see GL distribution per line item in a SQL view? Seems I have some sales transactions where the SALES are being booked but there are no COGS or INVENTORY postings. Scary, yes I know.

    Otherwise, I am having to resort to opening each sales transaction in GP and look at the distribution and hope that each line item on the sales invoice has hit the GL correctly.

    Liked by 1 person

    • Victoria Yudin Reply March 6, 2016 at 6:13 am

      Hi Mark,

      Are you talking about SOP GL distributions?

      One reason that COGS and INVENTORY would not be on a transaction would be that the inventory cost is $0. Another would be that the items are not set up properly. You may want to work with your GP Partner to go over your data and/or setup to get to the root of the problem.

      -Victoria

      Liked by 1 person

  13. Phil Clarke Reply February 25, 2015 at 6:29 am

    Hi Victoria,

    Is there a way of getting a list of all of the roles and what they have access to?

    Thanks

    Phil

    Liked by 1 person

  14. Phil Clarke Reply February 24, 2015 at 5:29 pm

    Hi Victoria,
    Is there a graphical representation of all of the user roles in GP 2013?
    Thks
    Phil

    Liked by 1 person

  15. Cecilee Reply August 15, 2014 at 4:33 pm

    Victoria,

    I am wondering if you can tell me what tables I can find the Deferred Revenue in. I am trying either write a report, or find a report that will show what sales documents have been deferred, and what ones have not.

    Cecilee

    Liked by 1 person

  16. TeknoBlast (@TeknoBlast) Reply July 24, 2014 at 3:54 pm

    Is there a way to know if an invoice has a zero balance remaining? I find all the invoices in the SOP30200 table, but it lists ALL the invoices for a particular customer. I would like to only view the ones that are not paid off. Thanks!

    Liked by 1 person

  17. Daniel Reply April 9, 2014 at 1:29 pm

    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.

    Liked by 1 person

    • Victoria Yudin Reply April 10, 2014 at 4:47 am

      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

      Liked by 1 person

      • Daniel Reply April 10, 2014 at 8:03 am

        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

        Liked by 1 person

      • Rob Klaproth Reply May 21, 2014 at 10:33 am

        One company I worked for would create a unique item number for every possible combination of its software product. So there were 10 products x 100 markets x 1000 different user count possibilities x 5 sub modules x whatever other things they could come up with to complicate life.

        We had over A million items in our IV tables. They were all non inventory since it’s software delivered electronically.

        I inserted them all directly in SQL to each IV table then I ran check links on inventory tables to make sure everything is ok.

        Bottom line, it can be done. It’s a royal pain but much easier then eConnect or integration manager.

        Our system did not take a performance hit as far as I could tell. Just make sure your SQL server has 4 cores or more and that you have 16 gigs or more of ram in SQL and you should be ok.

        Liked by 1 person

  18. Matthew Arp Reply March 24, 2014 at 1:56 pm

    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())

    Liked by 1 person

    • Victoria Yudin Reply March 24, 2014 at 1:59 pm

      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

      Liked by 1 person

  19. Richard West (@RichardWest) Reply January 12, 2014 at 11:49 am

    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?

    Liked by 1 person

    • Victoria Yudin Reply January 12, 2014 at 12:11 pm

      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

      Liked by 1 person

  20. Ian Holt Reply November 26, 2013 at 7:12 am

    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

    Liked by 1 person

    • Victoria Yudin Reply November 26, 2013 at 7:44 am

      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

      Liked by 1 person

      • Ian Holt Reply November 26, 2013 at 7:53 am

        Hi Victoria

        Thank you, that is great.

        Ian

        Liked by 1 person

      • Amanda Baudo Reply June 11, 2019 at 5:13 pm

        Hi Victoria,
        This is exactly what I was looking for, but when I go to the DB, that value is 0. I am using GP2016. Are these still stored in the same table or has it been moved to a newer location? I’m looking for the Accounts Payable and the Purchases default accounts.

        Like

        • Victoria Yudin Reply June 12, 2019 at 4:55 am

          Hi Amanda,

          The accounts Iam was asking about were on the Vendor Account Maintenance window specifically. There are other places for various different defaults. Where (what window) in GP are the accounts you’re looking for?

          -Victoria

          Like

          • Amanda Baudo Reply June 12, 2019 at 12:39 pm

            Hi Victoria,

            The window says “Vendor Account Maintenance”. I’m getting there by clicking Accounts when in a vendor card. We have an inactive GL linked to a specific vendor record and I’m trying to locate all vendors with this same situation.

            Amanda

            Like

            • Victoria Yudin Reply June 15, 2019 at 7:51 am

              Hi Amanda,

              If you’re looking at the Purchases account on the Vendor Account Maintenance window, you can check for invalid ones using the query below:

              select * from PM00200 where PMPRCHIX <> 0 and PMPRCHIX not in (select ACTINDX from GL00100)

              Hope that helps.
              -Victoria

              Like

  21. Amber Reply November 14, 2013 at 6:07 pm

    Hi Victoria,

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

    Thanks!

    Liked by 1 person

  22. kyle Reply October 5, 2013 at 5:51 pm

    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

    Liked by 1 person

    • Victoria Yudin Reply October 6, 2013 at 4:43 am

      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

      Liked by 1 person

      • kyle Reply October 7, 2013 at 12:48 pm

        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!

        Liked by 1 person

        • Victoria Yudin Reply October 8, 2013 at 8:47 am

          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

          Liked by 1 person

          • kyle Reply October 8, 2013 at 8:37 pm

            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.

            Liked by 1 person

            • Victoria Yudin Reply October 9, 2013 at 9:30 am

              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

              Liked by 1 person

  23. Mike Reply September 19, 2013 at 12:44 pm

    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.

    Liked by 1 person

    • Victoria Yudin Reply September 19, 2013 at 3:47 pm

      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

      Liked by 1 person

  24. henry1971 Reply September 12, 2013 at 6:31 pm

    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

    Liked by 1 person

    • Victoria Yudin Reply September 13, 2013 at 5:27 am

      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

      Liked by 1 person

  25. J Massey Reply September 4, 2013 at 1:00 pm

    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

    Liked by 1 person

    • Victoria Yudin Reply September 4, 2013 at 2:06 pm

      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

      Liked by 1 person

  26. sathya Reply July 8, 2013 at 6:30 am

    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

    Liked by 1 person

    • Victoria Yudin Reply July 8, 2013 at 6:36 am

      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

      Liked by 1 person

  27. Karen Reply March 11, 2013 at 8:41 pm

    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?

    Liked by 1 person

    • Victoria Yudin Reply March 12, 2013 at 6:18 am

      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

      Liked by 1 person

      • Kuntheary Pek Reply June 27, 2013 at 7:28 pm

        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

        Liked by 1 person

        • Victoria Yudin Reply June 28, 2013 at 3:52 am

          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

          Liked by 1 person

  28. Timothy Reply July 23, 2012 at 10:57 am

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

    Liked by 1 person

    • Victoria Yudin Reply July 23, 2012 at 11:37 am

      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

      Liked by 1 person

      • Timothy Reply July 23, 2012 at 11:49 am

        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?

        Liked by 1 person

        • Victoria Yudin Reply July 23, 2012 at 11:59 am

          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

          Liked by 1 person

          • Timothy Reply August 9, 2012 at 8:41 am

            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

            Liked by 1 person

            • Victoria Yudin Reply August 9, 2012 at 8:54 am

              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

              Liked by 1 person

              • Timothy Reply August 9, 2012 at 9:04 am

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

                Liked by 1 person

  29. Fazil.TK Reply July 3, 2012 at 4:24 am

    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)

    Liked by 1 person

  30. Muhammad Yezdan Reply July 27, 2011 at 6:50 am

    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 ?

    Liked by 1 person

  31. Aissam Reply July 26, 2011 at 2:21 pm

    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.

    Liked by 1 person

  32. Esteven Fulgar Reply July 18, 2011 at 5:48 am

    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

    Liked by 1 person

    • Victoria Yudin Reply July 18, 2011 at 6:30 am

      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

      Liked by 1 person

  33. Steve Reply July 6, 2011 at 9:43 am

    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

    Liked by 1 person

    • Victoria Yudin Reply July 7, 2011 at 7:23 am

      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

      Liked by 1 person

  34. Vic Reply April 7, 2011 at 11:58 am

    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

    Liked by 1 person

  35. Rahmanfard Reply March 15, 2011 at 4:25 pm

    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

    Liked by 2 people

    • Victoria Yudin Reply March 16, 2011 at 6:47 am

      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

      Liked by 1 person

  36. Ronald McVicar Reply March 8, 2011 at 9:30 am

    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.

    Liked by 1 person

  37. Tanuja Reply October 21, 2010 at 1:06 am

    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

    Liked by 1 person

  38. Tanuja Reply October 19, 2010 at 1:40 pm

    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

    Liked by 1 person

  39. Tanuja Reply October 18, 2010 at 4:52 am

    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

    Liked by 1 person

    • Victoria Yudin Reply October 18, 2010 at 5:53 am

      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

      Liked by 1 person

      • Tanuja Reply October 19, 2010 at 1:14 pm

        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

        Liked by 1 person

        • Victoria Yudin Reply October 20, 2010 at 10:27 am

          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

          Liked by 1 person

  40. Vic Reply October 6, 2010 at 8:03 am

    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

    Liked by 1 person

  41. Dawn Reply August 24, 2010 at 10:06 am

    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

    Liked by 1 person

    • Victoria Yudin Reply August 24, 2010 at 10:12 am

      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

      Liked by 1 person

  42. Dawn Reply August 18, 2010 at 12:38 pm

    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.

    Liked by 1 person

    • Victoria Yudin Reply August 18, 2010 at 2:16 pm

      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

      Liked by 1 person

  43. waseem Reply May 24, 2010 at 4:36 am

    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

    Liked by 1 person

    • Victoria Yudin Reply May 24, 2010 at 6:31 am

      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

      Liked by 1 person

Trackbacks/Pingbacks

  1. A Simple Way to Track Changes to Master Records in GP – DYNAMICS ZR - November 20, 2019

    […] The third solution I’ll present is using triggers within the database tables when an update, insert or delete of a record happens, and it will capture the changes in a log table. This trigger will work for any systems using a SQL database. In this example, I’ll be using Dynamics GP. You’ll need some knowledge of Dynamics GP tables, but as a reference, here are two links that describes the tables: http://dyndeveloper.com/DynModule.aspx and https://victoriayudin.com/gp-tables/ […]

    Like
  2. Implementing SmartList Builder: Review - Microsoft Dynamics GP Community - May 28, 2019

    […] are resources such as the GP Tables section of Victoria Yudin’s website or my azurecurve|Microsoft Dynamics GP Table Reference which you can use to find the tables you […]

    Like
  3. Implementing SmartList Builder: Review – azurecurve - May 28, 2019

    […] are resources such as the GP Tables section of Victoria Yudin’s website or my azurecurve|Microsoft Dynamics GP Table Reference which you can use to find the tables you […]

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

    Liked by 1 person
  5. 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 […]

    Liked by 1 person
  6. 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 https://victoriayudin.com/gp-tables/.Victoria also has some very helpful SQL queries available for you to download. Miscellaneous Tables […]

    Liked by 1 person
  7. 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 […]

    Liked by 1 person
  8. 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 […]

    Liked by 1 person
  9. Dynamics GP Tables « The World of an IT Leader - August 2, 2011

    […] https://victoriayudin.com/gp-tables/ […]

    Liked by 1 person
  10. 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. https://victoriayudin.com/gp-tables/ […]

    Liked by 1 person
  11. 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. https://victoriayudin.com/gp-tables/ […]

    Liked by 1 person

Leave a comment