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

SQL Script To Create macro To Activate BOMs

Microsoft Dynamics GPWhile 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 (https://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.

Fixed Assets Now Disallows Hyphens In The Asset ID

Microsoft Dynamics GPI heard about this problem a short time ago, but have just had a client upgrade to Dynamics GP 2013 R2 and encounter this problem:

Microsoft Dynamics GP - The asset ID cannot contain a hyphen. Select a different asset ID.Microsoft Dynamics GP – The asset ID cannot contain a hyphen. Select a different asset ID.

This issue was discussed at some length on the Community Forum with Frank Hamelly supplying a SQL script to update the assets.

With my client I took a slightly different tack and used the Fixed Assets Modifier tool in the Professional Services Tools Library (PSTL). This tool works on a single asset at a time and, unlike some of the other modifiers, does not allow import of a spreadsheet.

However, PSTL is written in Dexterity and allows the use of Dynamics GP Macros; so after doing an extract of all Asset IDs I was able to use mail merge to generate a macro to update all fixed assets containing a hyphen in the Asset ID.

I have checked Microsoft Dynamics GP 2015 and a hyphen is still disallowed in the Asset ID field.

Automate Login To Microsoft Dynamics GP 10

Microsoft Dynamics GPClients who have upgraded to Microsoft Dynamics GP 2010 can use the new remember user and password functionality, but what about those who have not yet upgraded?

Well, in Microsoft Dynamics GP 10, and before, you can use a login macro to automate the login process. A macro can be created by pressing Alt + F8 on the login screen and following the normal login process before pressing Alt + F8 again to stop recording. Continue reading “Automate Login To Microsoft Dynamics GP 10”