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