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
- 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.
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?
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
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#.
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
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?
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
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
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
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….
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
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)
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
Thanks Victoria, This Will help.
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 ?
Muhammad,
I do not work with Manufacturing, so I do not have a list of tables to share for it.
-Victoria
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.
Aissam,
That is in the SY01200 table.
-Victoria
Hi Victoria,
Does the email address field in the SY01200 table allow for multiple email addresses if separated by a semicolon?
Thanks,
Sarah
Hi Sarah,
Yes.
-Victoria
Hi Victoria,
I am currently working with GP 9.0 and implementing Project Accounting series. Among the prerequisite activities is the initialization of the the inventory master. the reason we need to start at zero, is because the company i am working for right now did not use the Inventory Control module before.
The list of inventory items is very long and if we will enter it the standard way it will take a some time before data entry is finished. I was wondering if there was a way to make things faster like through an SQL script or through Integration Manager. Furthermore, which inventory tables need to be updated to initialize the inventory control module.
Thanks in advance for your help.
Steve
Hi Steve,
I would not recommend initializing the Inventory module using tables, you should do this through the GP user interface. Similarly, there are so many tables involved with inventory items, that importing data directly into tables would be a nightmare and could cause issues if it’s not done properly. Integration Manager can definitely help with this. If you are not familiar with GP’s Inventory module and its setup, I would recommend a few things:
-Victoria
Thanks for your reply.
- Steve
Hi Victoria,
I am not sure if this is the right place to post this question. We are using GP 10. I was wondering if it would be possible to give someone “Read only” access to everything in GP? If so how would I do that? Thanks for your help.
Steve
Steve,
There is a way to do it, but it’s going to be manual and tedious because there is no global ‘read only’ option or setting. Security in GP is by windows – you either have access to a window or you do not. If you have access to a window, you can do anything that the window allows (this can be somewhat mitigated with field level security, but I do not believe that will be of help with what you’re asking). So to accomplish read only access you need to restrict a user to inquiry windows and reports. There is no default role with this this, so you would have to create your own security role (and possibly tasks) that only allow access to inquiry windows and/or reports.
-Victoria
Victoria,
Thanks for your help.
Steve
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
Hi Vic,
You should be able to get this with the Items SmartList.
-Victoria
Hello;
I am new to the company and GP. I noticed the ODBC access to GP tables are locked. The IT / consultants are reluctant to grant me permission due to risk of jeopardizing the integrity of the GP tables. Is it not possible to have “read only” ODBC access to avoid ruining the table and field structures? I used to run “Read only” queries against SSA LN in other companies without any issues.
Many thanks in advance
Hi Rahmanfard,
Using a Dynamics GP login it is not possible to get to the GP SQL data, so separate security would need to be set up to accomplish this. This should be possible to set up as read only, however there may be many other considerations. For example, depending on what you need to access, it may be quite time consuming to set up permissions, as this may need to be done one table or resource at a time (there are typically over 1,000 tables and 20,000 stored procedures in a GP company database). In addition, many companies have strict policies about who can access the ‘raw’ data and how.
-Victoria
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.
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
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
Hi,
I want to create a report which is provide a vendor wise sales.I am
using following tables in my query. But it was not give me a proper data for vendor wise sales.
Please guide me which link is better to join these tables.Otherwise you can suggest me which are the tables I have to use to get this report.
table IV30300
table IV30301
table SOP30300
table IV10200
table IV10201
Thanks & Regards,
Tanuja
Tanuja,
This is a pretty difficult report and may depend greatly on exactly how data is being entered into GP. This will not actually give you any amounts, you’ll have to add a number of columns, depending on what data you need, but as a start, try something like this:
SELECT SH.CUSTNMBR Customer, S.SOPNUMBE Invoice,
S.ITEMNMBR Item, P.VENDORID Vendor,
I.RCPTNMBR Receipt, P.PONUMBER PO
FROM SOP30300 S
INNER JOIN
SOP30200 SH
ON SH.SOPTYPE = S.SOPTYPE
AND SH.SOPNUMBE = S.SOPNUMBE
LEFT OUTER JOIN
IV30301 I
ON S.SOPNUMBE = I.DOCNUMBR
AND S.LNITMSEQ = I.LNSEQNBR
INNER JOIN
IV30300 IP
ON I.RCPTNMBR = IP.DOCNUMBR
AND S.ITEMNMBR = IP.ITEMNMBR
LEFT OUTER JOIN
POP10500 P
ON P.POPRCTNM = I.RCPTNMBR
AND IP.LNSEQNBR = P.RCPTLNNM
WHERE S.SOPTYPE = 3
-Victoria
Hi Victoria,
I tried above queary, but it didn’t work with my requirments.
My required fields are as follows.
Item No
Item Desc
Item Class
Site ID
Invoice No.
Customer Customer No.
Vendor ID
Vendor Country
Item Class
Qty Sold
Sales Amt
Trade Disc.
Unit Cost
I used following tables in my query.
IV10200,IV10201,IV30300,IV30301,SOP30300,SOP30200,RM00101,IV00101,IV00102,PM00200
I got 90% result with above tables, but i could not able to get 100%.
Please can you give me other hint for this report.
I tried this report from last one month, but I am not able to get it properly. Please help me or suggest me what can I do for it.
Thanks & Regards,
Tanuja
Tanuja,
I would say at this point we’re a little past what I would be able to help with in a blog post. There are some very complicated relationships between these tables and depending on how data is entered into GP and the logic needed for the report, this could get very complex.
-Victoria
Victoria,
I also feel like that. Because I tried every possibiltes for it.
Thanks for your support and time.
Tanuja
Hi Victoria
Can you tell me where i can find the default bill to email and default ship to email in gp 2010. I have to update all of our customers emails
thanks
Vic
Vic,
Since there are so many e-mail addresses now…can you specify where exactly you enter these e-mails in the GP user interface?
-Victoria
Victoria
Thanks for quick response!! Under the customer card under internet information. You select with which address ID then you can enter internet info for that particular address.
thanks
Vic
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
Hi Victoria,
I do have another question for you….but it doesn’t have to do with the tables in Canadian Payroll (that mystery is still in discovery mode), but since you are the only one I seem to have found that has experience in Cdn Payroll for GP, do you know how to deal with Garnishments and/or Family Support Payments with Cdn Payroll…the kicker is we are not using the HR module, just the Cdn Payroll and it seems garnishments are setup via HR. Is there a work around for this if you are not using HR with Cdn Payroll?
Dawn
Hi Dawn,
I am sorry if what I wrote lead you to believe I have experience with Canadian Payroll, I do not.
I am not sure how much knowledge you’re going to be able to find out on the internet for this – have you talked to GP Support to see if they can help you with these questions? While not free, that may be the best way to go.
-Victoria
Hi,
The Table info is very useful…thank you…do you have any further information on Canadian Payroll tables and their transaction flow? Doesn’t seem to be a lot of documentation or discussions on Canadian Payroll. Any insight into Cnd Payroll tables and transaction flow would be most useful.
Sorry Dawn,
I do not have any information about Canadian Payroll…I see you also have a post about this on the GP Forum, you may need to check with GP Support on this one to get the information you need.
-Victoria
Hi Victoria,
This is my first comment at your site. I have registered it last year. I have learned a lot from this site about GP tables and writing views and proceedures. I have a query and that is “IS there any best way you know that I can study the relationships between tables easily?”
Thanks,
Waseem
Hi Waseem,
Glad to here you are finding my blog useful! There is really no shortcut for learning the GP tables. Sounds like you are on the right track and already using online resources that are out there, other than that it’s just experience and practice.
-Victoria