Commonly Used Tables – Company databases:
CO00101 – Document Attachment Master
CO00102 – Document Attachment Reference
SY00500 – Batch Master
SY00600 – Location/Address Master
SY01100 – Company Posting Account Master
SY01200 – Internet Addresses
SY01401 – User Defaults
SY02200 – Posting Journal Destinations
SY02300 – Posting Settings
SY03000 – Shipping Methods Master
SY03100 – Credit Card Master
SY03300 – Payment Terms Master
SY03900 – Record Notes Master
SY04200 – Comment Master
SY06000 – Address EFT Master
SY40100 – Period Setup
SY40101 – Period Header
TX00101 – Tax Schedule Header Master (header)
TX00102 – Tax Schedule Master (detail)
TX00201 – Tax Detail Master
TX30000 – Tax History
Commonly Used Tables – System (DYNAMICS) database:
ACTIVITY – User Activity
SY003001 – Account Definition Header
SY00302 – Account Definition Detail
SY00800 – Batch Activity
SY00801 – Resource Activity
SY01400 – Users Master
SY01402 – System User Defaults
SY01403 – User Tasks (Reminders)
SY01404 – Customer Reminders Setup
SY01500 – Company Master
SY01990 – Shortcut Bar Master (Navigation Pane Shortcuts)
SY07130 – Navigation Bar Buttons
SY60100 – User Access
Security Tables in GP 10.0 and higher – System (DYNAMICS) database:
SY09000 – Task master
SY09100 – Role master
SY09200 – Alternate or modified form and report ID master
SY10500 – Role assignment master
SY10550 – DEFAULTUSER task ID assignment master
SY10600 – Tasks assignments master
SY10700 – Operations assignments master
SY10750 – DEFAULTUSER task assignment
SY10800 – Alternate or modified form and report ID assignment master
Email Tables (in GP 2010 and higher)
SY04900 – Email Options
SY04901 – Email Messages
SY04902 – Email Series Setup
SY04903 – Email Series Documents
SY04904 – Email Card Setup
SY04905 – Email Card Documents
SY04910 – Email Details
SY04911 – Email Temp
SY04912 – Email Attachment Temp
SY04915 – Email History
Document Attach Tables
CO40100 – Document Attachment Setup
CO00101 – Document Attachment Master
CO00102 – Document Attachment Reference
CO00103 – Document Attachment Properties
CO00104 – Document Attachment Status
CO00105 – Document Attachment E-mail
Official list of BCHSTTUS (Batch Status) in SY00500:
0 – Available
1 – Marked to Post
2 – Available / Delete
3 – Marked / Receive
4 – Marked
5 – Marked / Print
6 – Marked / Update
7 – Posting Interrupted
8 – Journal Printing Interrupted
9 – Table Updates Interrupted
10 – Recurring Batch Error – Edit Required
11 – Single Use Error – Edit Required
15 – Computer Check Posting Error
110 – Checks Printing (this is the status you will see for a computer check batch after the checks are printed)
130 – Remittance Processing
Francisco’s list of BCHSTTUS (Batch Status) in SY00500:
[This is a much more comprehensive list posted by Francisco Hillyer in a GP Newsgroup – provided courtesy of e-mail by Robert Cavill – thanks Robert and Francisco!]
0 – Available
1 – Batch Posting
2 – Batch Being Deleted
3 – Batch Receiving Transactions
4 – Batch Done Posting
5 – Being Printed
6 – Being Updated
7 – Interrupted While Posting
8 – Interrupted While Printing
9 – Interrupted While Updating
10 – Recurring Batch Errors / Transactions Did Not Post
11 – Single Batch Error / Transactions Did Not Post
15 – Error While Posting Computer Checks
20 – Interrupted While Processing Computer Checks
25 – Interrupted While Printing Computer Checks Align
30 – Interrupted While Printing Computer Checks
35 – Interrupted While Printing Computer Checks Align Before Reprint Computer Checks
40 – Interrupted While Voiding Checks
45 – Interrupted While Reprinting Checks
50 – Interrupted While Processing Remittance Report
55 – Interrupted While Processing Remittance Report Align
60 – Interrupted While Printing Remittance Report
100 – Processing Computer Checks
105 – Check Align Being Printed Before Print Checks
110 – Printing Computer Checks
115 – Check Align Being Printed Before Reprint Checks
120 – Voiding Computer Checks
125 – Reprint Computer Checks
130 – Remittance Report
135 – Printing Remittance Align Form
140 – Printing Remittance Form
Series (in most tables):
1 – All
2 – Financial
3 – Sales
4 – Purchasing
5 – Inventory
6 – Payroll
7 – Project
10 – 3rd Party
Series in TX30000 table:
1 – SOP
2 – Invoicing
3 – Sales (RM)
4 – Purchasing (PM)
5 – General Ledger
12 – POP
BACHFREQ (Batch Frequency):
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous
UserStatus in SY01400 table:
1 – Active
2 – Inactive
3 – Lesson
MODULE1 in Email Tables:
9 – Receivables Management
11 – Sales Order Processing
12 – Purchase Order Processing
19 – Payables Management
EmailDocumentID in Email Tables:
Module 9 – Receivables Management
1 – Invoice
3 – Debit Memo
4 – Finance Charge
5 – Service/Repair
6 – Warranty
7 – Credit Memo
8 – Return
10 – Customer Statement
Module 11 – Sales Order Processing
1 – Quote
2 – Order
3 – Invoice
4 – Return
6 – Fulfillment Order
Module 12 – Purchase Order Processing
1 – Purchase Order
Module 19 – Payables Management
6 – Remittance
EmailDocumentFormat in Email Tables:
1 – DOCX
2 – HTML
3 – PDF
4 – XPS
—
Last Updated: Aug 12, 2021
Victoria Yudin 
I had a user get a receivables batch get stuck certain receipts managed to post so now we are trying to delete the duplicate receipts via the Receivables Posted Transaction Maintenance window but upon trying to void we get a “This document is being edited by another user.” We have cleared the 00801,00800, tempDB tables and nothing seems to want to make it budge. Any pointers?
LikeLike
Hi Jimmy,
This is the kind of situation that usually involves checking data in a dozen tables and potentially fixing things directly in the database. Most likely someone will need to look directly at your data to fix it. I would recommend reaching out to your GP partner or Microsoft support for this.
-Victoria
LikeLike
Thanks Victoria for all of your blogs/ post about table structure! I did have a question about the Document Attachment Management Window. If the user attaches a document then changes the file name (so it is not just the default pdf name) where is that name stored? I cannot find it in the CO tables unless I am missing it.
Thanks
LikeLike
Hi Casey,
My understanding is that any doc attachments are uploaded to SQL as a “blob”, it’s not simply a pointer to the original file. So anything you do to the original file no longer has anything to do with what is stored in GP as the document attachment.
-Victoria
LikeLike
Hi Victoria, I’m fairly new to GP and I’m trying to build a report for a user. I’m looking for batch comments related to Payables transactions. The only place I have seen batch comments is in the SY00500 table; however it appears that this table only temporarily stores the batch info. Where are BCHCOMNT stored that can be referenced for reporting? Thanks in advance.
LikeLike
HI Steven,
I do not believe batch comments can be seen after the batch is posted, I think they are deleted. So depending on what you’re trying to track and report on, you may need to find a different way to do it.
-Victoria
LikeLike
Is there a listing of the values for the field ORIGIN in SY00500? It appears that payments are 2, correct?
LikeLike
Hi Lori,
I believe that value will depend on the type of transaction, so it’s not a unique list. What are you trying to accomplish?
-Victoria
LikeLike
Victoria,
Is there a table that stores the users usage, login date & time and duration of their session?
Regards,
Raul
LikeLike
Hi Raul,
The ACTIVITY table in the system database (usually called DYNAMICS) will show you the current users logged in with the company and login date and time. Not the duration, you’ll have to do that math on your own if you want. You might find this helpful, as well: https://victoriayudin.com/2011/09/12/sql-view-for-user-activity-in-dynamics-gp/
-Victoria
LikeLike
Victoria,
Thank you for your prompt reply as always. I assume that table gets refreshed daily and does not keep more than the current date. I’m trying help with an audit in tracking down how many times a user logged in and how long he stayed logged using the system. Not sure if anything out there maintains and keeps this information for more than just one day like the ACTIVITY table.
Regards
Raul
LikeLike
Raul,
This is actually a current table, it gets updated every time someone logs in or out. Dynamics GP out of the box does not keep any history for logins. That may have been set up for you or you might have that tracked by SQL server, but that will be something you have to look at your specific environment for, as it will very much depend on your setup.
-Victoria
LikeLike
@Raul you can easily create a trigger on SY00500 and insert the records into your own audit00500 table, you then can keep times and activities, but beware when users Force Close GP, because then the table will not be updated. You can also extend to which company and more.
LikeLike
please add SY07130 here, i am frequently use this table to refresh navigation bar buttons. Thanks
LikeLike
Hi Vira,
You got it. Thanks for the suggestion.
-Victoria
LikeLike
HI Victoria,
where would I find the Login date for the user in SQL ? What I mean by the date is the one found at the bottom left in Dynamics GP next to the logged company. It’s not the same date as what’s found in the ACTIVITY table.
LikeLike
HI Dominic,
I believe that date is called the “User Date” and I am pretty sure that is not stored anywhere in the database. 😦
-Victoria
LikeLike
hi, i am using gp2016, as i am attaching documents my db size is increasing too much, is there any way that GP can store only link of attachment which is stored in share folder, instead of saving document in DB.
thanks
LikeLike
Hi Khalid,
I don’t believe you have any choice with Document Attachments – they are stored in the database. You can choose to store links in either the Internet Information fields or Extender windows. However, both of those are much more limited in availability and/or functionality.
-Victoria
LikeLike
Hello please add table sy00600 as company address
LikeLike
Hi Daniel,
Good idea – done!
-Victoria
LikeLike
Hello Victoria,
I’m in Cash Receipts Inquiry and there are 2 fields I can’t find in RM tables. They are fields from our main operating system that feeds GP. The Window has “Movers Custom Program” in the title. Where can I find any info about this override window? I suspect that that is where the 2 fields are pulled in.
Thanks.
LikeLike
Hi Glen,
This sounds like a custom window in your Dynamics GP. It might be an Extender window, it might be part of an ISV product. So without knowing how it was set up, there is no way I can help you. However, I will say that it’s very doubtful that you would find these fields in the RM tables, since those are typically where the Dynamics GP out-of-the-box fields are.
-Victoria
LikeLike
Thanks for the quick response. I’ll talk to our GP vendor.
LikeLike
Hi Victoria,
I have two users with identical security permissions in the same company, yet one only has option to Attachment Management Inquiry while the other has both, Document Attachment Management and Inquiry. Have you come across anything like this and do you offer a solution?
Regards,
Raul
LikeLike
Hi Raul,
I would start by creating a brand new user in GP and copying the permissions from the user for whom this is working. Hopefully you are on one of the newer GP versions where you can copy the user security settings on the User Setup window.
If that new user works, then my next step would be to re-copy the permissions for the user it is not working for.
If that still does not work, it may be that you have a corrupted GP user. It does not happen often, but I have seen it here and there throughout the years. If you can, create a new GP user for that person. If you must stay with the same User ID, then you can try deleting the existing one and recreating it, then copying the permissions from the working user again.
Hope that helps,
Victoria
LikeLike
Victoria,
Creating the new user and copying permissions resolved my issue, thanks for your help as always!
Regards,
Raul
LikeLike
Hi Victory,
Your site has been a huge help with learning GP table structures. Where in the database does GP store the user information for last modified? For instance, I need to get the user who last modified customer information? Or Order information? Thank you!
LikeLike
Hi Brandon,
Most changes in Dynamics GP do not get stored or tracked. For the ones that do, every ‘object’ will store this in a different table. For customer information, GP only stores the last modified date. For sales orders only the user who entered the order is tracked, not the user who modified it.
There are various custom options for tracking changes, depending on what you need. I would recommend talking to your GP partner about this to get more details specifically for your situation.
-Victoria
LikeLike
Victoria,
Long time lurker here finally needing some advice. We have Dynamics 2010 with around 300 companies. Often times we add multiple companies at once. We have several accountants who need the same access to those multiple companies. Instead of having to go through one by one and setting security up per user through the GP program, is there a way to systematically setup the security permissions for new companies to all users at once via SQL? I’m thinking of copying existing data from the SY60100 table and reinserting with the correct userid, as well as in SY10500, but I don’t know if there’s a better way, or if you may have something already precoded that can do this. It would save me a lot of time! Thanks
LikeLike
Hi Dominick,
I have not done something like this before, so I don’t have an answer for you. What you could do is use SQL Profiler to figure out everything that is being updated when you do this manually and then replicate that in SQL. Not necessarily an easy task, but might be worth it in the long run for you.
-Victoria
LikeLike
Hi Victoria
Would you be able to advise of a script to select all users from a specific GP company and their associated roles and tasks (with task ID and Task name)?
Nic
LikeLike
Hi Nic,
Do you mean something like this: https://victoriayudin.com/2009/03/12/sql-view-to-show-security-roles-and-tasks-in-dynamics-gp-10/ ?
-Victoria
LikeLike
I’m looking for information on “staging” tables CF01PMHD and CF01MPLN , they supposedly exist in GP Dynamics – are you familiar width these designations? Can you tell me where to find them?
Thanks, Roger
LikeLike
Roger,
I do not believe these are standard GP tables, the structure is different from the standard GP table naming convention and I have not seen these before. That’s certainly not a definitive, but that means if they are part of standard GP, they are part of a module that is not widely used. When I hear ‘staging tables’, I typically think of custom integration projects.
-Victoria
LikeLike
Victoria –
Hello… had a question regarding system roles. We are undergoing an audit for SOX compliance, and one of the things they’re asking for is a report of what roles were added or removed from users over a given time range. I can find the roles that a user belongs to in dynamics..SY10500 however this table unfortunately does not include a date.
Do you know of any way to get this information? Basically we just want to know when a role was added to a user account.
thanks! 🙂
LikeLike
Hi Craig,
Unfortunately, Dynamics GP does not store this kind of data. To be able to report on it in the future, you could either add some custom tracking in SQL, or keep a record of all security changes outside of GP. My customers that get audited on security typically opt for the latter, as they also keep documentation of the requests and approvals for security changes.
-Victoria
LikeLike
Hi Victoria, I got the data dump from GL20000 table and looking for transaction entry and posting dates. I got the ‘TRX Date’ and ‘ORPSTDDT’ from the extracted data but in some cases, the TRX Date is greater than ORPSTDDT while in some transactions it is vice versa. Can you please advise as I’m a bit lost on that one.
LikeLike
Syed,
The TRXDATE is the General Ledger Posting Date and can be specified when you are entering the transaction. The ORPSTDDT is the system date when the transaction was physically posted. There is no rule that one should be before or after the other, these are completely independent.
-Victoria
LikeLike
Hi Victoria,
I am in the process of copying setup tables into another entity on Microsoft Dynamics GP 2010. When doing this, do you know if everyone needs to be out of the system or can I just perform this action during normal business hours while other users are in the system?
LikeLike
Matt,
Users only need to be out of the ‘destination’ company in GP. I don’t see any reason they cannot be in the ‘source’ company if you’re simply copying from there.
-Victoria
LikeLike
Victoria, hope all is well. Need your expertise.
The TX30000 table is great to use in determining monthly sales tax calculations. I believe this table is the one used in the GP Smartlist “Tax Detail Transactions”. However, there is a missing component – the state where the sales order shipped to.
I tried pulling in the ship to state from SOP30200 which works fine. But this does not include anything created in RM. You must include RM30101 to include any credits issued or documents outside of SOP. Problem is, the results coming from RM30101 are returning NULL values from the CUSTNMBR for instance. Currently my script is as follows:
SELECT TOP (100) PERCENT dbo.TX30000.DOCNUMBR, dbo.TX30000.DOCDATE, dbo.SOP30200.CUSTNMBR, dbo.SOP30200.CUSTNAME, dbo.SOP30200.STATE,
dbo.RM30101.CUSTNMBR AS Expr1, dbo.TX30000.VOIDSTTS
FROM dbo.TX30000 FULL OUTER JOIN
dbo.RM30101 ON dbo.TX30000.DOCNUMBR = dbo.RM30101.DOCNUMBR FULL OUTER JOIN
dbo.SOP30200 ON dbo.TX30000.DOCNUMBR = dbo.SOP30200.SOPNUMBE
WHERE (dbo.TX30000.DOCDATE > CONVERT(DATETIME, ‘2015-01-31 00:00:00’, 102) AND dbo.TX30000.DOCDATE < CONVERT(DATETIME, ‘2015-03-01 00:00:00’, 102)) AND
(dbo.TX30000.VOIDSTTS = ‘0’)
ORDER BY dbo.TX30000.DOCNUMBR
What am I doing wrong????
LikeLike
Hi Mark,
A couple of thoughts:
selectt.CustomerVendor_ID, t.DOCNUMBR, t.DOCTYPE,
t.DOCDATE, t.TAXAMNT, t.Taxable_Amount,
s.CUSTNAME SOP_Cust_Name, s.[STATE] SOP_State,
r.CUSTNMBR RM_Customer_ID
from TX30000 t
left outer join
(select CUSTNMBR, RMDTYPAL, DOCNUMBR
from RM30101
union
select CUSTNMBR, RMDTYPAL, DOCNUMBR
from RM20101) r
on t.DOCNUMBR = r.DOCNUMBR
and t.SERIES = 3
and t.DOCTYPE = r.RMDTYPAL
left outer join SOP30200 s
on t.DOCNUMBR = s.SOPNUMBE
and t.SERIES = 1
and t.DOCTYPE = s.SOPTYPE
where
t.DOCDATE between '2015-02-01' and '2015-02-28'
and t.VOIDSTTS = 0
order BY t.DOCNUMBR
Hope that helps.
-Victoria
LikeLike
In previos post I neglected to say that I know I am looking to give him access to the Account Maintenance window. I just can’t locate it.
LikeLike
Steven,
It might be best to simply create a new task and role with the Account Maintenance window.
-Victoria
LikeLike
Victoria,
I have been asked to give user access to create financial accounts in GP. I am unsure what this task is actually called so that I can locate it in Security Tasks or Roles in order to identify what I need to add to the user account to allow this access.
LikeLike
Thanks for the quick response, i’m glad i asked!
LikeLike
Hello Victoria, I have a question regarding the exchange rate table MC00100, it resides in the dynamics database; so does that mean it is shared between companies? We need to do some testing in our TEST company with different exchange rate scenarios if we change the rates in the TEST company will it effect our LIVE company?
LikeLike
Hi Dsammie,
Correct, Exchange Rates are shared between companies. If you change existing rates in TEST, they will also update LIVE. If you need to test things, you could create new exchange rate tables that are not active in the live company. Or you could set up a separate development/test server where you can have an exact copy of your live setup to test with.
-Victoria
LikeLike
Hi Victoria,
I apologize if I posted this twice, but couldn’t find my posting from the other day.
I have a batch in SY00500 which does not show up in Batch Recovery. It has a Batch Number of ‘GL Close’ so obviously was generated by a previous close. The Series is 0 as is the Batch Total and Batch Status.
Do you have any recommendations on how to delete this without breaking a table link?
thx,
Doug
LikeLike
Hi Doug,
I’ve not seen a GL Close batch get stuck before. Is there anything in the GL10000 or GL10001 tables with that as the BACHNUMB?
-Victoria
LikeLike
Hi Victoria,
Is there a way to tell what forms have been modified? i.e. is this stored in a table somewhere that I can query?
Thanks for the great table list!!
LikeLike
Darhl,
I don’t believe this information is stored in a table. You can see the list of modified forms by going to Microsoft Dynamics GP | Tools | Customize | Customization Maintenance. This will be specific to the Reports and Forms dictionaries being pointed to by the GP install you are in. In other words, it could be different on each computer that points to a different set of .dic files.
-Victoria
-Victoria
LikeLike
Thanks for getting back to me, that may help.
We run in a terminal services environment, and I keep all of the terminal servers identically configured.
LikeLike
Hi Victoria, in which tables can I find the batches sitting in the Batch Recovery window?
LikeLike
Hi Lyn,
The batch headers will be in SY00500 table.
-Victoria
LikeLike
Thanks, Victoria! So I’m thinking the only way to create a report of all batches sitting in the Batch Recovery window is to filter out Batch Status codes 7 to 11, would that be right?
LikeLike
Hi Lyn,
I don’t think I’ve ever made a direct correlation of what batch status codes show up in Batch Recovery, sorry. I personally always look at all batches with either an error state or a batch status not equal to 0, for example:
select ERRSTATE, BCHSTTUS, * from SY00500where ERRSTATE 0 or BCHSTTUS 0
-Victoria
LikeLiked by 1 person
Hey Victoria,
When I do a “select *” query from DYNAMICS..SY00801, the query just hangs up (i.e. it never returns a value, and I just have to cancel the query). What could cause this??
Thanks!
LikeLike
Bill,
That table should not have that much stuff in there, so I cannot imagine what would cause that. How much time are you giving it? Where are you running the query? Is it in Microsoft SQL Server Management Studio, or somewhere else? Are you directly on the SQL server? What if you first change the database at the top to DYNAMICS, then try the following:
select count(*) from SY00801
-Victoria
LikeLike
Hi Victoria,
Is there away to give someone read only status to specific cards in GP13? I would like someone to be able to view bank EFT Remit info but not allow editing.
LikeLike
Damian,
Dynamics GP does not have any easy way to give someone read only access to a window where you can normally edit information. I typically recommend a report for this type of request – that will show all the information without any ability to edit it.
-Victoria
LikeLike
Victoria,
We have a batch in Master Posting whose status is “Unequal Actual/Control Transactions”. This is an inventory batch. When I look at the Posting Setup for Inventory, no check boxes are checked for “verify number of tx” and “verify batch amounts”. What else might be causing this to occur? Thanks!
LikeLike
Hi Bill,
Even if you don’t have the verify options checked on the posting setup, if you enter control totals on a batch and they do not equal the actuals, I believe you will see this happen. You can open the batch, clear out the control totals and then you should be able to post the batch.
-Victoria
LikeLike
Victoria, thanks for your prompt response. During the first attempt to print these checks, the user did not get a prompt to choose the printer; upon re-printing, GP goes through the process of assigning check numbers, but they do not print and again there is no prompt to choose a printer. I had the one user who ran the check batch exit GP and then cleared TempDB..Dex_Session; Temp.DB..Dex_Lock; SY00800, and SY00801, though this didn’t seem to help.
LikeLike
Hi Ana,
I am not sure if this is something that we can troubleshoot in blog comments, I think we may need to look at it in your GP to be able to determine what is happening.
-Victoria
LikeLike
Hi Victoria, we have a check batch that is stuck with a 110 status; yet no checks have printed. We have tried to reprint; the system goes through the process and still no printed checks.
1) We are unsure if the checks are printing to some other printer; how can we verify that?
2) Confirmed the PM10300 contains about 126 checks
3) Is it a matter of updating the SY00500 table and changing the batch status to 0?
Any suggestions?
LikeLike
Hi Ana,
You can certainly try changing the batch status to 0 in SY00500 to see if that would help. I would also try to get all users out of GP and clear the five ‘activity’ tables. You are on GP 2013, so somewhere in the check printing process it should ask you what printer to send the checks to – is it not getting that far? If so, it’s definitely not printing to another printer.
-Victoria
LikeLike
Victoria,
Thanks for your help! I was hesitant to try and forcfully delete anything but now I’ll definitely defer to the experts and call our GP partner. Thanks again!
LikeLike
Victoria,
They’re check printing batches. I’ve yet to see if after the batch is deleted in the UI, it’s gone in the SY00500 table. The user went back into GP before I could look. So the process was to reset the batch, have user delete from UI, they exit GP, go back in and the batches are back. I’ll have her delete them again and see if they’re still in the table. If this is the case, what are your thoughts? Thanks!
LikeLike
Dave,
Actually, check batches are a bit more ‘special’. If it got stuck in the middle of posting after the checks were printed, which is what I am guessing, you will not be able to delete it. If this is the case, I eould recommend working with your GP Partner or Dynamics GP Support to help you with it. While I have fixed a number of these types of issues in the past, there is no one fix that I can recommend, as this always involved examining a number of related tables to determine what was causing the posting problem.
-Victoria
LikeLike
Victoria,
I have a strange issue where 2 batches will not delete. We have reset the batch status and marked to post status to zero in the sy00500 table and within the application they both appear to delete. Problem is, next time this user opens the app the batches reappear. I’m hesitant to just delete them from the table as I assume there is a reason for this behavior. Any ideas? Thanks!
LikeLike
Hi Dave,
Are you saying that after you change the values in the SY00500 table you then go into the GP application and delete the batch? And then it is not in the list of batches in the UI or in the SY00500 table anymore? But then, later, when a user logs into GP it’s there again? If I misunderstood, can you please elaborate? Also, what kind of batch is it?
-Victoria
LikeLike
I have multiple companies in our current installation. Is there an easy way via SQL to find out which users have access to each different company and what roles they are assigned in each company?
LikeLike
Nick,
Take a look at the security views I have posted on my System/Setup SQL Code page.
-Victoria
LikeLike
Perfect! Thanks for the fast response.
LikeLike
Hi Victoria,
We are an export company so we do zero rated in our invoices, but now we already have local customer’s so it mean’s that VAT can be included in invoice. Is there need to setup or initialize in GPD so that when we generate invoice it will automatically compute the VAT if it is local costumer and zero rated if export?
Any suggestions from you would highly appreciated…..Thanks.
RolanVH
LikeLike
Hi Rolan,
I believe you should be able to set up Dynamics GP to calculate taxes properly. Taxes in Dynamics GP are calculated based on 3 things: item setup, customer setup and shipping method. I would recommend reviewing the documentation and talking to your Dynamics GP partner if you need help on setting this up properly.
-Victoria
LikeLike
thank you victoria for the suggestions and quick reply..more power and god bless….
RolanVH
LikeLike
Hi Victoria,
Might seem like a bit of a daft question, but can you advise how the Master_Type field of the SY01200 table is set when a customer record is created? We’ve got a client whose debtor records all have this field set to VEND rather than CUST for their corresponding records in the SY01200 table and not sure how to modify this behaviour from the GP interface.
Can you please advise?
Thanks.
Lev
LikeLike
Lev,
I do not know the precise logic of how the code sets these, however, it should be something as simple as, ‘if you’re on the customer record, insert CUST’. I have not yet seen these populated incorrectly by GP out-of-the-box, can you add a new customer record and internet information manually in the GP UI and duplicate this behavior?
-Victoria
LikeLike
Victoria,
Have you seen this error before.
I have setup Intercompany relationships in Great Plains 2010.
I am receing the error “Destination account is required for distribution account:
1000000-2001-000” (account numer).
The destination account is setup in the company that I am trying to enter an IC trx to, but I am still receing the message.
LikeLike
Hi Jeff,
This is difficult to troubleshoot without looking at it. I would double check your Intercompany setup window to make sure everything is set up correctly for both the companies.
-Victoria
LikeLike
hi again victoria,
hopefully this one is an easy one..though if it was i would have expected google to answer me! =).
I can not seem to figure out how to make the settings we want for our customer email setup to be the default when creating a new customer record. I would like the quote and order documents to default to the correct message ID and .PDF format.
I was able to go change our existing customers in bulk, but new customers dont have those options set, so the sales guys have to click on the email button on the customer card and set up the documents for each new customer. would really like to fix it so this happens by default. thanks!
-jon
LikeLike
Hi Jon,
I am not aware of any setting in GP that would automatically select the appropriate email documents when a new customer is created. I think the best you can hope for is for the Message ID and Format to be defaulted in when you enable each document. (Which is done on the Sales E-mail Setup window – GP | Tools | Setup | Sales | E-mail Settings.)
-Victoria
LikeLike
ahh thats unfortunate =(. thank you for the fast reply!
LikeLike
Hi Victoria,
Many thanks for that, however it doesn’t quite do everything we rquire.
The SQL returns values per user, but only for smatlists saved against that user id. What I am looking for is a list of all the smatlists that users have access to, by user.
I realise this is a big ask, but any further help will be hugely appreciated.
Jerry
LikeLike
Jerry,
Unless I am misunderstanding what you’re asking, the results that you are looking for are a part of the results from my view. If you first create the view using the code on my blog, then you can simply run the following query:
If you have multiple companies, you might also want to add Company_Name to the ‘order by’…or you can restrict to just one company by adding that to the where clause.
-Victoria
LikeLike
Hi,
Is there a way of finding out which users on our system have access to which smartlists? I know it must in one or more of the tables, but it is like trying to find a needle in a haystack.
Many thanks in advance.
LikeLike
Hi Jerry,
Does this help: https://victoriayudin.com/2010/05/13/sql-view-with-security-and-smartlist-details-in-gp/ ?
-Victoria
LikeLike
Hi Vitoria
Many congrats for the MVP
I want to modify the “Trial Balance Detail” report in report writer and bring the “Record Note” from the originating subsidiary module. Please help me how can I link the “Year-to-Date Transaction Open” on the report to “Record Notes Master”.
Thank You
LikeLike
Because of the fact that there could be many different subsidiary modules and you would have to link to all of them, this is by no means an easy task. I do not believe you will be able to accomplish this in Report Writer without writing custom code. You might want to post your question on the GP Community Forum, as I do not work with Report Writer for anything this complicated and you might get some additional ideas there.
-Victoria
LikeLike
Hi Victoria,
Thank you very much for an Amazing Blog, I use it on a daily basis to resolve table issues as you have set it out so easily
I now however have a issue, more of annoyance really.
One of my clients is unable to sort by other segments and I have researched this propperly
Some obvious questions answered,
1. When the system was installed there was provision made for sorting by account segments in the Account Framework
2. I have run check links on the account Master file
I also found the following,
When sorting the account by Main segment it works perfectly in the screen (Account Summary) .
1. When I sort by other segments it doesn’t work, BUT after doing a custom sort by on the different segments the other work perfectly
So I am starting to suspect that when doing a custom sort by it pulls the data but when you just select the sort by it doesn’t?
Do you have a suggestion why this would be happening or how I can start looking to fix it?
LikeLike
Hi Chris,
I have seen this before, but I do not have a solution for it. I would recommend posting this on the partner GP forum to see if you can get some more guidance on this.
-Victoria
LikeLike
Hi Victoria,
I thank you so much for this reply
I found a solution, but it was a bit like using a Wrecking ball to hit a nail in, we just did a re-install of the Client and it worked.
Thank you again for this blog.
LikeLike
Hi Chris,
Thanks for the update. Glad you got it to work.
-Victoria
LikeLike
Thanks for your reply Victoria. I know how to setup the intercompany transactions in GP. However there is a requirement that different account numbers be specified for different transactions. I am aware that when running through the setup for the intercompany transactions only one account number can be specified. Hope it makes more sense.
LikeLike
Nish,
GP only allows for 2 different accounts – a ‘due to’ and a ‘due from’. If you need something beyond this, it will require either a customization or a 3rd party product. Or, alternately, if there are not too many of these, they can be entered manually without using the Intercompany functionality.
-Victoria
LikeLike
Hi Victoria,
I need to setup intecompany transactions between the GL and the accounts payable module in Dynamics GP 2010. The process seems failry simple however the accounts will vary in the one company from one transaction to the other. Please advise on the how we should go about setting that up.
Your help will be awesome
Nish
LikeLike
Hi Nish,
I am not sure what you’re asking. If you’re asking how to set up the Intercompany module, then I would recommend reading through the steps in the manual. If you have a specific question about one of the steps, please let me know. If you have complicated intercompany requirements, you may want to talk to your GP Partner about the setup so that you can explain what you need to them in detail and they can give you a recommendation for your specific situation.
-Victoria
LikeLike
Hi Victoria,
I would like to get some of the posting information added to the cross-reference report in GP. Mainly the Approved by and Approval Date fields that are on the posting edit list. Is there an easy way to do this?
LikeLike
Jay,
Do you mean batch posting approval? If so, I do not believe that is stored after the batch is posted.
-Victoria
LikeLike
Thank you for your effort in creating this list!
LikeLike
Is there a way or can you point me to the tables that will provide a list of modified reports. I saw you have SY10800 but can’t link that back to a useable name for the report as this table only references an ID.
LikeLike
Hi Allen,
I am not aware of a table that will simply store a list of modified reports. You may be able to cross reference SY10800 with table SY09400 to get the report names.
-Victoria
LikeLike
Hi Victoria,
I have experienced a situation where a general ledger journal is captured manually with both Debits and Credits, but on printing the Transaction Edit List the debits and credits do not appear. I have run a reconcile and checklinks but to no avail. What could be the cause and the solution to this? We are on GP2010 running on SQL 2008 R2.
Regards,
Tumi
LikeLike
Tumi,
Have you confirmed that this is not simply an issue with the report? If you’re using a modified report, try switching back to the default one. Also, try printing the report on a different computer and with a different GP user to make sure it’s not an issue specific to a particular computer or user.
-Victoria
LikeLike
Hi Victoria,
I need an expert advice. We have 3 dental companies which was deployed to our local server. And now, we need those 3 companies to be deployed to the LIVE server.. We already have restored the 3 companies to LIVE server, and everything is doing good except for the currency we have seen.
The currency ID that we are using is Philippine Peso (P) but in the transaction amount, the australian currency had appeared. We cannot change it into P. Upon checking the currency setup, Philippine Peso symbol is P. Please help us.. Thank you.. 🙂
LikeLike
Scha,
All system-wide settings including users, security and multicurrency setup are stored in the DYNAMICS database. If you only copied over the company databases and not the DYNAMICS database, then none of the system-wide information came over to your live server. At this point, if you can redo the live deployment, you could restore the 3 company databases again, together with the DYNAMICS database. If it is too late because you have started entering transactions, then I would recommend working with your GP Partner or GP Support to straighten this out, as the fix could potentially be very complicated.
-Victoria
LikeLike
We have started using GP to send invoices as PDF to customers. In the process, some customer files have been updated to include the email address in the “To…” line of the E-mail address portion of the Internet Information page of the customer card.
I am trying to find the table and field in GP where that “To…” field is located so I can query and show which are completed and which need to be updated. We have instances where the E-Mail field in Internet Information is filled, but the To… field is not, so we can not rely on that.
Thank you
LikeLike
Hi Anthony,
The table is SY01200 and the field is EmailToAddress. My SQL view for SOP email setup in GP 2010 may also help with what you are looking for.
-Victoria
LikeLike
So I am not sure if this posted or not. I am trying to track down where an operation is tied to a task. “Inventory Transfer Entry” window is the operation access that is linked but I have not been able to determine which task linked to the roll is giving access. We are in the middle of an audit and I need to remove this access from our Controller’s roll. Any assistance is greatly appreciated.
LikeLike
Allen,
Have you tried this code: https://victoriayudin.com/2009/03/20/sql-view-with-security-resource-details-for-dynamics-gp-10/?
-Victoria
LikeLike
Thanks. That pointed me to/confirmed SY009400 is the table to use and I was able to find it. INQ_INV_004* provides the access to Item Transfer Entry. Oh how I love having a transactional entry window assigned by an inquiry access provision… Thanks again!
LikeLike
Correction “Item Transfer Entry” is the Window.
LikeLike
Victoria,
On GP 10 and have via audit found that we have an errant assignment of the window “Inventory Transfer Entry”. I need to track down where it is coming from. I have looked through many of the associated Role and Task ID’s but it only shows attachment to the users when printing the operation access report. I have looked into the SY009XXX tables and do not find the specific operation names. SY10700 doesn’t give names of the operations. Any pointers to what I am missing would be great.
LikeLike
Hi victoria,
I just want to ask what is the physical tablename of Payables Manual Payment Entry in Purchasing module? it is really frustrating to search for physical table names.
Thanks so much.
– Rhon
LikeLike
Hi Rhon,
It is PM10400. You can find commonly used Payables tables, including this one, on my PM Tables page.
-Victoria
LikeLike
Thank you so much victoria! your website really helps.
Rock en’ Roll!
– Rhon :)\m/
LikeLike
I have a user who made a payment to one creditor,the Creditor Information was updated correctly, But the amounts are not showing in the GL the trial balance report shows only transations up to the 6th February as with the new info. there is nothing.Any help in this?
LikeLike
Aubrey,
Have you confirmed these are not sitting in the General Ledger waiting to be posted?
-Victoria
LikeLike
Oops! There they are! Thanks
In GP 2010, Microsoft Dynamics GP | Tools | Setup | Company | E-mail Settings is not showing on the menu and the email options are greyed out on Debtors and Creditors cards. Do you have any idea what might cause this?
Thanks again,
Richard
LikeLike
Hi Victoria,
Do you know of any reason why the email option tables might be missing from my DYNAMICS database? Tables SY04900 to SY04915 are not present when I check the DYNAMICS database so I don’t have email options in GP. It is version GP 2010 with sp2.
LikeLike
Hi Richard,
Those are company specific, so they will be in the company database(s), not DYNAMICS.
-Victoria
LikeLike
Victoria,
Wonderful site, thanks for all the info!
I am upgrading from Version 8.0 to 10 (and then 2010). I get errors on Report Options (Purchasing, Inventory, RM) I have no problem recreating these if I can get them to upgrade. Also Distribution Account Master? Any thoughts greatly appreciated.
Debbie
LikeLike
Debbie,
Report Options might be ok to recreate, but I would be concerned about the Distribution Account Master. Did you perform database maintenance (specifically check links) prior to starting your upgrade? I find that usually helps avoid all sorts of issues like this.
-Victoria
LikeLike
Thank you for all your great answers. I have a batch in Financials that was created when I ran CheckLinks. The batch does not have any transactions or Batch ID. I try to select it to delete it but it tells me I have to make a selection. I have tried Reconcile but that comes up with no errors. I removed it with a SQL Query, then ran CheckLinks again and the batch came back. What can I do to make this batch go away and stay gone?
LikeLike
Hi Sue,
It could be that there are detail lines in the GL10000 and GL10001 tables that were never posted and until they are cleared out this will keep coming back. Without looking at these in detail and checking them against posted transactions in the GL20000 and GL30000 tables it’s hard to say whether these should just be cleared out or if something else needs to be done to fix them. If this is not easy to find, it may be a situation where getting GP Support involved might help.
-Victoria
LikeLike
Victoria –
Curious to know if there is a SQL that would return the total of a tax schedule ID (i.e. Tax Schedule ID = STATE SALES TAX + COUNTY TAX) on one line. I used one of your other SQLs that got me a listing of the tax schedules, but I have one line for my state tax and another for county. Would prefer they be one total.
Thanks in advance!!!
LikeLike
Mark,
I am not aware of any code that would do that for you. Part of the problem is that GP stored taxes at the tax detail ID level and you could have a tax detail ID that is part of more than one tax schedule ID, so this is not something that can be written generically. You would need to create some code that is specific to your data to show the tax this way.
-Victoria
LikeLike
Hi Victoria,
I use your blog a lot so thanks for all the information! I’m receiving lots of errors when in our test company related to WennSoft (3rd party). I noticed in the SY02100 table (DYNAMICS) that I have extra dictid’s in one company that is working and they are not there for the one that isn’t. How does the SY02100 get populated and how would I add a dictionary id to this table? The companies are reading the same set file (and same client folder) and are in the same SQL instance, so I’m not sure how one has a dictionary id that another company doesn’t have. Wennsoft is installed in both companies and all the setups are the same. I also dropped all wennsoft tables and stored procs in test company and re-created and this remains an issue.
11 836 10 0 TRI/dbo/ TRI dbo local 166
11 836 10 22006 TRI/dbo/ TRI dbo local 167
These are the 2 lines in the SY02100 table that are in the working company that aren’t in the test company.
Not sure if you can answer, but any help would be greatly appreciated!
LikeLike
Hi Alishia,
It sounds like maybe the test company did not get copied or created properly, but I have not worked with WennSoft, so I cannot offer more specific help on this. If you are getting WennSoft errors, first place I would check is with their support – they might have a quick and easy answer for you having seen this before.
-Victoria
LikeLike
Hi Victoria
I set time aside this weekend to update some pricing in my system. When i went to run integration manager i found out that my partner lied to me and said I had the full version of integration manager instead of the 240 day version. Now I am stuck. Are the price levels just in IV00108? If they are can i just write an update script myself to uptade that table??
Thanks
Vic
LikeLike
Hi Vic,
I have not updated prices directly in the tables myself, so I cannot tell you 100% it will definitely work. However, if all you are doing is updating existing records with new prices and you are using Standard Pricing in GP (as opposed to Extended Pricing), I don’t see why it would not work with a SQL script to update IV00108. I am sure you were already planning this, but I would make sure to have a good backup and try it on one or two records before updating everything. Good luck.
-Victoria
LikeLike
Hi Victoria
Thanks!! Through some major google searches and help from others like yourself in the great GP community, I pieced together a couple scripts that did the job. In fact I will be turning them into a repeatable stored procedure in the near future. One thing of note is that you also have to insert a record into IV00107 if it is a new record in addition to adding it to IV00108. Hopefully that info will help someone else out in the future. Also I used your excellent view view_Inventory_Price_Levels to help me accomplish this task!!!
Thanks again for all your work!
Vic
LikeLike
Hi Victoria,
I love your blog. It’s very informative :). I was wondering if it’s possible to void a posted cheque? We have an interco set up. The cheque on the AP side has been voided but we need to void the other company but that transaction has been posted and the period is closed. Is there anyway to void that portion? Thank you.
LikeLike
Hi Kevin,
A posted/historical check can be voided under Transactions | Purchasing | Void Historical Transactions. The check itself cannot be an Intercompany transaction unless you have a customization or 3rd party product doing that, in which case, all bets are off. 🙂
When you void the check, that will cause the invoice(s) it was applied to to become open again. If the invoice was Intercompany and you void that (under Transactions | Purchasing | Void Open Transactions), then I believe the Intercompany portion of that invoice will need to be adjusted manually in the General Ledger of the other company. This may depend on your GP version and various other setup, so you would need to see what happens with the void and proceed accordingly.
Hope that helps,
-Victoria
LikeLike
Hi Victoria….
Not sure if this would be the right place for this ?…..
GP v 9 – The GL Period Palance in Summary does not = the Period balance in Detail…the summary balance is correct. How do I get the detail to agree to the summary. I’ve determined the entry that is causing the difference…………Any suggestions
LikeLike
Colin,
Have you tried running Reconcile and Check Links on the Financial series?
-Victoria
LikeLike
Hi Victoria
Thanks for your kind response……
No I haven’t thought that there would be another option for the reason that I would need to take a backupup before I use this method…my backup process takes in the region of 4 hours. but am goint to try this now as I’ve restored by DEV server.
Thanks
Colin
LikeLike
Colin,
That doesn’t sound right…how big is your company database? How are you doing the backup?
-Victoria
LikeLike
Wow..thats fast…its 11.51 here in sunny SA.
I’ve restored from SQL back up. If I take a backup on the fly thats how long it take
my db size is app 260G
LikeLike
It is 6am in not yet sunny New Jersey. 🙂
Can’t say I have ever taken a backup of a db that big, but still 4 hrs sounds excessive. You’re doing a backup directly in SQL? What SQL version?
In any case, if your summary and detail are out of sync, reconcile and check links are the first 2 steps to try to fix that. I would start with reconcile first.
-Victoria
LikeLike
Victoria,
First, GP version 10.00.1061 (10 sp2 +)
The question:
Our users have recurring batches set up in payables module. Is there a way to have a recurring batch in the Sales module?
Todd
LikeLike
Hi Todd,
The Receivables module does have recurring batches, similar to Payables. However, I am guessing you mean SOP by ‘Sales’? If so, then no, there are no recurring batches there. You may be able to make use of the ability to create recurring quotes or orders, but that may be too limiting for what you need.
-Victoria
LikeLike
Hi!
I would like to place a new registration keys on GP 9. When I do this the Access Rights I assigned to the users where removed. How do I make sure it would not disappear? Is there a way to backup the access rights of the GP users?
LikeLike
Mick,
I have never seen new registration keys change GP completely remove user permissions. It sounds like something may have gone wrong in your case. I don’t know if there is a way that you can selectively back up permissions only, and easily restore them – it would depend on exactly what permissions got reset.
-Victoria
LikeLike
It happened more than once. When I loaded the new access rights. I needed to mark and unmarked access rights again to the windows of GP.
LikeLike
Mick,
This has not been my experience. However, since I do not know your environment or the specifics of your GP implementation, it’s really difficult to comment further. I would recommend talking to your GP Partner to see if they can help you determine why this is happening.
-Victoria
LikeLike
Is SY00500 the only table that contains batch data? I realize a transaction may have a batch number from SY00500 in the BACHNUMBR field, but that’s not what I’m referring to.
I’m moving invoices around programmatically using eConnect, and this causes some of my batches to become empty, which is intended and expected. eConnect doesn’t appear to provide a method to delete batches, and I want to simply use a SQL query to accomplish this.
Thanks in advance.
LikeLike
Hi Chris,
Yes, if you have moved everything out of a batch, I believe that deleting it from SY00500 should suffice.
-Victoria
LikeLike
Hello Victoria,
When i am trying to close FINANCIAL YEAR IN MS Dynamics Great Plains GP 10. Ver with SP2.
Analytical Accounting Module is installed….
The Error:
Microsoft ODBC SQL SERVER Driver SQL Server: ERROR CONVERTING DATA TYPE INT TO SMALLINT.
How can i get rid of this Error.
LikeLike
Akram,
Looks like you’ve already started a thread about this on the GP Community Forum, which is what I would have recommended. Hopefully you’ll get some help there.
-Victoria
LikeLike
Hi Victoria,
I recently upgraded GP 9 to service pack 2. I was able to upgrade my DYNAMICS database as well as 2 other company databases. I have a problem upgrading the third company database. When I ran update in GP Utilities, it stops in the “Load Additional Required Data” stage on table SY40100, then it gives me this error:
“The stored Procedure SynchronizeTableData() of form duSqlAccountSynch: 27 Pass through sql returned the following results: DBMS: 102, Microsoft Dynamics: 0”
I also noticed that there are SY40100_T and SY40100(V1) tables added in the database. Is it normal?
The DEXSQL log says “exec duAN_SynchronizeTable ‘SY40100′,’SY40100_T’,’D’
then it stops with this log:
SQLSTATE:(01000) Native Err:(15472) stmt(16657232):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]The object ‘(null)’ does not have any indexes, or you do not have permissions.*/
Any help will be highly appreciated.
Thanks much ^_^
PJ
LikeLike
Hi PJ,
One thing that I have seen cause issues with service packs and upgrades is if DYNSA is not the owner of the SQL databases. However, I have not run into this particular error before, so I would suggest posting your question on the GP Customer Forum to see if you can get some more people to chime in on this.
-Victoria
LikeLike
Thanks Victoria.. ^_^
LikeLike
Hi Victoria!
I would like to ask something about Sales Utilities.
We used the Remove Trx History from Sales Utilities to remove old transactions in order to decrease the size of our company’s database. After using the said utility, we found out that the database size was not decreased even though we removed 4 years of transactions. Does the utility just transfered the data from the original tables to another table? If so, could we reverse the removal of the said transactions?
Also, could you give us some advice on how to minimize the size of our database?
Thanks in advance!
LikeLike
Hi Chris,
The Remove Transaction History Utility does actually remove your data, it’s not moved elsewhere. There is no reversing this process, the only way to get it back is to restore from a backup.
For the SQL Server database size – you may need some SQL processes to shrink your database, this is a better question for a SQL dba. I would recommend posting on a SQL forum or the GP customer forum for some advice on this, as it’s not my area of expertise.
For the future, there is a product called Company Data Archive that can archive older GP data – this might be preferable to your method, depending on your specific needs and budget.
-Victoria
LikeLike
Hi Vitoria,
I found this website as I am struggling in finding the resolution for my problem. There is no recent changes in GP, suddently we cannot post any recurring batches. It says Recurring Batch error – Edit Required.
I check the SY00500 and found all the batches have MKDTOPST = 0 and BCHSTTUS = 0, there is no lock in SY00800 or SY00801 either.
Would you please instruct me what I need to do?
Thanks in advance.
LikeLike
Hi Phat,
When a batch has an error, GP typically puts it into a ‘suspended’ status in Batch Recovery (Tools > Routines > Batch Recovery). You can select it there and click continue to get it out of the ‘suspended’ status. Next, go to where the batch is (for example, if it’s a GL batch, it would be under Transactions > Financial > Batches) and print the Batch Edit List. This will tell you what the problem with the batch is so you can correct it.
-Victoria
LikeLike
Victoria,
Thank you.
The batches are clean now in the edit list. However, I don’t know if finance staff still have the same issue. If the issue persist, I will follow your advice.
LikeLike
Hi Victoria,
I would like to ask something about Financial Batches..
One of our users reported that one of their IVTFR batch returned a “Table Updates Interrupted” status. But the user also stated that the affected batch posted fine when she tried to check the transactions.
What caused this kind of behavior? Is it safe to delete the batch since it is posted already or are there some errors that we are unaware of that triggered the said status?
Thank you very much!
LikeLike
Chris,
This is not an official answer, as I do not have any kind of list that would tell me what specific conditions would return this error, but I have seen that message if there was a connectivity issue between the client and the server while posting or there was a problem detected in one of the tables that was being updated by the posting process. Without checking ALL the associated tables, it is really impossible to tell if there is still an issue at this time.
If this is an isolated problem and the inventory and GL modules look like they got updated correctly, I would recommend running check links and reconcile (on both financial and inventory series). If you have a batch of transactions that are both in posted and unposted status and you’ve verified that the posted transactions are fine, you should be able to delete the duplicate unposted ones.
If this is something that happens regularly, I would work with your GP partner and/or GP support to resolve it…otherwise you can be left with lingering data issues that may not be so obvious right away, but will cause problems later.
-Victoria
LikeLike
Hello victoria,
i am developing an application for Dynamics GP and i created some additional tables under one of the companies. The problem that i am having is that im able to perform a select to any of the Dynamics GP tables but when i try to perform a select to one of my tables i get ” INVALID OBJECT NAME”
i changed all the secutirty settings on my tables and its the same issue. im using the GPConnNET reference on connect to GP. i really hope that you can help me. thank you
LikeLike
Hector,
I am not the best resource for this, as I do not do this kind of coding myself. I would recommend posting your question on the GP Customer forum to see if some others more experienced with this can help you.
-Victoria
LikeLike
thank you for your response, just FYI i fixed the problem by changing the ODBC thank you again
LikeLike
Hi Victoria,
I know I can get company name from SY01500 table in the Dynamics DB….I want to link this to the bank account details for the company, sort Code, Acc number etc….which tables are these found in?
Many Thanks in advance
Nev Browitt
LikeLike
Hi Nev,
The bank details will be in the SY04100 table (Bank Maintenance) in the GP company databases. If you’re looking for checkbook information, that is the CM00100 table (Checkbook Maintenance).
-Victoria
LikeLike
Hi all
GP9
I need to create a user account report from GP referencing all areas related to basic user setup for audit purposes.
Where is the information shown under ‘Advanced SQL Server options’ stored?
I don’t see any references in SY01400 other than possibly SECACCS but that does not appear to change when marking/unmarking the check boxes.
Help always appreciated
Regards
Stu
LikeLike
found it:
sys.sql_logins in master
LikeLike
Stu,
Thanks for the update – glad you found it!
-Victoria
LikeLike
Hi Victoria,
We are seeing that when we make a change to the GL account assignments at the item class level, it gives us the option to push these changes to all items in the class. However, we are seeing that it is not pushing changes to the Unrealized Purchase Price Variance down to the items in that class.
Can you tell me what table I can query to view Inventory Item to GL Account assignments?
Thanks,
Jeff
LikeLike
Hi Victoria,
Sorry for the noise, I found the associations in IV00101.
Thanks,
Jeff
LikeLike
Jeff,
Great, thanks for the update.
-Victoria
LikeLike
Hello Victoria,
I am working on GP 7.0 I am trying to set new User Class for entry level only. Accordingly I have made changes thru Security Level but still its shows all pallets although i have keep unmarked. Please help me on this. Also please let me if there tables wherein we can make changes for User Class or access.
Thanks
Sanjay Parab.
LikeLike
Sanjay,
Are you using Advanced Security? If so, can you try rolling the user class settings down to the user(s) to see if that will help? If not, I am not sure how much I can help, as the ‘old’ security is something I have not used in many years. One thing you can try after making all the changes to the user class is to create a new user and put them in that class – does the new user have the appropriate security?
-Victoria
LikeLike
Can you provide definitions for the SY02000 fields – DICTID, RESTYPE, RESID, ALIAS, ALTDICID?
Example If you look at the SY00500 table (BCHSTTUS field – 0 is for Available, 8 is for Interrupted while Printing etc etc
Many thanks
LikeLike
Colin,
Maybe I have not had enough coffee yet, but I am not seeing the SY02000 table anywhere. Perhaps I do not have the module that installs that table on my test machine. However, by the name of the fields it sounds like you’re asking about a security table. If so, I am not aware of a listing available for the value of those fields, sorry.
-Victoria
LikeLike
This seems to be in older versions. We’re trying to figure them out too.
LikeLike
Hi Victoria,
Congratulations on your MVP status for another year.
This error that we get is driving us mad.
When we try to post a timesheet batch (timesheet batch entry window) – we get the following error “This Batch must be pre-processed first”
Have printed edit list – no errors
Batch is available.
Any thoughts would be appreciated.
Cheers
Paul
LikeLike
Hi Paul,
I have not come across this error before, nor can I find anything even remotely close to it in the GP KnowledgeBase. If you’re not able to get help on any of the GP newsgroups, I think you may need to talk to GP Support to resolve this.
-Victoria
LikeLike
Hi Victoria,
In Transaction Dimension Code, is there a way to modify the accounting class link without affecting the unposted transactions?
Thanks.
Ernesto
LikeLike
Ernesto,
Is this for Analytical Accounting? If so, I would recommend posting your question on one of the Dynamics GP newsgroups (links are on the right side of this page) with some more details of what you are trying to accomplish.
-Victoria
LikeLike
Dear Victoria,
I was upgrading my GP from SP1 to SP4 and I have a total of 7 companies.
After the upgrade.. 2 companies were successful and the others, when you login to GP utilities, have a small icon with a lock.
When you try to select one of them it gives you “The company is being updated by another client”
Is there a way to check the status of the companies from SQL because there seems that something went wrong during the upgrade and they became locked? or do you have any solution in mind ?
thank you in advance,
Barrak
LikeLike
I found the solution for the locked companies problem.
There is a table in DYNAMICS database with the name dulck and this has a list of locked dbs for update.
Thank you anyway Victoria,
Regards,
Barrak
LikeLike
Barrak,
Glad you were able to find a solution. Thanks for the update.
-Victoria
LikeLike
Hi all
Is there a best practice for recovery of Smartlist Favourites should a user delete one other than user recreation of said favourite?
If not, how would I best recover a deleted favourite if user recreation is not an option?
Thanks as always for any assistance.
Stu
LikeLike
Stu,
The only other option would be to restore from a backup. That might be a lot more work than having the user recreate their SmartLists. You could also restore the backup to somewhere else and see if you can find the needed data and copy it directly into your tables. Unfortunately, I do not have all the tables needed for this and you may interfere with new favorites created in the meantime, so you may need to do some additional research and/or talk to Dynamics GP support.
-Victoria
LikeLike
Dear Victoria,
I know this question might sound lame but I have a company for testing that is not showing in GP, although it has been in GP before now its not showing.
I checked SY01500 and its not listed.
My question is how can we put it back to show in GP and is it a manual procedure?
LikeLike
Barrak,
If the company was showing up before but is no longer there, especially if it is not in the DYNAMICS..SY01500 table someone would have had to remove/delete it. Without knowing the details of what exactly was done, the safest suggestion I can think to get your test company back is to create a new company (I would use a different database ID, but you can use the same company name if you want). Then once the new company is created, if you want to get the data in your old test company into the new one, just restore from the old to the new test database in SQL.
-Victoria
LikeLike
well thank you so much for the reply victoria.
I deleted the testdb from sql and then recreated it in GP and then restored it just like you said.
thank you again
LikeLike
Hi Victoria,
I am stuck with a stuck check batch in recovery , i checked the sy00500 table the batchstatus is set to 15, there is no entry in the dynamics..Sy00800 for that batch , we are using ver 6 ,
the checks printed successfully
vendor invoice shows open
vendor pmt shows work
gl did not update
checkbook did not update , however in checkbook maintenance it shows the correct next available check #
Any idea or input in this regard will be highly appreciated.
Thanks,
Vinay
LikeLike
Vinay,
Check posting problems are typically very difficult to pinpoint remotely like this, however, I would recommend changing the batch status to 0 (and also error state to 0 if it is not) in the SY00500 table, then trying to open the batch in GP to see if you can tell what the problem is.
-Victoria
LikeLike
Thank you victoria for your suggestion, I updated the batch status to 0 , the error state was already set to 0, had the user print the batch to check for any errors, there were no errors found, the user reprinted the checks with the same check numbers (over writing the ones that were used), and re processed it , the batch got processed fine without any issues.
Thanks again for you help
Vinay
LikeLike
Hi Victoria,
Could you please help me with this concern? I’d like to disable the pop-up for ‘credit limit override password’ on Sales Order document within SOP window. Do you know where I can go to disable it?
Many Thanks & Warm Regards,
Nikki
LikeLike
Nikki,
I do not know of a way to completely disable that pop-up without a customization. The only work around I can think of is setting the credit limit to unlimited for every customer – that would ensure that you are never prompted to override the credit limit.
-Victoria
LikeLike
I see, thanks so much for your reply. Once again, I really appreciate all your help!!
Best Regards,
Nikki
LikeLike
Hello Victoria,
In which table I would get Period Start Date and Period End Date?
LikeLike
Sanjay,
Period start and end dates are in the SY40100 table. You can filter by SERIES = 0 to get rid of all the extraneous data.
-Victoria
LikeLike
Thanks Victoria Its really helpful for my report.
Regards,
Sanjay.
LikeLike
Can you pls let me know what is Posting date, Posted date and Document date in Payable Module?
LikeLike
Sanjay,
This is an excellent and very popular question – please take a look at my latest blog post for the answer.
-Victoria
LikeLike
Victoria,
Sometime ago we lost our Fabrikam, Inc. and would like to reinstall it. Any idea as to how we would do this?
Thanks!
LikeLike
Bill,
It would really depend on what you mean by ‘lost’, as there might need to be some cleanup done, but I would start with going into GP Utilities and choosing the option to reinstall the sample company.
-Victoria
LikeLike
The Fabrikam company was completely removed through the transistion to a new server as they figured it was not needed. I ran GP Utilities and installed a sample company and it created a “TWO” company but I could not access it from GP.
LikeLike
Bill,
Are you able to log in as ‘sa’ and access Fabrikam? If so, you need to grant access to the other users by going to Tools > Setup > System > User Access.
-Victoria
LikeLike
It will not list the Fabrikam company in the company list.
LikeLike
Bill,
In that case, I would delete the TWO database in SQL and run the automated solution to delete references the TWO company out of all the tables. You can find that by going to this link for automated solutions. It is called Delete Company in Dynamics GP. Once this is done, re-create the sample company again.
-Victoria
LikeLike
I was able to delete the TWO company and re-created the sample company. I can now access the Fabrikam, Inc. company. Thank you for your help on all of this!
LikeLike
Victoria,
First of all thank you for putting all these useful info on here, It has helped in the past in number of ways in locating the data,
Now to the question, what would be the best way to remove a batch,
LikeLike
Vinay,
Can you please provide some more information? What exactly are you trying to remove and from where?
-Victoria
LikeLike
hai
Yudin
it is execellant composition of all tables at one place
i am searching from the last 4 months
Iam aFinance Controller of EPSILON at hyd,India,& we r implementing Nav 09
thank u for u guidance
get back to u if rerequired
LikeLike
Victoria,
We have a transaction that is stuck in the marked/print (5)status. Generally our stuck transactions have not yet been posted (mkdtopst 1 and bchsttus 0) and thus we are able to simply reset the transaction and the user can then repost. In this case the transaction shows both the sy00500 table and the dynamics sy00800 table, however the user is not currently logged into GP. I’m tempted to simply reset the transaction to batch status 0 and marked to post 0 but I’m concerned about the fact that it’s already posted. Any suggestions? Thanks!
LikeLike
Dave,
The batch being in SY00800 simply means that there is a stuck process, not that it’s already posted. First thing I would try is logging in as that user to see if GP will recognize there is a process running and finish it. If that does not work, then you would need to delete the process out of the SY00800 table and reset the status in SY00500. To verify that the batch is not already posted (while that is unusual, I have seen it happen), I would typically check the work and posted tables for your transaction type. Hope that helps.
-Victoria
LikeLike
Victoria,
Thanks so much for the response. Having the user log back into that company and then run batch recovery worked! Who knew it would’ve been so simple 🙂 Thanks again.
LikeLike
Dave,
Great, thanks for the follow up. This is one of the things that I think has gotten much better in the last few versions of GP.
-Victoria
LikeLike
Hi,Victoria,
Thanks for your prompt response. I try to add the INTERID = ‘INSG3’ before “Select” but system saying that “Incorrect syntax near “=”. Would you please take a look the following statement and give me some input?
Thanks a bunch!
SELECT GL00105.ACTNUMST, GL00100.ACTDESCR, GL20000.JRNENTRY, GL20000.TRXDATE, GL20000.DEBITAMT, GL20000.CRDTAMNT, GL20000.DSCRIPTN, GL20000.SOURCDOC, GL20000.ORMSTRNM, GL20000.REFRENCE, SY01500.CMPNYNAM
FROM INSG3.dbo.GL00100 GL00100, INSG3.dbo.GL00105 GL00105, INSG3.dbo.GL20000 GL20000, DYNAMICS.dbo.SY01500 SY01500
WHERE GL00105.ACTINDX = GL20000.ACTINDX AND GL00105.ACTINDX = GL00100.ACTINDX AND ((GL00100.ACTNUMBR_1 Like ‘1100%’) AND (GL20000.TRXDATE<{ts '2009-05-01 00:00:00'}))
LikeLike
Lily,
At the end of your query, add the following:
AND SY01500.INTERID = 'INSG3'-Victoria
LikeLike
Hi, Victoria,
I am trying to show “Company name” on my query with GL 00100, GL00105, GL 20000 and SY01500 selected. However, it does not show up correctly. What kind of relationship should I set up beteween SY01500 and other tables?
Thank you very much!
LikeLike
Lily,
Since the SY01500 table is in the DYNAMICS database and the other tables are in your company database(s) you would need to do a link on something like:
INTERID = 'COMP'replacing COMP with your company database ID.
-Victoria
LikeLike