Sage 100

MAS 200 SQL v4.5.5 Inventory, Utilities, Delete and Change Items. Getting ""No Action - Item exists in inventory history.

FYI.... talked to Rene at Sage yesterday. He was stumped also. He did some research and called me a few minutes ago. Apparently beginning with 4.5 the Delete utility changed. The Delete utility will remove all instances of an item from many different tables EXCEPT the IM_ItemTransactionHistory file. So...... if you want to delete an item all of its transaction history must first be deleted from IM_ItemTransactionHistory. YOU HAVE GOT TO BE FKM! My client has 2000 items to be deleted. Now I need to break the news that we have to do some custom work to first remove all of the history for the 2000 items before we can begin to use the utility. WHAT freaking good is the utility if you have to write a custom program to first delete the history? Thank you.. I feel a bit better.

Doug what about merging everything to an unused number - something like ZZZZZ? Admittedly a workaround but at least you have the appearance of getting rid of the items.

@DougHiggs - since it is SQL you can do this without much trouble: DELETE FROM IM_ItemTransactionHistory WHERE ItemCode = (SELECT ItemCode FROM IM_DeleteChange) or something similar - doing this off the top of my head so check the table and field names and test your query on a test company first. This is why having SQL is important - it protects you from stupid programming tricks!

Also can you use the purge utility? Go to Inventory Management / Utilities / Purge Item History.

Oops - looks like the purge item history utility is global --- not selectable by an item or item range...

Maybe I'm wrong, but I believe the reason you can't delete items with transaction history is so that if you run one of those inventory utilities to reconstruct period posting history it will have all of the transactions in place in inventory to do so. Merging the unneeded items into one dummy item like Wayne suggested works well.

@PhilMcIntosh - Thanks for the SQL statement. That WAS the direction I was headed however I believe the work around provided by @WayneSchulz is the path of least resistance and will work well. There are over 2000 items that would need to be removed from 2 different tables so the SQL statement would be quite lengthy. I am going to present these 2 options to the customer but I believe opting for the merge to one item is the prudent play. Thanks to all of you!

@DougHiggs the ""WHERE ItemCode = (SELECT ItemCode FROM IM_DeleteChange) "" gets all the items in the deletechange file without having to specify each one individually, but @WayneSchulz idea does work as well, with the advantage that the detail is still there for Sage utilities to use in generating random counts and values...

@PhilMcIntosh - OK... I see now. That makes using the SELECT statement a lot more feasible. I am going to present both options and let him decide. These 2000+ items also need to be removed from the IM_SalesKitHeader and SalesKitDetail because they used kits prior to installing bill of materials. Most of these items are kits.

@JaneScanlan - you are probably right. Why doesn't the utility remove the history detail?