Solved

Deleting history records from GP 10

Posted on 2013-06-04
11
841 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now