?
Solved

Deleting history records from GP 10

Posted on 2013-06-04
11
Medium Priority
?
934 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

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 2000 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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part III
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

749 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