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).
	'# 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'' 
	BM00101 AS BMH
	BM00111 AS BMC
	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”