Use SQL to generate a macro to create Vendor Items

On 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