Use SQL to generate a macro to create Vendor Items

Microsoft Dynamics GPOn a recent project with a client to implement the Purchase Order Processing module, we needed to create a lot of vendor item numbers. I’m a big advocate of doing updates in the front-end where possible or using integration tools where they support standard functionality. This particular client didn’t have a suitable integration tool available for inserting vendor items, so instead I created a SQL script which would generate a Microsoft Dynamics GP macro which can be played back to insert the records.

The script below will link all active vendors with every active non-discontinued item; it can easily be amended to limit the selected vendors or items (which is how we used it by running it several times with different restrictions):

/*
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=18.00.0021.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Vendors'' window ''IV_Item_Vendors'' TypeTo field ''Item Number'' , ''' + RTRIM(REPLACE(ITEMNMBR, '''', '~"')) + ''' MoveTo field ''Vendor ID'' TypeTo field ''Vendor ID'' , ''' + RTRIM(REPLACE(VENDORID, '''', '~"')) + ''' MoveTo field ''Vendor Item Number'' CommandExec dictionary ''default'' form ''IV_Item_Vendors'' command ''Save Button_w_IV_Item_Vendors_f_IV_Item_Vendors'' ' FROM PM00200 AS ['PM Vendor Master'] CROSS JOIN IV00101 AS ['Item Master'] WHERE VENDSTTS = 1 AND ITEMTYPE <> 2 -- discontinued AND INACTIVE = 0 AND ( SELECT COUNT(*) FROM IV00103 WHERE VENDORID = ['PM Vendor Master'].VENDORID AND ITEMNMBR = ['Item Master'].ITEMNMBR ) = 0