I am trying to insert an invoice via eConnect and some custom code I wrote, but I keep getting the error "Violation of PRIMARY KEY constraint 'PKAAG20000'. Cannot insert duplicate key in object 'dbo.AAG20000'." After doing some research I came across kb 897280, "Error message when you try to post Analytical Accounting transactions in Microsoft Dynamics GP: "Cannot insert duplicate key in object".
select MAX (aaGLHdrID) from aag30000
Select the Company database in the list at the top of the window, and then press F5.
Are you sure that this company has been setup to use Analytical Accounting? How about select * from AAG30000. My knowledge of the AA tables is a little limited, but I always feel like the 10000 tables are work, unposted, 20000 tables are current year, or open, and the 30000 Tables are historical. What is the setup of this database? I hope this is a test system or at least a test company.
I'll try to remember what I did to configure a test company to use AA. I had to create a true company using GP and then do some of the basic setup. Then I had to configure it to use analytical accounting. Somewhere in DYNAMICS, it keeps the records of if AA is set up in a company. At this point, you can restore a backup of a live company. But if you don't do the AA configuration after creating the new test company, DYNAMICS does not know that it has Analytical accounting, but restored company has the AA tables.
That script from Microsoft (if it the one I am thinking of) basically goes in and updates the "Next" number, I believe in DYNAMICS. Say I have company A which is running Analytical accounting. Say the next transaction number is 5000. I then restore that company to test and then I only do 2 transactions, so that company's next transaction number is now 5002. In the meantime, I have done 1000 transactions in the live company. Then next time I restore that live company, DYNAMICS thinks the next number is 5002, so I try to do a transaction, it looks at DYNAMICS to get the next number, it comes back as 5002, then trys to insert that in the company database (which is over 6000 by now) and sees that there is already a transaction number 5002, then you get this error that you are getting, bucause I bet that this "Next Number" is a Primary key field.
So anyway, every time I restore the live company to the test company, I have to run that AA script that I think you are looking at, along with the other script they recommend running that updates references to the old company name to wht is stored in the Company master.
I hope this is somewhat helpful. I actually had to get a support case going with Microsoft about this, and this is why I know a little bit about this error.
Well this morning I noticed that the Analytical stuff was on the Admin page so I expanded the group and saw a setup link. So I ran through the setup and got an error on the PM tables, but the process finished. I then went back to try the same program where I was getting the above error and everything worked fine. I checked the AAG30000 table and found it to still be NULL. I am not sure what really changed, but we were able to continue.
I am also unsure what the Analytical Accounting is used for at this point (note: i am a dev not an accountant). Thanks for your reply K Day. I have printed what you wrote for my future review. Once we move on to the point of needing or using the AA stuff I will check back to what you wrote.