Microsoft Dynamics GP Macros: Conclusion

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

Despite the limitations of GP macros, they are a very good way to either insert or update data in Microsoft Dynamics GP. Using the mail merge method does not require a user to be especially technical and the Excel formula method is actually quite an easy approach for many Finance users as they are usdually very capable in Microsoft Excel.

I, personally, favour the SQL method a lot of the time, due to the ease and flexibility afforded by T-SQL. I’m also fairly good at writing SQL so can get code written quickly.

However, if I am working on macros either alongside or for clients to run, I will always use the Excel formula method, again because of the flexibility possible in the formula.

Click to show/hide the Microsoft Dynamics GP Macros Series Index

Microsoft Dynamics GP Macros: Macro By Excel Formula

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

Over the last couple of posts, I’ve shown how data can be merged into the macro template using either mail merge or a SQL script. This post is going to show the final method I use for merging data into the macro template, which is to use an Excel formula.

I am using an Excel spreadsheet with two columns, USERID and PASSWORD. I am going to add a third column to the spreadsheet to hold the formula containing the macro.

User data in Excel

Continue reading “Microsoft Dynamics GP Macros: Macro By Excel Formula”

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 (http://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”

Microsoft Dynamics GP Macros: Macro By MailMerge

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

The most common way I update a macro template with the data to be updated, is to use Mail Merge in Microsoft Word.

Make sure you have your data prepared: in the demo system I’m using, I have only four users. I have created some new passwords based on the User Name field and stored them in an Excel spreadsheet:

Microsoft Excel

Continue reading “Microsoft Dynamics GP Macros: Macro By MailMerge”

Microsoft Dynamics GP Macros: Playing A Macro Quickly

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

As I said in the last post, which covered the playing of a macro, in this post I am going to show how the macro can be played quicker than using the standard Play option.

Credit for this one goes to MVP Leslie Vail, who blogged about it back in 2014. There is a Dex.ini setting which needs to be set to make this function available:

ShowAdvancedMacroMenu=TRUE

This needs to be configured on any client machine which will be used to run macros. If you are a user of the GP Power Tools you can use the Dex.ini Configuration to roll out this switch to all clients:

User Setup

Continue reading “Microsoft Dynamics GP Macros: Playing A Macro Quickly”

Microsoft Dynamics GP Macros: Playing A Macro

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

Before moving onto manipulating the macro template I am going to cover how to play back the macro; this way once you have your template ready, you already know how to playback the macro.

To play back the macro, we need to recreate the circumstances in which the macro was recorded; for this example, this means having the User Setup window (Administration >> Setup >> System >> User) open.

To play back the macro, click the Tools button on the action pane, then expand the Macro menu and click Play…:

User Setup - play macro

Continue reading “Microsoft Dynamics GP Macros: Playing A Macro”

Microsoft Dynamics GP Macros: Recording A Macro

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

Recording a macro is a fairly simple task, but I strongly recommend knowing the exact series of steps to be recorded, before starting to record the macro. I usually take a couple of practise runs before hitting record.

One of the common uses of macros for clients is to reset passwords when upgrading Microsoft Dynamics GP; due to versions of Windows and SQL Server supported by Dynamics GP, new servers are involved so these have different names (and the server name is used during encrypting the passwords.

So, in this series of posts, I am going to use resetting passwords as the example.

Macros are capable of opening and closing windows, but when resetting passwords this isn’t necessary so, before starting to record the macro, open the User Setup window (Administration >> Setup >> System >> User).

To record this macro, I am going to perform the following steps:

  1. Type a User ID.
  2. Type a new Password.
  3. Repeat this password in the Confirm Password field.
  4. Click Save.

To start recording the macro, click the Tools button on the action pane, then expand the Macro menu and click Record…:

User Setup

Continue reading “Microsoft Dynamics GP Macros: Recording A Macro”

Microsoft Dynamics GP Macros: Limitations of Macros

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

Macros can be a quick and easy way of inserting or updating information in Microsoft Dynamics GP, but there are some limitations to macros which need to be considered before using them.

A macro works by recording the exact series of actions taken by the user after clicking record; this means it is best to practise the series of steps the macro should record before clicking record.

When the macro is played, it repeats the exact steps as recorded. The way I typically explain this to clients, is to say that the macro pretends to be them typing very quickly. Which is a fair description of what the macro does; it records any typing and then plays back the typing; depending on what actions were recorded in the macro, you will see windows open and close and fields typed into.

This means that when a macro is being played back, Microsoft Dynamics GP must retain cursor focus or the macro will crash.

A macro plays back the exact steps as recorded and does not tolerate any deviation; this means that if the macro plays back and encounters a difference, such as a popup message dialog, the macro will crash and stop running. Any data updated will remain updated, so restarting the macro will likely require updated data to be removed from the macro file.

A frequent question asked by users is if they can build in conditions within the macro. The short answer is no, you can’t as macros simply play back a series of steps. There is a longer answer which I will cover when in the posts on producing a macro from a macro template using Excel and SQL.

When recording a macro which will open windows, it is best to use the drop down menus at the top to open the window, as using the area page menu will often fail as this used the exact mouse position; when users using the same macro have different access configured, the menu option will be in different locations and therefore cause the macro to fail.

Click to show/hide the Microsoft Dynamics GP Macros Series Index

Microsoft Dynamics GP Macros: Series Index

Microsoft Dynamics GPUsually when integrating data into Microsoft Dynamics GP we will use Integration Manager, but sometimes this tool is either not available or suitable. Not all clients have a licence for the Customisation Pack (which includes the licence for Integration Manager) or the data to be imported does not have an available adaptor in Integration Manager; examples of the latter which I’ve done work with recently include Bill of Materials and Extended Pricing.

The alternatives to using Integration Manager include other tools such as SmartConnect or Scribe (which have their own price tags attached), direct SQL inserts (which do not respect business logic in the application) or the use of GP macros to play back actions.

This latter method is what I am going to cover in this series; I will cover the recording and playback of macros and how data can be loaded in bulk by using the recorded macro as a template.

Microsoft Dynamics GP Macros
Limitations of Macros
Recording A Macro
Playing A Macro
Playing A Macro Quickly
Macro By MailMerge
Macro By SQL
Macro By Excel Formula
Conclusion