Open Forum

Expand all | Collapse all

Purge GP History files

  • 1.  Purge GP History files

    Posted Nov 14, 2017 03:20 PM
    ​Have a client on GP version 10 Standard who has history files (Financial, Sales & Purchasing series) dating back to 2002 and wants to delete history in each series for 2002 - 2006.

    What is the easiest and safest way to do this?

    Thank you,

    Dan Mazzagetti

    Dan Mazzagetti
    GP Consultant
    Middletown NJ
    Academy - Online Interactive Learning from Experts

  • 2.  RE: Purge GP History files

    Posted Nov 14, 2017 04:44 PM

    GP has built in functionality to help you complete these tasks.
    During Summit I have done a session on this. GP When It's Time to Purge Data. If you or your client were at Summit the slide deck has been posted.
    There are a few areas to look at and it would be better to try it out on a test system first so you can see what it does. Depending on the amount of data that you have will also set the limits of how much data you can purge at a time.

    The items are found under Utilities for each series.
    If you have further questions let me know.

    Bert Green
    GP Developer/GP Administrator
    Berger Transfer & Storage, Inc.
    Saint Paul MN
    GP2015/MR CU11
    GPUG All Star 2017

    Academy - Online Interactive Learning from Experts

  • 3.  RE: Purge GP History files

    Posted Nov 14, 2017 04:58 PM
    Depending on how large your database is, this can take a lot of time. If you decide to go that route make sure you have a number of backups throughout the process and I would also try and add as much RAM to your SQL server as possible. I have tried doing this in the past and stopped doing it because it was taking too long. All users need to be out of the company while you are removing history.

    I created a request on Microsoft's website ( to try to improve this in future versions. As the product matures, many customers out there have a lot of historical data that could be purged.

    Professional Advantage also sells a company archive tool that would be worth looking at.

    Christine Boisvert
    IT Manager
    Floform Countertops
    Winnipeg MB

    Academy - Online Interactive Learning from Experts

  • 4.  RE: Purge GP History files

    Posted Nov 14, 2017 05:39 PM
    We typically create a new GP company and call it Archive and restore the production company to it.  From there, we use the GP routines/utilities to purge out the data that we want gone.  With this approach, the old data is always accessible.

    Ryan McBee | iSolutions
    Managing Director

    Academy - Online Interactive Learning from Experts

  • 5.  RE: Purge GP History files

    Posted Nov 15, 2017 10:13 AM
    It has a lot to do with the database size. Last year we completed an update from GP9.0 to GP2016R2 for a database over 100gigs. We wanted a planned downtime for this update to be 24-36 hours no more for the GP users.

    Since the objective was to only have transactions in GP2016 for 01-01-2015 and forward and to keep the V9.0 instance available for inquiry, the update was done in SQL not using any GP utilities or update msi programs.  Updating from V9 to V2016 involves significant OS changes, SQL changes and GP table and client changes and a new SQL server.

    The general approach was as follows:

    1. Install GP2016 on the new SQL Server with no data.
    1.5. Manually Add new users and security access reflecting GP2016.
    2. Manually set up each module in GP2016 including any new setup features.
    3. Import the GL Chart of Accounts to GP2016 making sure the ACTINDX is exactly the same as the GL Accounts exported from GP9.0 (23,000 accounts!)
    4 Write a separate SQL SSIS package for each module ( FIN, RM, PM, SOP, POP, CA and so on) to directly integrate the cards and transaction tables from V9.0 to V2016 across SQL servers with "on the fly" SQL updates for new data columns such as WORKFLOW STATUS in GP2016 tables updated since V9.  Restrict on doc date, gl posting date table by table as needed. Some table joins are required in the script such as for lines and headers where the date is in the header but not the lines.
    5. The SSIS packages for all the GP modules covered about 300 tables to integrate.
    6. Run the SSIS packages and then run various GP Checklinks and Reconciles in GP2016. Do a couple times for test and have your users learn GP2016 and validate the modules.
    7. We did not bring over Report Options or Smartlist Favorites. Users can redo those.
    8. Done. The database update to GP2016 took about 1 hour and the checklinks and reconciles took much of a day.
    9 GP9 security updated to View-Inquiry only for users.
    10. Deploy via Citrix XenApp both V9.0 to some users and V2016 to all users.
    11. Note: we were able to export dictionary customizations for Reports and Forms  from V9.0 and import to V2016 with only a small amount of cleanup. in Report Writer and Modifier

    This procedure turned what would have been a challenging if not impossible update if done conventionally from V9 to V2016 into a 24hr exercise with limited downtime completed on a short weekend.

    Thaddeus Suter
    Retus, Inc

    Academy - Online Interactive Learning from Experts

  • 6.  RE: Purge GP History files

    Posted Nov 15, 2017 01:27 PM
    Tough I love Thaddeus's approach for the technical challenge, it's probably not something you want to tackle alone, if you don't have a deep knowlege of the tables structure..
    We're using the same approach suggested by Christine and Ryan, as creating "_ARC" Databases for your production entities, is probably the easiest to do and then purge the data out of you PROD DB's.. this is the longest mudane task, but you could somehow automate the process with Macros and run it overnight when no one is in the system.
    Be aware that the archive data will no longer show up in your historical reports, unless you write your own join scripts..
    CDA from Professional Advantage offers a way to seemlessly "view" the archived data from within the production company, but there is a cost attached with the tool.. and it doesn't support all the modules apparently from what I heard (inventory for example).

    Beat Bucher
    Business Analyst, Dynamics GP MVP
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14

    Academy - Online Interactive Learning from Experts

  • 7.  RE: Purge GP History files

    Posted Nov 16, 2017 02:50 AM
    I'm about to embark on a similar exercise now during Christmas shut down. Besides the time issue, do the various purges 'intelligently recalculate'?
    What I mean is, if I clear inventory to a date, and then reconcile inventory, will it tell me my inventory balance is incorrect (due to purged transactions)? Or does it create a line for the balance as at that date? Similar for purchasing and sales?

    Gavin Hundermark
    Technical Support Specialist
    African Global Operations (Pty) Ltd
    Krugersdorp, RSA

    Academy - Online Interactive Learning from Experts

  • 8.  RE: Purge GP History files

    Posted Nov 16, 2017 08:30 AM
    If you want to KEEP the data, then you should look at CDA Archive.  This product is from Professional Advantage.

    I have found that some customers have lots of data the they want to keep but do not want it in the Production database.
    We move data that is 5 years old or older to this archive.  Then if we need to look at the old data for tax purposes we have it but it is not cluttering up the Production database.

    They do a great job for all the standard GL applications.  They have some facilities for third party also.
    This process will REMOVE data into the other company database.
    You can use it year after year to move the data.

    Otherwise, I go through the GP Utilities to purge.
    I start off with the subledgers then go to the GL and Bank.  Bank is the most time consuming.

    Janeece Moreland
    Managing Director
    Conexus SG
    Dallas TX

    Academy - Online Interactive Learning from Experts

  • 9.  RE: Purge GP History files

    Posted Nov 16, 2017 10:18 AM
    Allow me to chime in on a few things.

    When you archive or purge data you will want to make sure there is no need to keep the summary balances in the GL for those historical years.   Once you purge the data from the GL the next time you run a reconcile on your historical years in the GL it will REMOVE the balances from the summary table so you will no longer see a balance for those years.

    On the other hand if that is your intention then don't forget to run reconcile after purging the GL data.

    CDA is a great tool but if you have Mekorma you have to purchase CDA plus and someone has to write a stored procedure.  :) The same goes with any other 3rd party products and CDA.

    Rob Klaproth
    Dynamics Certified Professional
    (GP Install & Configure)
    Sr. GP Consultant
    Armanino, LLP
    San Diego, CA

    Academy - Online Interactive Learning from Experts

If you've found this thread useful, dive deeper into User Group community content by role