Depending on the cause for this error message, please refer to the appropriate resolution as follows:
Resolution 1
Verify that the account is marked as being linked to an Accounting Class. To do this, click on
Cards, point to
Financial, point to
Analytical Accounting and click on
Account. Enter the account number. Verify that
Class ID field is populated.
Resolution 2
In SQL Server Management Studio, review the AAG20001 (aaSubledgerDist) table that stores all the distribution records associated with a particular subledger transaction. Each transaction should have at least two or more distributions (debit and credit) associated with it. You can cross-reference the Account Index to the GL00100 Account Master table in GL to be sure you are viewing the correct distribution line. Review the value listed in the
aaBrowseType column. To be able to add AA data to this distribution line, you would want this field to show a '
1' if the account is linked to AA.
The values in the aaBrowseType column mean:
0 - Not linked
1 - Required or Optional codes allowed
2 - AA Code is set to Required, but not filled in
A good starting point is to look for missing required codes (value of '2') and update those to a '1' to see if that resolves it.
select * from AAG20001 where aaBrowsetype = 2
Note: To obtain a script (AA_Update_aaBrowsetype.sql) that will update the aaBrowseType column for all linked accounts to match the current setup for that account/class, you would have to open a support case for further assistance. To open a chargeable support case, you may call Microsoft Technical Support at 1-888-477-7877.
Resolution 3
In SQL Server Management Studio, make sure the Account Master table (AAG00200) in Analytical Accounting has been populated. This table should have the same number of records as the Account Master (GL00100) table and the Account Index Master (GL00500) in GL.
To repopulate the AAG00200 table, you can run the script below. This script will insert any missing accounts into the AAG00200 Account Master table that exist in the GL00100 Account Master table.
Insert into AAG00200 (ACTINDX,aaAcctClassID,aaChangeDate,aaChangeTime)
select ACTINDX,0,convert(char(10),getdate(),111),convert(Char(12),
getdate(),114) from GL00100 where ACTINDX not in (select ACTINDX from AAG00200)
Note: If you delete the AAG00200 table and then run the script to repopulate it entirely, you will lose the link to the Accounting Class. In that case, all the accounts must be linked to the Accounting Class again.
Resolution 4
If there are records in the AAG20000 subledger tables in AA with an invalid header ID of '0', this could cause issues. If there are invalid records in the AAG20000 series tables, the validation process checks to see if the distributions are linked to an Accounting Class, and may see these records as not linked and produce the error message above.
You can correct the problem by clearing out the records with an invalid header ID from the AAG20000 tables. Execute the scripts below in a query window in SQL Server Management Studio against the company database to see if there are any invalid records and if so, you may remove them:
Select * from AAG20000 where aaSubLedgerHdrID = 0
Select * from AAG20001 where aaSubLedgerHdrID = 0
Select * from AAG20002 where aaSubLedgerHdrID = 0
Select * from AAG20003 where aaSubLedgerHdrID = 0
Note: Make sure to have a current backup that you can restore to before running any delete statements, in case you would need to restore for any reason.
Resolution 5
For more information or steps on how to update the next available number in the AAG00102 table, click the following article number to view the article in the Microsoft Knowledge Base:
897280 Error message when you try to post Analytical Accounting transactions or save a Master record in Microsoft Dynamics GP: "Cannot insert duplicate key in object 'AAGXXXXX'"
Resolution 6
Obtain the results of the GL10000, GL00001, AAG10000, AAG10001, AAG10002 and AAG10003 tables. Review data and make sure the records are all there in the AA tables. May need to use insert script or run Distcorrect.sql to fix.