Open Forum

Expand all | Collapse all

Mass item removal

  • 1.  Mass item removal

    Posted 14 days ago
    ​Hello everyone,

    When we first brought our inventory into GP, our partner helped us by bulk dumping all of the information from our old system. We have found out that a lot of the inventory that carried over are things that we no longer purchase or sell and we are trying to remove unnecessary items. Most of the items with no history, BOM, routing, etc. have been removed already, is there a way to run all of the items in a list against GP and see what needs to be removed from them before we can delete them? We were thinking of a script we could run and it would return "Item #1 has PO history", "Item #2 has routing", etc.

    Any help would be appreciated!


    Richard
    ​​​​

    ------------------------------
    Richard Warden
    The Spiratex Company
    Romulus MI
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Mass item removal

    GPUG ALL STAR
    Posted 13 days ago
    Hi @Richard Warden

    First question, "WHEN" did the inventory items get brought in?

    Second, did you guys import in historical Purchase Orders and Receipts? If so, you may need to remove those to clear it up, and by remove I mean delete but you might have to do that in the GP Tables, especially if these are old PO's and not useful any longer.

    IF they were not old PO's then you actually might have used the items in which case you can inactivate them and then change the inventory lookups to exclude inactive items, but you won't be able to remove them in the GP Application.

    IF they are indeed older items and PO's that should never have been brought in then talk to your partner or a trusted consultant with data repair skills and have them remove mention of the items from every table they exist in. Most great data repair consultants have tools they use to 'find' the items in each and every table they reside in.

    This may be an involved and expensive exercise, but can be done.  The first solution, though, is to inactivate the items which you can do in several ways in bulk:

    1)​  Use Navigation Lists, pick each of the items by checking them off and then hit the 'inactivate' button on the top ribbon
    2) Use a SQL Script to mass update them in the IV00100
    3) Use a mail-merge macro to inactivate them in the GP Application
                     For info on mail merge macros Google "David Musgrave Mail Merge Macros"

    But, to shorten the answer it looks like from the error messages, GP is finding reference to these items in a number of tables, to delete them you need to remove all reference to them in all tables.  The first step, though is inactivating them.

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Mass item removal

    TOP CONTRIBUTOR
    Posted 13 days ago
    "is there a way to run all of the items in a list against GP and see what needs to be removed...?"

    Sure, but you're most likely going to need to contract with a knowledgeable partner to do this.  I'm with @Jo deRuiter here in that the first step is to inactivate the items in question to prevent any further data entry of them.  That can be done directly in the table if you want or even using Integration Manager.  If you aren't familiar with either of these, find a reputable partner who can help.  After you have inactivated all the items, then you can work on removing them one by one.

    Just a word of caution: I would strongly recommend doing this maintenance after hours and take a backup before doing so.  If you want to be extra careful, test everything on a copy of the information loaded on a test database first.  Run Checklinks afterward to see what Dynamics GP thinks you missed.  (If Checklinks recreates entries or even item information, it means the data purge was incomplete.)  Note that you'll likely have to do an inventory reconcile afterward just to clean up those entries.

    I applaud you for taking active steps to keep your data clean.  My question: what is the overarching need to remove these items (and all their history) from the database?  Why not consider archiving that information instead, as you may still want the sales history for forecasting, year-over-year comparisons, trend analysis, etc.​​

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise Idaho GPUG Chapter Leader
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Mass item removal

    Posted 13 days ago
    ​Thanks for the advice guys!

    Blair - For the longest time we only utilized the accounting module in GP, when we decided to add the other modules in 2014 I believe that we weren't sure what information we would need to carry over so we asked our partner just to grab all of it from our old system (I've only been using the software since 2016, so there might be more to this story). As our business environment and knowledge of GP changed over the years, we have come to realize that we don't need a lot of the information that is in there and it is bogging down our inventory reconcile time. Our controller is okay with removing a lot of the old data because in some cases we have parts that a customer hasn't ordered in 5+ years.

    We have found that if we change an item to "discontinued" in the item card we can do a mass removal when we run year-end close, we actually realized this about an hour after I made the original forum post yesterday. This might be the route that we go considering that we are spending a good amount of time removing all of the BOMs, routings, histories, etc.

    Thanks again for the advice!

    ------------------------------
    Richard Warden
    IT Support Specialist
    The Spiratex Company
    Romulus MI
    ------------------------------

    Academy - Online Interactive Learning from Experts


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