Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL Script To Create macro To Activate BOMs
August 8th, 2016 by Ian Grieve
While implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).
This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.
I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.
The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.
However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:
/* 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK). */ SELECT DISTINCT '# DEXVERSION=11.00.0364.000 2 2 CheckActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + ''' MoveTo field ''Bill Status'' item 0 ClickHit field ''Bill Status'' item 2 # ''Pending'' MoveTo field ''Expansion Button 1'' ClickHit field ''Expansion Button 1'' NewActiveWin dictionary ''default'' form bmBillMaintenance window ChangeStatus ClickHit field ''Bill Status'' item 1 # ''Active'' MoveTo field ''Process Button P'' ClickHit field ''Process Button P'' NewActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance MoveTo field ''Save Button'' ClickHit field ''Save Button'' ' FROM BM00101 AS BMH INNER JOIN BM00111 AS BMC ON BMC.ITEMNMBR = BMH.ITEMNMBR WHERE BMH.Bill_Status = 2
I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.
Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.