Microsoft Dynamics GP Macros: Macro By SQL

Microsoft Dynamics GPIn 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:

SQL Server Management Studio - SQL Query

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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Microsoft Dynamics GP Macros: Macro By SQL

Leave a Reply

Your email address will not be published. Required fields are marked *