Solved

Deleting history records from GP 10

Posted on 2013-06-04
11
831 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS Project Critical Path 4 72
SCCM 2012 Report 8 105
Excel Automation of Autosum 23 86
Is AWS.RDS considered a part of "Big Data"? 4 69
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the different options available in the Backstage view in Excel 2013.

747 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

12 Experts available now in Live!

Get 1:1 Help Now