GP 2010 - General Ledger accounts

Posted on 2013-10-05
Medium Priority
Last Modified: 2013-10-17
About 100 accounts were added in GP but they aren't needed.  Is there a way in SQL to delete these accounts quickly.  The are not in a range. Our GL string is 3-6-6-6  For example: 100-100066-668799-308907.  Are there any options?

thank you!
Question by:pstre
  • 2
LVL 18

Assisted Solution

by:Victoria Yudin
Victoria Yudin earned 2000 total points
ID: 39550294
You could delete them from the GL00100 table, but then you would need to run Check Links on the financial series to delete them from all the other related tables.

The problem with this approach is that there is no data validation done. So if in the Dynamics GP UI it would have given you an error because that account was used, you will not see that error in SQL. You could inadvertently delete an account that is actually used somewhere in GP and that could cause some serious problems in the future. If you're going to do this, I would at the very least make sure that the Account Index you are going to delete does not appear in the GL10001, GL20000 or GL30000 tables.

Author Comment

ID: 39550473
but how would you delete in SQL if the account numbers are all over the place. They are not in a range...

would it be best for the user to just delete from the front-end of GP?

LVL 18

Accepted Solution

Victoria Yudin earned 2000 total points
ID: 39550557
It certainly would be safer to delete them from the GP user interface. If it's really about 100 accounts, you're probably talking about a couple of hours work.

In SQL, you would need to either find some logic to find all the accounts or get a list of them. For example, if they were all created on the same date, you could search by created date in GL00100. Or if you already have them in Excel you could use that to get a list of the account indexes in SQL. If this is not something that you already have and you'd need to put it together anyway, it will probably not save you any time to do this in SQL. Especially considering the need to run check links afterwards and the danger of deleting an account with activity because there is no safeguard for this in SQL, as I mentioned before.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question