• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

Deleting history records from GP 10

We had several years of history copied into our GP 10 database.  More than 1 person was working on this and they copied in the same data so the year 2008 is duplicated.  We need to delete all 2008 records.  I have deleted from the GL20000, GL30000, GL10100, and GL10110 tables and there is still history data.  What other tables do I need to delete from?
0
pstre
Asked:
pstre
  • 6
  • 5
1 Solution
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Rather than delete records through SQL, have you tried the GP "Remove History" feature?

Tools -> Utilities -> Financial -> Remove History

You mentioned that you "copied" history into your GP 10 database.  I don't know what you mean by that, or what data you copied, so depending on how the data was brought into GP, that may affect whether the Financial Remove History works properly.

If you are looking to remove history for subledgers as well, a similar GP feature exists for sales, purchasing, etc.  But those operate differently and require a Range to be entered, such as document date range or PO Number range.

Tools -> Utilities -> Sales -> Remove Sales History  (Document Date range?)

Tools -> Utilities -> Inventory -> Remove Transaction History  (Document Date range?)


And I don't know what you mean by "there is still history data".  If you are looking at summary numbers in an inquiry window, it is likely that those were not updated after you deleted records in SQL.

I would recommend running the Financial -> Account Transaction History and Transaction Work Check Links, and then Financial Reconcile to ensure that your data is valid and that summary numbers are updated.

You should thoroughly test any of these operations on a test database first to confirm that they meet your requirements and do not remove data that you want retained.  And make sure to have a full backup of your Dynamics database and company databases before running any of these routines on a live DB.

Let me know if any of this helps.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
pstreAuthor Commented:
Tools/Utilities/Financial/Remove history doesn't work because we have never closed the years in GP so I cannot select a year using this utility.  The smartlist is still showing 2008 data and we need to remove that data because somehow this data was duplicated in import of data from a legacy system into GP. We need to remove the 2008 data and reimport.
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Which specific SmartList(s) are you running that are showing 2008 data?

And which search criteria are you using to select 2008 data?  Transaction Date?  GL Posting Date?  Open Year?

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
pstreAuthor Commented:
Where trxdate is between 01012008 and 12312008
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Which specific SmartList(s) are you running that are showing 2008 data?
0
 
pstreAuthor Commented:
Account Transactions under the Finance Module...
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Check in GL10000 and GL10001.

SELECT * FROM GL10000 WHERE TRXDATE BETWEEN '2008-01-01' AND '2008-12-31'

SELECT * FROM GL10001 WHERE JRNENTRY IN 
(SELECT JRNENTRY FROM GL10000 WHERE TRXDATE BETWEEN '2008-01-01' AND '2008-12-31')

Open in new window

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
pstreAuthor Commented:
There is no data in either of the SQL queries. I used the exact statement that you used...
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

GL transaction data is only stored in a few main tables.  GL10000 (work), GL20000 (open), and GL30000 (history).

I have verified in my test company that running the following deletions removes the GL data so that it no longer shows up in the GP SmartList.

SELECT * FROM GL10001 WHERE JRNENTRY IN 
(SELECT JRNENTRY FROM GL10000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12') --40

SELECT * FROM GL10000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12' --8

SELECT * FROM GL20000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12'  --212

SELECT * FROM GL30000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12'  --0


DELETE FROM TWO..GL10001 WHERE JRNENTRY IN 
(SELECT JRNENTRY FROM GL10000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12') --40

DELETE FROM TWO..GL10000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12' --8

DELETE FROM TWO..GL20000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12'  --212

DELETE FROM TWO..GL30000 WHERE TRXDATE BETWEEN '2017-04-12' AND '2017-04-12'  --0

Open in new window


If you are seeing data in GP, but are not seeing it in the above queries within SQL Server Management Studio, then my only remaining guess is that you are querying the wrong SQL Server instance or the wrong SQL database.

On the GP machine that is still showing 2008 data, open the GP ODBC DSN settings and verify the SQL Server instance name.  Then within GP, open Tools -> Setup -> Company -> Company and verify the Company ID, which is the physical database name.

Then verify that you are connecting to the proper SQL instance in SQL Management Studio and that you are querying the correct database.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
pstreAuthor Commented:
Hey Steve...I was using the correct database.  What I found using your queries was that transaction data from 2008-12-31 was in openyear = 2009'   For some reason the 2008 data was in openyear 2008 and 2009.  Once I deleted the trxdate between Jan and Dec of 2008 with openyear = '9'  I no longer found data in the smartlist .  Thanks a lot for your help!!
0
 
pstreAuthor Commented:
Steve was excellent in documenting the solution!!
0
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

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now