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 a Dynamics GP Consultant ( 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
) = 0

What should we write about next?

Your Name (required) –
Your Email (required) –

Leave a Reply

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