Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How do I add Analytica...
Microsoft Dynamics GP (Archived)

How do I add Analytical Accounting codes in the database rather than manually entering each one?

(0) ShareShare
ReportReport
Posted on by Randy King 45

I am in the process of setting up Analytical Accounting. I have set up my dimenstion and am ready to put in my codes. I have over 1000 codes so would prefer inserting the records in the databse over entering each one individually through the form.

From what I have gathered so far, I will need to update tables AAG00401, AAG02000, and AAG00102. It appears that AAG00401v is updated when I insert the records into AAG00401. Below I provide an example of system users, AA Dimensions, Codes, and Descriptions. Below that I have provided the SQL I believe I need to run in order to update the database to add the codes and then modify the AAG00102 table in the DYNAMICS Database so that future records will be added with the correct consecutive ID. 

Will you please review and let me know if this covers everything or if there are other tables which would need to be updated in order to have AA function properly?

One thing I have not been able to figure out is how the NOTEINDX column should be updated and what other tables it may be related to.

Users - All users will have Distibute and Adjust Access for all codes.

 

sa

DYNSA

rr000

rr001

 

AA Dimensions

Codes

                                Description

Job Codes

                RW1-001

                                Job 1

                RW1-002

                                Job 2

                RW1-003

                                Job 3

Cost Codes

                1001

                                Cost 1

                1002

                                Cost 2

                1003

                                Cost 3

 

Insert Into AAG00401 Values (1,1,’RW1-001’,’Job 1’,’’,101.00000,0)

Insert Into AAG02000 Values(‘sa’,1,1,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,1,1,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,1,1,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,1,1,0,0,1,1,0,0)

 

Insert Into AAG00401 Values (1,2,’RW1-002’,’Job 2’,’’,102.00000,0)

Insert Into AAG02000 Values(‘sa’,1,2,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,1,2,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,1,2,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,1,2,0,0,1,1,0,0)

 

Insert Into AAG00401 Values (1,3,’RW1-003’,’Job 3’,’’,103.00000,0)

Insert Into AAG02000 Values(‘sa’,1,3,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,1,3,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,1,3,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,1,3,0,0,1,1,0,0)

 

Insert Into AAG00401 Values (2,4,’1001’,’Cost 1’,’’,201.00000,0)

Insert Into AAG02000 Values(‘sa’,2,4,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,2,4,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,2,4,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,2,4,0,0,1,1,0,0)

 

Insert Into AAG00401 Values (2,5,’1002’,’Cost 2’,’’,202.00000,0)

Insert Into AAG02000 Values(‘sa’,2,5,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,2,5,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,2,5,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,2,5,0,0,1,1,0,0)

 

Insert Into AAG00401 Values (2,6,’1003’,’Cost 3’,’’,203.00000,0)

Insert Into AAG02000 Values(‘sa’,2,6,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘DYNSA’,2,6,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr000’,2,6,0,0,1,1,0,0)

Insert Into AAG02000 Values(‘rr001’,2,6,0,0,1,1,0,0)

 

UPDATE DYNAMICS.dbo.AAG00102 SET aaRowID=6 Where aaTableID=401

 

*This post is locked for comments

  • Verified answer
    veeyeskay Profile Picture
    veeyeskay on at
    Re: How do I add Analytical Accounting codes in the database rather than manually entering each one?

    Hi Randy

    There are 4 thigns to keep in mind.

    1. You can import the codes into AAG00401

    2. You can use the article blogs.msdn.com/.../fixing-missing-note-index-values.aspx to update note index values for the imported dimension codes. This is a recommended practice to maintain data consistency.

    3. You can update the AAG00102 table for the index value using my article msdynamicstips.com/.../updated-sql-script-for-fixing-primary-key-errors-in-analytical-accounting to fix records in AAG00102

    4. Use the following scripts in my blog to grant access to the new dimension codes to all the users

    msdynamicstips.com/.../sql-script-to-provide-access-to-transaction-dimension-codes-to-users

    or

    msdynamicstips.com/.../macro-to-provide-access-to-transaction-dimension-codes-to-users

    One option is to use SQL script to grant access to all users and the second option is to build a macro to grant access to all users to all dimension codes via the application

    Once u perform these 4 steps, you should be good to go.

    I hope this helps.

  • Randy King Profile Picture
    Randy King 45 on at
    Re: How do I add Analytical Accounting codes in the database rather than manually entering each one?

    I think I figured out the NOTEINDX column in table AAG00401. Prior to setting up my insert statements I would need to find the value of the NOTEINDX column in table DYNAMICS.dbo.SY01500 for the company that I am adding AA codes for. The value for NOTEINDX in my first insert statement for AAG00401 would need to be set to the NOTEINDX value from table DYNAMICS.dbo.SY01500, so my first query above assumes a value of 101.00000 for NOTEINDX in table DYNAMICS.dbo.SY01500 for the company that I am adding AA codes for. Then the value for each following insert statement on table AAG00401 would need to be incremented by 1. After running all of the insert statements, I would need to set the value of NOTEINDX in table DYNAMICS.dbo.SY01500 to the next consecutive number. In my query examples above, I would need to change the insert statements for table AAG00401 for cost codes 1001, 1002 and 1003.  The NOTEINDX column values 201.00000, 202.00000, and 203.00000 would need to be 104.00000, 105.00000, and 106.00000. Then I would also need to add the following query after running all of the insert statements:

    UPDATE DYNAMICS.dbo.SY01500 SET NOTEINDX=107.00000 Where CMPANYID=1

    On a side note, considering that DYNAMICS.dbo.AAG00102 is for multiple companies and I am adding the codes for CMPANYID 1, I would change the update statement on that table to:

    UPDATE DYNAMICS.dbo.AAG00102 SET aaRowID=6 Where aaTableID=401 AND CMPANYID=1

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans