Sage 100

 View Only

  • 1.  Inventory Item Change Process - Possible Alternative

    Posted 02-17-2025 09:10

    Sage 100 v2022 Premium.  They have about 300K items and most of them are one-time use. They want to clean up the item list.  The usual process I recommend is to merge the items to be "deleted" into the ZZZDELETE item code.  It took 24 hours to merge 500 items this weekend.  It would be a couple of years to finish the project and that is if they don't add any new items.

    Can I use a SQL query to do the merge?  I am reasonably sure a SQL query would be much faster.   Because the items are average cost valuation there are a few less tables that need to be updated.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------


  • 2.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 07:34

    I'm sure the official answer is No, but I don't see any technical difference (outside the frameworks error checking) in why you couldn't mass update the tables and then rebuild sorts....



    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------



  • 3.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 08:21

    Thanks @Steve Iwanowski  I am sure you're right about Sage's official answer being no.  Very unfortunate some of these processes haven't been re-worked to take advantage of SQL indexes.  We shouldn't have to be writing queries because the software utility was poorly written.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 4.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 11:49

    Years ago I was asked about the feasibility of doing the rename via SQL (in a similar circumstance).  In the end it didn't go anywhere.  The SQL logic isn't just a simple column value change for many tables, where ItemCode is a key column (IM_PeriodPostingHistory, SO_SalesHistory, IM_ItemVendor...).  You'd have to intelligently merge the values to handle things "properly".

    If you plan to crash and burn, doing the bare minimum, not caring about such tables... be sure to test it to an extreme level to ensure you aren't adding a ticking time bomb to the system.  (Eg. if you ever had to run *UTL item quantity rebalancing, would it choke on the inconsistencies?).



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 5.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 09:29
    Edited by Wayne Schulz 02-18-2025 09:31

    Why not copy this company to a ZZZ company and then in the test company try deleting items instead of merging? See if that's any faster. And if you're reason for not doing so is because you're afraid there could be history somewhere then you may have answered your second question about whether it would be safe to use SQL queries to merge ( unless your also planning to merge any history files via SQL ).



    ------------------------------
    Wayne Schulz
    wayne@s-consult.com
    Schulz Consulting
    (860) 516-8990
    CT
    ------------------------------



  • 6.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 11:46

    If these were one-off items, do they even care if they aren't left anywhere but invoice history?



    ------------------------------
    Phil McIntosh
    Friendly Systems
    ------------------------------

    Original Message


  • 7.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 11:53

    Because items can't be deleted when history for the item exists it is difficult to delete.  There are permanent item codes intermixed.  Maybe a query to delete the history for one time items then use the delete process in Sage 100.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 8.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 11:58

    One time items should never be created in Item Maintenance.  Use * items... or if they really need to track inventory, use a generic item, with the Lot# being specific to the one-time-use item.



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 9.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-18-2025 20:46
    Purge as much IM history as the client can tolerate. Then use the delete program logic found in an earlier 90 Mind’s post where you can delete records using VI. It won’t delete if there’s history. Then see what’s left. You might end up with a manageable list.

    This same issue exists for AR where there’s lots of history. These utilities take way too long to complete. I wish Sage or an MD would develop tools specifically for Premium, perhaps written as scripts, that allows these utilities to run more efficiently natively in SQL. Maybe where providex is used for an initial scan and validation, to confirm data integrity, but the work is done in SQL. Then again, I don’t know SQL well enough to know if it actually would be faster.



    ---------------------------------
    Bill Pfahnl
    Nims & Associates
    ---------------------------------





  • 10.  RE: Inventory Item Change Process - Possible Alternative

    Posted 02-19-2025 07:58

    Thanks for the suggestions, @Bill Pfahnl !



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


Related Content