By using this website, you agree to our Terms of Use (click here)
Does anyone know if it's possible to change the value in the Type column on the Chart Of Accounts screen after you have already created the GL Account?
Since different Account Types (Asset, Liability, Income, Expense) could be storing their values differently behind the scenes, I could see how it would be dangerous to change it for an existing account.
But I have one that I would like to change. I'm just a little nervous to do it.
This is the field that I want to change:
Hey Kermit,
You can change the Type on a GL Account, but only if there aren't any existing posted transactions for it. There might be some unposted journal entries which is fine, but any posted journal entries are a problem.
If you try to change the Type for a GL Account that has posted activity, you will get the following error message:
You cannot change the selected GL Account type because transactions for this GL Account already exist.
If you need to change the Type for a GL Account that has activity, I think the best way is to uncheck the Active box for the GL Account to deactivate it. Then create a new GL Account with the correct Type. Also, you can rename the number in the Account column so you might renumber the old (now inactive) GL Account to a special area of the Chart of Accounts and then use the old GL Account number on the new GL Account.
Thanks, Tim, for adding the suggestion on changing an account with existing activity. I ran into this scenario yesterday for an account that had 117 posted transactions. The account had been set to Type: Expense instead of Type: Liability, so in addition, the Net Income account balance was not as the accountants had expected. While pondering how I was going to hit "undo" safely, I came across your post.
I followed your advice and:
1. Inactivated and recoded the existing account (I also removed the class)
2. Recreated the account with the correct type
3. Updated the accountid's in GLTran
4. Ran the Validate Account History process to allow the Acumatica functionality to repoint the transactions and recalculate Net Income
5. Made a minor adjustment in one of the ARM Row Sets to avoid the old, recoded accounts from appearing with $0 balances
6. Updated from the old account code to the new account code where the account had been defaulted in the system
All in all - relatively painless from where I thought I would have to go, and we are back in business! Thanks, again!
Hi Megan,
Good day to you. Thanks for your update on this issue. Am facing similar issue, may i know how do you update the accountID in GLTran? I wasn't able to find anywhere where i could update the accountID. I guess the program is still checking on the AccountID , as after i validate all the historical balances, the balances is still stuck to the old accountID. Many thanks.
Thanks for giving me the guts to try it. Whew, looks like I don't have any posted activity so I was able to change the Type without any problem.
Wow, you were brave to do Step #3, but it sounds like the Validate Account History (GL509900) screen cleaned things up for you which is AWESOME!
Now that you repointed all of the history, is there any reason why you can't just delete that only GL Account? You should be able to delete a GL Account with no history.
I definitely have a whole new respect for, and understanding of, the Validate Account History functionality!
I try to avoid making direct database changes, but it was the only way I could think of to resolve the Net Income variance, as the balance is entirely system calculated and will not accept journal entry manipulation. This came to me on a Saturday, with the accounting close deadline coming at Monday mid-day, so my resources were limited. There may be a better way. I did briefly think about trying to impact Net Income directly, but now that I think about it, VAH would have likely undone it when next run if the transactions were still pointed to the old account. VAH will not be lied to!! lol
Seriously, though, before executing I did steps 1-4 in a completely separate test environment and backed up the production DB. If anyone is contemplating this and is not set up to do ALL of that - phone a friend. No matter how good your disaster recovery is, you probably do not want to be the person that "broke" the GL.
I think you are right about being able to delete the account, but I left out part of the story. That account was also created as a Cash Account, so I removed the associated payment method from the existing Cash Account and created a new Cash Account for the new GL Account.
I wanted to quit while I was ahead, so I left it there for now. We are in the middle of a pretty steep upgrade (4.2 to 2017R2). After that hurdle is crossed, I will revisit and let you know how it turns out. I know you can now make Cash Accounts inactive, but haven't looked at what that actually does. Maybe it will come into play.
Thank you so much for sharing your findings here. Very much appreciated.
Hi Pamela,
I updated the ID directly in the database in SQL. My installation is on premise - if you are hosted I am not sure what your access would be to do that, and if you are not experienced doing it you should ask for help. This might be something Acumatica or your Acumatica Partner can assist you with. I would start there.
Good luck! It is solvable, but just a little challenging. There is a reason the Acumatica functionality hard codes the account types, and it is a good one, but it is not easy to "cheat".
Thanks, Megan for your speedy reply. Ours is hosted, thus we are not able to update the tables. Seems like we got to do the "transparent" way - reclassify all the transactions from the wrong account type to the new one with correct account type, then inactivate the old account.
Pamela, that is a totally legitimate solution, and maybe what I would have done if I had an Accountant on hand to consult.
I also don't yet have the Reclassify Transactions (GL506000) screen, which is another super handy Acumatica functionality, where you can reclass ALL GL transactions that meet a defined criteria in one shot. I am looking forward to using that after upgrade.
I would like to say that the reclassify transactions feature is useful when you don't have too many transactions to reclassify. If there are pages of transactions, the reclassify feature would be a pain. It would probably be better off to do a reversal entry.
Also, just to add here for anyone who is on-prem, Megan's method personally makes sense to me. However, in general, it's definitely not recommended to modify the database directly. If you are going to do so, make sure that you are very comfortable with SQL and that it passes your own personal "smell test". If you do run a SQL statement against the Acumatica database and you break something, the liability is entirely yours.
That being said, I really like Megan's idea and I personally would use it on my Acumatica database. I just want to put a stern warning in here for anyone reading this in the future. If something breaks, you own it, not Megan or me. But, if something does break, please report it back here so we can all benefit from the lesson learned.