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
- Bank Rec Tables
- Company/System Tables
- Fixed Assets Tables
- GL Tables – General Ledger
- Inventory Tables
- Payroll Tables
- PM Tables – Payables Management
- POP Tables – Purchase Order Processing
- RM Tables – Receivables Management
- SOP Tables – Sales Order Processing
More Online Resources for Dynamics GP Reporting
- David Musgrave’s blog:
- David has a comprehensive post on Finding Table and Field Information in Microsoft Dynamics GP.
- For use with the Support Debugging Tool: Getting Table and Field Data out of Dexterity Dictionaries and my example on how to use this.
- Mark Polino’s blog – Mark has a section for GP downloads where you can find lists of all the GP tables in Excel format.
- Leslie Vail’s blog – Leslie has some great posts about the inner workings of GP including an explanation of Dynamics GP Table Names and Moving from Work to Open to History for various GP modules.
- Mariano Gomez’s blog – detailed post on how to update an existing SmartList in SmartList Builder after adding column(s) to the SQL view the SmartList is based on.
- Ron Wilson’s blog – Ron has been posting some useful SQL code on his blog, Real Life Dynamics User.
- Steve Gray’s blog – Steve has lists of tables for selected modules under the Dynamics GP Table Structures section.
- Prospr’s Dynamics GP Table Reference – very nice searchable list of Dynamics GP tables, with details for each table.
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
LikeLike
Is there a way to rewrite this to retrieve daily period/ending GL balances?
Thanks so much!
LikeLike
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
LikeLike
What table has the Department Description Field?
LikeLike
Hi Sean,
Not sure what “Department Description” means to you. What screen do you see it on in GP?
-Victoria
LikeLike
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 🙂
LikeLike
Hi Tommy,
I am not aware of any way to do this.
-Victoria
LikeLike
That’s sad but thank you!
LikeLike
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?
LikeLike
Hi Ruben,
That is in field TRXDSCRN in the PM20000 and PM30200 tables.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLiked by 1 person
Hi Tanner,
We don’t have any customers that use the Project Accounting module, so it’s not something I know well. That’s why I have not put any Project Accounting code or posts on my blog.
-Victoria
LikeLiked by 1 person
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)?
LikeLiked by 1 person
Hi Cid,
I believe it should only be the SOP10100 table.
-Victoria
LikeLiked by 1 person
That’s what I though so also, but does it affect SOP10105 also?
LikeLiked by 1 person
Yes, if you have freight set up to be taxed, that table might be updated. I suppose there are also other possibilities depending on that else you might have installed or set up. What specifically are you looking to do?
-Victoria
LikeLiked by 1 person
I’m looking at pushing data (freight, tracking number, weight, no. of boxes) from FedEx Ship Manager to Dynamics GP to automate the process.Freight is the tricky one because it affects other tables and I think some field needs to be recalculated like Document Amount.
LikeLiked by 1 person
I would STRONGLY recommend against pushing this directly into the tables. There is a huge amount of logic in the UI that you will be missing. By the time you error trap for all of it, you’re going to be much better off using eConnect to update the SOP records correctly.
-Victoria
LikeLiked by 1 person
eConnect might be too much for me to handle, but I’ll give it a shot. I was looking at ShipGear by V-technologies as another option. Have you used them before?
LikeLiked by 1 person
Yes, I have customers using ShipGear. That’s a good alternative if the features work for you.
-Victoria
LikeLiked by 1 person
Thanks Victoria. That might be my last/best option. I’m testing directly using an econnect stored procedure (taSopHdrIvcInsert) to do the update. If that wont work, then I have to pitch acquiring ShipGear.
LikeLiked by 1 person
Ok, thank you Victoria. I will do that for sure.
Have a great week,
Alex
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Alex,
I don’t work with the Payroll module much myself, and have never done what you’re asking, so I cannot help with your question. I would recommend posting it on the Dynamics GP Community Forum to see if others can help you out.
-Victoria
LikeLiked by 1 person
Fantastic site. You have no idea…
LikeLiked by 1 person
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
LikeLiked by 1 person
Alex, I am having the same issue with GP 2010. Do you mind share the table names?
LikeLike
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Victoria,
Is there a way of getting a list of all of the roles and what they have access to?
Thanks
Phil
LikeLiked by 1 person
Hi Phil,
Try some of the security code I have posted on my System/Setup SQL Code page. I would probably start with the security details one. If not all the roles are coming up, try assigning a user to all roles except POWERUSER and then run the code for that one user.
-Victoria
-Victoria
LikeLiked by 1 person
Hi Vctoria,
Is there relation between Customer class and extender in GP2015? If yes How are they linked ?
LikeLiked by 1 person
Hi Kingsley,
There is no ‘out-of-the-box’ relationship between Customer Class (or any other data in Dynamics GP) and Extender. You might have something set up in your environment that creates a relationship, but without seeing your Extender setup, it’s impossible to know.
-Victoria
LikeLiked by 1 person
Hi Victoria,
Is there a graphical representation of all of the user roles in GP 2013?
Thks
Phil
LikeLiked by 1 person
Hi Phil,
Not that I am aware of.
-Victoria
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Cecilee,
You probably need tables PP100100 (header) and PP100101 (detail). I am not aware of any out of the box report that will give you the information you’re looking for.
-Victoria
LikeLiked by 1 person
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!
LikeLiked by 1 person
SOP tables only store invoice details, no current balances. The invoice balances will be in the RM20101 table, in the CURTRXAM field.
-Victoria
LikeLiked by 1 person
Thank you. Actually, I just used one of the views you have posted here to get what I exactly needed. Thank you very much.
LikeLiked by 1 person
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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.
LikeLiked by 1 person
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())
LikeLiked by 1 person
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
LikeLiked by 1 person
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?
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Victoria
Thank you, that is great.
Ian
LikeLiked by 1 person
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Do you have a listing of the payroll module tables as well?
Thanks!
LikeLiked by 1 person
Hi Amber,
Not yet. But I will put it on the request list for the future. 🙂
-Victoria
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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!
LikeLiked by 1 person
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
LikeLiked by 1 person
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi victoria,
Thanks for timely help…
Regards,
sathya
LikeLiked by 1 person
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?
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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#.
LikeLiked by 1 person
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
LikeLiked by 1 person
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?
LikeLiked by 1 person
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:
-Victoria
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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….
LikeLiked by 1 person
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
LikeLiked by 1 person
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)
LikeLiked by 1 person
Hi Fazil,
The Receivables Transactions SmartList uses the following tables:
The Sales Transactions SmartList uses the following tables:
For additional information and code, I would recommend taking a look at the following pages on this blog:
-Victoria
LikeLiked by 1 person
Thanks Victoria, This Will help.
LikeLiked by 1 person
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 ?
LikeLiked by 1 person
Muhammad,
I do not work with Manufacturing, so I do not have a list of tables to share for it.
-Victoria
LikeLiked by 1 person
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.
LikeLiked by 1 person
Aissam,
That is in the SY01200 table.
-Victoria
LikeLiked by 1 person
Hi Victoria,
Does the email address field in the SY01200 table allow for multiple email addresses if separated by a semicolon?
Thanks,
Sarah
LikeLiked by 1 person
Hi Sarah,
Yes.
-Victoria
LikeLiked by 1 person
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
LikeLiked by 1 person
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:
-Victoria
LikeLiked by 1 person
Thanks for your reply.
– Steve
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
Victoria,
Thanks for your help.
Steve
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Vic,
You should be able to get this with the Items SmartList.
-Victoria
LikeLiked by 1 person
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
LikeLiked by 2 people
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
LikeLiked by 1 person
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
Victoria,
I also feel like that. Because I tried every possibiltes for it.
Thanks for your support and time.
Tanuja
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
Hi Vic,
All the ‘internet information’ is in the SY01200 file. The Customer ID will be the Master_ID and the Master_Type will be CUS.
-Victoria
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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.
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person
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
LikeLiked by 1 person