Fixing A Corrupt Account Index Master

A while ago I investigated an issue where some reports were not working correctly on a client system; they’d been written with Crystal and were not returning transactions for newly created accounts.

As reports I write tend to do, these reports were joining to the General Ledger via the GL Account Index Master (GL00105) table and then joining to GL Account Master (GL00100) for the account descriptions. I wrote the following script to check the two tables against each other to see where the problem lay (my suspicions were on GL00105 being corrupt as the accounts were being used within Microsoft Dynamics GP;

The script returned about 300 accounts with entries in the GL Account Master but not in the GL Account Index Master.

It turned out that the client recently wanted to update their chart of accounts by adding in some new posting accounts. There are three ways that this should be done with two of them within Microsoft Dynamics GP;

1. Account Maintenance (Cards » Financial » Account) where you create each account individually
2. Mass Modify (Cards » Financial » Mass Modify) which allows you to create new accounts in bulk

The third alternative is Integration Manager which is available during implementation for 240 days or, long term, as part of the Customisation Suite which is available from Microsoft on an à la carte basis.

However, without Integration Manager, the client wanted a way to update the system from a spreadsheet (or CSV) and had found Table Import (Microsoft Dynamics GP menu » Maintenance » Table Import) and decided to use it without discussing it with us.

The problem is that Table Import only updates single tables at a time and, as with most SQL based applications, data in GP is usually stored in multiple related tables, which means that Table Import is singularly dangerous to use.

In this case they had updated their new accounts into GL00100 but not GL00105. We’ve recommended they purchase Integration Manager (they’d need to buy the Customisation Suite which gets them IM, Web Services, eConnect, Dexterity and Visual Studio Tools) for future updates of the system. In the meantime though, they have corrupt data which needs to be fixed.

The following SQL script updates GL00105 from GL00100 where there is not already an entry (the client in question has three segments; if this script is used for companies with a different number of segments the script will need to be amended);

If you do run this script, then please ensure you make a backup before running it and check the data afterwards for problems (as with all scripts I post here, it is provided “as is” with no warranty so please be careful).

Doing an update of the Account Index Master from the Account Master is quite simple; doing it the other way round is not possible as the information (such as Posting Type, Typical Balance, etc.) is not available on Account Index Master.