Solved

Deleting history records from GP 10

Posted on 2013-06-04
11
856 Views
Last Modified: 2013-06-13
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
Comment
Question by:pstre
  • 6
  • 5
11 Comments
 
LVL 18

Expert Comment

by:Steve Endow
ID: 39222864
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
 

Author Comment

by:pstre
ID: 39223437
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
 
LVL 18

Expert Comment

by:Steve Endow
ID: 39223467
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:pstre
ID: 39223576
Where trxdate is between 01012008 and 12312008
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 39223667
Which specific SmartList(s) are you running that are showing 2008 data?
0
 

Author Comment

by:pstre
ID: 39224270
Account Transactions under the Finance Module...
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 39224289
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
 

Author Comment

by:pstre
ID: 39225599
There is no data in either of the SQL queries. I used the exact statement that you used...
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 39225980
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
 

Author Comment

by:pstre
ID: 39245286
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
 

Author Closing Comment

by:pstre
ID: 39245289
Steve was excellent in documenting the solution!!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

777 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