Deleting items from Inventory in Dynamics GP 10 using SQL

Question Status

glenndw asked a question on 16 Mar 2009 4:00 PM

 My company has several thousand inventory items that are obsolete but still in the inventory.  Although none of these part numbers has any quantity (on-hand or on-order) associated with them, they all have history, which means removing them manually would be painful and time-consuming.

I generated a csv file of these item numbers, thinking to use that as input to a program which would delete each part from the Item Master table(IV00101) using a SQL DELETE command.  But I'm worried about the dangling dependencies this would leave, such as Item Quantity Master (IV00102) and Item Price List (IV00108).  Am I okay to delete only from the Item Master table?  If not, what other tables do I need to include?  Is there another, better way to do a mass delete?  Thanks!



Ian Stewart responded on 17 Mar 2009 8:01 AM

Hi Glenn,

If it was me, I would first mark all of the items as discontinued.  To delete them, you would need to untick the maintain history options for each item, and also ensure the item does not exist on an unposted SOP, POP or INV transaction. Note, that once you untick maintain history, the items will no longer appear on the history stock status or transaction reports.

I would do the 'update to discontinued' and 'delete' through standard GP functionality rather than using SQL statements. At least if you use the standard functionality, you can't make a mistake and delete an Item that has a critical dependancy (Link a POP Purchase Order or something).

To automate the process of doing the above through standard functionality, you could write a macro that updates all of the items on your list to be discontinued, and then write another macro to delete them.

Maybe others will have a different view, but by doing it as above you are sure that only items that can be validly deleted will be deleted, and you don't have to worry about deleting records any other tables etc.

Best regards,

glenndw responded on 23 Mar 2009 4:00 PM

 Thanks for the suggestions, Ian.  I agree that using GP functionality is safest.  However, I haven't been able to get a keystroke macro to work.  I 'unticked' maintain history, but it won't delete any part numbers that still have history attached to them, and that's most of them.  So when I try to manually delete them,  I have two windows open:  Item Maintainence and the main Inventory page where I can access the link to Remove Transaction History.  I make sure these windows don't overlap so I can move from one to the without having to click to bring one of them forward.  But when trying to set up a keystroke macro, the macro fails when it tries to open the Remove Transaction History window, which inconveniently disappears after each item numbers' history is deleted (sometimes I luck out with a range of 3 or 4 part numbers that are consecutive).

 My VB macro writing skills aren't strong enough to call windows up and take item number input from a file, although I can get a start by looking at the macros that I generated by recording keystrokes and see how things are done in that file.

 Anyway, if you can offer any help to me in the macro-writing phase, I appreciate it.  If not, thanks for the help you've given so far.

 Thanks, Glenn