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;
SELECT ['GL Account Master'].ACTINDX AS [GL Account Master] ,['GL Account Index Master'].ACTINDX AS [GL Account Index Master] FROM GL00105 AS ['GL Account Index Master'] (NOLOCK) LEFT JOIN GL00100 AS ['GL Account Master'] (NOLOCK) ON ['GL Account Master'].ACTINDX = ['GL Account Index Master'].ACTINDX WHERE ['GL Account Master'].ACTINDX IS NULL UNION SELECT ['GL Account Master'].ACTINDX AS [GL Account Master] ,['GL Account Index Master'].ACTINDX AS [GL Account Index Master] FROM GL00100 AS ['GL Account Master'] (NOLOCK) LEFT JOIN GL00105 AS ['GL Account Index Master'] (NOLOCK) ON ['GL Account Index Master'].ACTINDX = ['GL Account Master'].ACTINDX WHERE ['GL Account Index Master'].ACTINDX IS NULL
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;
- Account Maintenance ( ) where you create each account individually
- 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 () 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);
INSERT INTO GL00105 (ACTINDX ,ACTNUMBR_1 ,ACTNUMBR_2 ,ACTNUMBR_3 ,ACTNUMBR_4 ,ACTNUMBR_5 ,ACTNUMBR_6 ,ACTNUMBR_7 ,ACTNUMBR_8 ,ACTNUMBR_9 ,ACTNUMBR_10 ,ACTNUMST)
(SELECT ACTINDX ,ACTNUMBR_1 ,ACTNUMBR_2 ,ACTNUMBR_3 ,ACTNUMBR_4 ,ACTNUMBR_5 ,ACTNUMBR_6 ,ACTNUMBR_7 ,ACTNUMBR_8 ,ACTNUMBR_9 ,ACTNUMBR_10 ,RTRIM(ACTNUMBR_1) + '-' + RTRIM(ACTNUMBR_2) + '-' + RTRIM(ACTNUMBR_3) FROM GL00100 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105))
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.