Microsoft Dynamics GP Macros: Macro By SQL

In this series I am taking a look at how macros can be used to update data in Microsoft Dynamics GP.

In the last post in this series, I showed how to merge the data into the macro template; another approach I have used a few times is to write a SQL query which returns the data already in the macro. This is done by embedding the macro file into the SQL Query:

/*
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 '# DEXVERSION=16.00.0034.000 2 2
CheckActiveWin dictionary ''default''  form ''Enter_User_Names'' window ''Enter_User_Names'' 
  TypeTo field ''User ID'' , ''' + RTRIM(USERID) +'''
  MoveTo field ''User Name'' 
  MoveTo field Password 
  TypeTo field Password , ''' + LEFT(RTRIM(REPLACE(USERNAME,' ','')),14) + '1' + '''
  MoveTo field ''(L) Confirm Password'' 
  TypeTo field ''(L) Confirm Password'' , ''' + LEFT(RTRIM(REPLACE(USERNAME,' ','')),14) + '1' + '''
  CommandExec dictionary ''default''  form ''Enter_User_Names'' command ''Save Button_w_Enter_User_Names_f_Enter_User_Names'' 
NewActiveWin dictionary ''default''  form ''Enter_User_Names'' window ''Enter_User_Names'' 
ActivateWindow dictionary ''default''  form ''Enter_User_Names'' window ''Enter_User_Names'''
FROM
	DYNAMICS..SY01400
WHERE
	USERID <> 'sa'
AND
	USERID <> 'DYNSA'
AND
	USERID NOT LIKE 'LESSON%'

This query is selecting the data directly from the Users Master (SY01400) table in the system database (typically called DYNAMICS and creating the passwords automatically based on the first 14 characters of the User Name with the spaces removed and suffixed with a 1.

SQL Server Management Studio should have the Result to text option set:

The macro text can be copied into Notepad and saved as a macro file for running.

I find this approach easier and quicker than the mail merge method I showed in the last post. However, this approach does require knowledge of SQL scripting and access to SQL Server Management Studio; good when working with IT people, but not of much use if your contact is in a non-IT department such as Accounts or Finance.

Two other examples of SQL script to create a macro are activating Bill of Materials and loggin into each company which you need to do after upgrading a system which uses the VAT Daybook module.

This method has a major advantage over the mail merge method of merging data into the macro template; the SQL script can have case statements built into it to handle a variety of differences in the data. The first time I did this was about 5 years ago for a client using WennSoft Job Cost; there was a bug which resulted in the Tax being incorrect on transaction lines.

As the transactions had a variable number of lines, a mail merge would not work. So I created a SQL script which was able to handle the variable number of lines and update all of the data.