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 (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

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you’d like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

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 *