Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Delete All Transactions in Dynamics GP

1,015 views
Skip to first unread message

haniwt

unread,
Jan 31, 2010, 3:03:01 AM1/31/10
to
Hello all,

I am looking for a method that will help me delete all of the transactions
but keep all other data from a single company in Dynamics GP 10.0 SP4.

I want to do so because we were running a multi-company GP for final test
and we had to do a lot of modifications to the initial configurations. Now we
are stuck either with working with an old backup that has the old
configuration that need modification but with no transactions, or to delete
all of the transaction from the company.

I have done a small research on the web but i couldn't find anything like
this. Dynamics AX has a nice class called SysDatabaseTransDelete which i hope
that Dynamics GP has a similer one.

Thank you for all of your help.

HANI


Richard Whaley -- MVP 2006-2008

unread,
Jan 31, 2010, 4:21:02 PM1/31/10
to
In your testing, did you post any of these test transactions?

You have to purge records from selected tables to accomplish this. Trick is
knowing what tables to purge. For example, if you entered receivable
transactions, you will have RM10xxx tables to purge. If you posted those
transactions, then you need to purge RM20xxx tables. And, if you moved
transactions to history, the RM30xxx tables need to be checked.

Generally (and work with good backups!!!!!) if you purge the xx10xxx tables,
you delete transactions from work tables. If you purge the xx20xxx tables,
you delete posted but open (like unpaid payables) transactions. And purging
the xx30xxx tables will remove history.

Use the Resource Descriptions in the application to carefully check the
table names and, if you are not sure, look at the data in SQL to be sure.

Again, generally, xx00xxx tables are cards and should be kept and xx40xxx
are setup. After purging, run reconcile as some of the xx00xxx tables keep
summary data that needs to be reset to zero (not purged). For example, for
each customer there is a customer summary record that contains total purchase
amounts, etc. The record needs to be kept but the amount needs to be set to
0.

If I have not scared you yet, jump in. Otherwise, get some help from
someone that has done this before.

--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

Frank Hamelly, MCP-GP, MCT, MVP

unread,
Jan 31, 2010, 6:16:44 PM1/31/10
to
I've used Clear Data to clear all transactions from a database when I
want to copy all settings from an existing db into a new one and then
set the new db up for use as a different company.

Go File>Maintenance>Clear Data and clear the data from all the
transaction and history tables for each series. Then, run Reconcile
on each series to clear the summary info.

Frank Hamelly
MCP-GP, MCT, MVP
East Coast Dynamics
www.eastcoast-dynamics.com

get your GPtip42today at www.gp2themax.blogspot.com


haniwt

unread,
Feb 1, 2010, 3:48:01 AM2/1/10
to
Dear Sir,

Thank you very much for your help.

We would like to work on the solution that you have suggested but we would
like to ask you a few questions if that is possible:
1- Are there any obstacles or risks of following this procedure?
2- Are there any any possible problems that we need to be aware of?
3- What would happen to the End Year Closing for this company???
4- We have the manufacturing module installed, but it doesn't show its
tables in the Clear table window.

We really appreciate your help.

Best Regards

HANI

> .
>

Frank Hamelly, MCP-GP, MCT, MVP

unread,
Feb 1, 2010, 8:59:33 AM2/1/10
to
> > .- Hide quoted text -
>
> - Show quoted text -

1. I've never experienced any problems using this procedure
2. Just make sure you're clearing only transaction and history tables
- it would be easy to clear setup and master data if you're not
careful or don't have a good understanding of GP table structure
3. This has never been an issue - Year End closing works fine because
I've always created a new company db and then restored an existing db
into it, which is what I believe you are doing also, correct?
4. This creates a challenge. As you've noted, there is no 'Clear
Data' for Manufacturing. This means you'll have to identify all the
MFG transaction tables and clear them via SQL. Do you want to keep
all the Bill of Materials intact? If not, the BOM tables would have
to cleared also. Are you using Engineering Change Management? MRP?
CRP? You'll have to spend the time required to properly identify
which tables should be cleared.

Hope this helps,

haniwt

unread,
Feb 1, 2010, 10:30:02 AM2/1/10
to
Dear Frank,

I would like to thank you for your help.

Best Regards

HANI

> .
>

Frank Hamelly, MCP-GP, MCT, MVP

unread,
Feb 1, 2010, 11:45:17 AM2/1/10
to
You are most welcome HANI.
0 new messages