SQL script to create macro to delete items in Microsoft Dynamics GP

Microsoft Dynamics GPI’ve been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn’t want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.

Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.

I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.

Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT '# DEXVERSION=14.00.0085.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' TypeTo field ''Item Number'' , ''' + CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + ''' MoveTo field Inactive # ''FALSE'' CommandExec dictionary ''default'' form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance'' # Are you sure you want to delete this record? NewActiveWin dictionary ''default'' form DiaLog window DiaLog ClickHit field OK NewActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' ' FROM IV00101 AS ['Item Master'] LEFT JOIN ( SELECT ITEMNMBR FROM POP10110 UNION SELECT ITEMNMBR FROM POP30110 UNION SELECT ITEMNMBR FROM POP10210 UNION SELECT ITEMNMBR FROM POP30210 UNION SELECT ITEMNMBR FROM SOP10200 UNION SELECT ITEMNMBR FROM SOP30300 UNION SELECT ITEMNMBR FROM IV30300 ) AS ['Used Items'] ON ['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR INNER JOIN IV00102 AS ['Item Quantities'] ON ['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR AND ['Item Quantities'].LOCNCODE = '' LEFT JOIN IV10301 AS ['Stock Count Line'] ON ['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR WHERE ['Used Items'].ITEMNMBR IS NULL AND ['Item Quantities'].QTYONHND = 0 AND ['Item Quantities'].QTYRTRND = 0 AND ['Item Quantities'].QTYINUSE = 0 AND ['Item Quantities'].QTYINSVC = 0 AND ['Item Quantities'].QTYDMGED = 0 AND ['Item Quantities'].ATYALLOC = 0 AND ['Stock Count Line'].ITEMNMBR IS NULL

If when you run the script you only get part of the macro text, you can change the query results length.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

Leave a Reply

Your email address will not be published. Required fields are marked *