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%'
Continue reading “Microsoft Dynamics GP Macros: Macro By SQL”
