Yes, it is possible to change the Account Format for the Chart of Accounts in Microsoft Dynamics GP as long as:
1. The changes fall within the Account Framework for the company in question.
2. The format changes are made to all accounts.
The free Account Modifier PSTL Tool can be used to accomplish this. More information regarding PSTL can be found at the bottom of the blog.
The account format changes must fall within the following boundaries in the Account Format Setup Window (to Microsoft Dynamics GP >> Tools >> Setup >> Company >> Account Format):
1. The final Account Length cannot exceed the ‘Maximum Account Length’.
2. The final number of Segments cannot exceed the ‘Maximum Segments’.
3. The Maximum Length of each Segment cannot exceed the ‘Max. Length’ defined for each segment.
In the example above, it is possible to change the format of all accounts as long as the:
1. Account Length does not exceed 45.
2. The total Number of Segments does not exceed 5.
3. The total Length of each Segment does not exceed 9.
The steps needed to accomplish the desired changes vary depending upon what those changes are. Here are a couple of common scenarios.
Scenario #1 Removing a Segment or Reducing the Length of a Segment:
In the following example the user wants to completely remove the fourth segment and change some of the numbers associated with the account. The change may look something like this:
To accomplish this change utilizing Excel to import, take the following steps:
1. Open Excel, Click Format, Click Cells, open the Format Cells window.
2. Under the Number Tab, select Text. This option will show all information exactly as it is entered in the cell, including numbers with leading zeros.
3. Enter existing account numbers in the first column of the spreadsheet being sure to include dashes separating the segments within the account number.
4. Enter the new account numbers, including dashes, in the second column of the spreadsheet.
5. Save the spreadsheet as a text file. Here is an example:
6. In Dynamics, log in as ‘sa’.
7. Open Professional Services Tools Library (PSTL), click Navigation Pane, click PSTL shortcut.
8. Under Financial Tools, select Acct Modifier/Combiner.
9. Click Next.
10. In the Account Modifier/Combiner window, select Import in the Select By field.
11. Two new buttons are now available, Validate and Modify.
12. Click on Validate.
13. Select the spreadsheet previously saved as a text file.
14. Click Open. All current accounts from the first column of the spreadsheet will be validated in Dynamics to verify that they exist before converting them to the new account number.
15. Verify that the Validation report contains no errors. Any errors will need to be fixed prior to the conversion of accounts. Fix the errors on the spreadsheet and re-import until no error appear on the report.
16. Click on Modify. This process will take time to perform depending on the number of accounts that are to be modified. Approximately 30 seconds for each account.
17. Click ‘OK’ on the following message: The Account Modifier is complete on the Dynamics/3rd party tables.
18. Click ‘Yes’ when you receive the message: Continue? Are you going to combine any accounts?
19. Click ‘OK’ on the following message: The Modifier/Combiner Process is now Complete, Please Reconcile your Year(s).
20. Close the Account Modifier/Combiner window.
21. Choose a report destination for the TA Modified Accounts report. This report will list all the accounts that were modified.
22. Reconcile all historical and current years, starting with the oldest.
a. Click Tools, Point to Utilities, Point to Financial, Click Reconcile.
b. Under Reconcile, Select Year.
c. Select History option for Year.
d. Select Oldest Year from drop down.
e. Click Reconcile.
f. Repeat steps for all years.
Next, take the following steps to reconcile the Account Format Setup:
1. Click on Microsoft Dynamics GP >> Tools >> Utilities >> System >> Reconcile to open the Reconcile Window.
2. Under Tables, highlight ‘Account Format Setup’ and click the Insert button.
3. Click Reconcile.
4. Verify that the appropriate changes were made to the Account Format (Microsoft Dynamics GP >> Tools >> Setup >> Company >> Account Format).
Here is the Account Maintenance Window before any changes were made:
Here is the Account Maintenance Window after the account was modified and the Account Format Setup was reconciled:
Here is the Account Format Setup Window before the Account Format was reconciled:
Here is the Account Format Setup Window after the Account Format was reconciled:
*Note: The Account Length dropped to 8 and the Segments were reduced to 3.
Scenario #2 Adding to the length of a Current Segment and/or adding a segment:
In the following example the user wants to add a 4th segment and increase the length of the first segment. The change may look something like this:
Here is the Account Format Window before making any changes:
First, take the following steps to change the Account Format to make room for the new characters:
1.Click on Microsoft Dynamics GP >> Tools >> Setup >> Company >> Account Format to open the Account Format Setup Window.
2.Increase the ‘Segments’. In this case increase the Segments to ‘4’.
3.Increase the length of the first Segment. In this case increase it to ‘3’.
4.Increase the total ‘Account Length’. In this case increase it to ‘10’.
5.Enter the appropriate ‘Length’ for the fourth segment. In this case enter ‘2’.
6.Save the changes.
*This will ensure the Account Format has enough room to accommodate the increase in size.
Notice that the Account Number now displays with blank spaces in GP:
*All originating accounts must be represented exactly as they are displayed in GP – including blanks in the Import file.
To accomplish this: use the steps 1-22 above (utilizing Excel to import), keeping the following in mind:
Step #3: Enter existing account numbers in the first column of the spreadsheet being sure to include dashes separating the segments within the account number. **In this case be sure to include all spaces/blanks appropriately.
Step #5: Save the spreadsheet as a text file. Here is an example:
Here is what the account looks like after the modification:
More information on PSTL:
If PSTL is not currently installed, Partners can download the appropriate PSTL version by clicking on the link below to open the Professional Services Tools Library (PSTL) for Microsoft Dynamics (North America Only) page:
*Make sure to provide your client with the appropriate version of PSTL (according to their current version of GP):
PSTL for Microsoft Dynamics GP no longer requires a Registration Key as it is now included upon installation. In order to utilize PSTL for Microsoft Dynamics GP at no additional cost you will need to download the most current version and install. Open the Professional Services Tools Library window from your shortcut and click Register. The correct key will default in, and then click OK.
I hope this information is helpful! Good luck!
How does this affect transactions already posted?
Extremely helpful! Worked like a charm. My problem now is Management Reporter and how to update my reports in an automated manner without going into each report.
Hi, I plan on following scenario #2 for my Account Changer situation. My COA is in a 4-4-4-4-4 format, and will be changing to a 5-5-1-3-4. I will do the account format setup change, then do account changer (then reconciles). That's no problem. The issue happens where they have a bunch of a accounts they want to combine during the process. My thought process was I would combine the accounts, change the account format, run account changer and do all the reconciles. Does that make sense? Or should I be running some form of a reconcile between the combine and the account format change?
Should they have everything posted before I begin?
As far as Management Reporter goes. . . . I assume this will be a manual process (?). I did a quick look online and didn't see anything.
Do all users need to stay out of this company in GP where account segment length is being modified?