Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

Posted on by 115

I'm using GP 2010 in a classroom and need to clean old transactions out of the DB - any easy way to do this?

*This post is locked for comments

  • Clamdipkid Profile Picture
    Clamdipkid 115 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Wanted to thank everyone - good ideas.  Just so everyone knows we did try doing a backup after setting up everything they way we wanted it prior to the semester starting with the idea we would then restore at the end of the semester.  Even though we tested the process prior to doing it, the restore failed and that is why we're looking at getting a clean sandbox.

  • Mike2011 Profile Picture
    Mike2011 1,255 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Hi Leslie,

    Do you have the download link for the Note Fix utility. Is it compatible for GP 2010?

    Thanks,

    Mike

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Hi all,

    The next note number for Record notes (not window notes) is stored in the SY01500 table of the DYNAMICS database in the NOTEINDX column. Yep, the system dtabase, not the company database. As noted earlier, if you back up and restore one company over the other, the SY01500 table does not change so the note number is not advanced. Note indexes get very cross-linked when that kind of thing happens.

    Instead of the NoteFix.sql script, I would advise getting a copy of the free tool from Microsoft which is the Note Fix utility. It's a dexterity tool with a complete user interface. It's very clever too. The tool allows several options for setting, filling in, and finding cross-linked notes. You can even select which record should keep a note when it seeming belongs to two different records. Be very sure to get the correct version of the utility. There is a separate copy of the tool for each version.

    To my knowledge the INTERID column will not mess up the note indexes, but it will cause other problems. The system thinks you're trying to post intercomany transactions:)

    I hope this helps.

    Leslie

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Hi all,

    I am probably thinking too simplistically here (or completely missing the point), but let me give it a shot. This sounds like the question many people have about ‘starting over’ or copying all of the settings and master files from an existing or test company to a new company.

    A.)   If you use the PSTL tool (GP2010) to copy a company, you can select what you want to copy. The choices are:

    Overall options:

    ü  Data

    ü  Report Options

    Specific options:

    ü  All modules

    ü  Company

    ü  Financial

    ü  Inventory

    ü  Fixed Assets

    ü  Multicurrency

    ü  Payroll

    ü  Payables

    ü  Receivables

    ü  Sales Order Processing

    ü  Purchase Order Processing

    ü  Advanced Financial Analysis

    ü  Extended Pricing

    ü  Invoicing

    ü  Human Resources

    ü  Manufacturing

    ü  Bill of Materials

    ü  Project Accounting

    B.)    Prior to GP 2010, you have to download the Copy Company Automated Solution.

    C.)    You could use the Rapid tools (32 bit only) to export the configuration and master files from the old company to Excel templates that you can then use to import into the fresh company.

    D.)   To avoid the user/company SQL security problem, deny access to the database you are working with before you back up the company. When you do a forced restore of the company, the users will not come over. You can hook them back up later. Be sure to export your security files so that you can import them. Use the Support Debugging tool to make the security import/export seamless. Use the macro language and mail merge if you have a quantity of users. I also use mail merge to create giant SQL statements.

    E.)    If you are using Fabrikam, follow my colleague’s recommendation of reloading the company data.

    F.)    Above all, when you have created the clean ‘start over’ company, make a complete backup of your environment so that you can reuse it next year :}

     

    Does any of this resemble what you are trying to accomplish?

     

    Kind regards,

     

    Leslie

     

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    You are going to have problems with user ID's because if you restore a database the users associated with that database will be the ones to come back after the restore. So now you will have SQL users who are no longer associated with the company database. When  you say transactions are we talking AR, SOP, AP or POP. I have never done this but I suppose you could use the capture logins script, restore the database and then your users will come back to the database. At this point in time I think it is best to do a fresh install of GP. Drop the DYNAMICS and company databases, go into SQL and drop all the users and start over. In the future I would get all your users defined and then  do a backup before you start entering any transactions. This way when I new semester starts you simply roll in the backup. Now what you could also do is use generic user ID's like LESSON USER1 and LESSON USER2. This way the restore will be all you need to do. Otherwise, you will always need to clean up the user list per database.

  • Suggested answer
    Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    I'd use Rubal's method.  You'll have a clean database with no historical transactions mucking things up.  I've done it many times for clients who want a clean sandbox to play in.

  • Suggested answer
    sueconrod Profile Picture
    sueconrod 335 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    I'm assuming that you are using the Fabricam Company.  If that is the case you can just reload the company by launching Great Plain Utilities and then using the drop down to reload the test company.  You would have to be logged in as sa.  Or if this is a company that you keep going in using for training, if you use the clear data but loose more then you expected, Load what you needed to have in there, then do a backup prior to teaching, then you can always restore this data to the company  with this backup.    (Go to  Microsoft Dynamics GP>>Maintenance>>Backup or Restore and use help for more information).  You would need to be sa again to use this feature.

  • Clamdipkid Profile Picture
    Clamdipkid 115 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    We've had issues loading or at least copying the lesson company.  The last take was we created a back up and thought we could restore it with IDs and no student transactions but the restore didn't work.  So now I'm looking at cleaning out last semester's transactions

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Do you really want to clean out transactions or simply reload the lesson company? You can reload using GP Utilities.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Using GP 2010 in classroom and need to clean old transactions out of the DB - any easy way to do this?

    Hi,

    You can use Clear Data window (Microsoft Dynamics GP menu >> Maintenance >> Clear Data) to clear the data. For which module you want to clear the data? Clear data does not ensure integrity of transaction. Therefore, you will need to make sure that you clear data from all relevant tables. You may need to run the check links and reconcile for updating the tables depended on these tables. Make sure you have good backup before clearing the data.

    Rubal,

    http://dynamicsgphelp.com/

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans