Recent ISC Software Webinar: A Macro Overview for Microsoft Dynamics GP

In our most recent webinar, we took a look at A Macro Overview for Microsoft Dynamics GP. In this webinar, we took a look at what Microsoft Dynamics GP macros are, how they can be used and ran through some examples. If you want to catch up on this, or any other, webinar, you can do so here.

1. Introduction
2. What are macros and how to use them
3. Pitfalls
4. Recording and Playing Macros
5. Macro by Mail Merge
6. Macro by Excel
7. Macro by SQL
8. Conclusion

Introduction ^

in this webinar we took a look at macros in Microsoft Dynamics GP, giving an overview of what they are how they can be used and ran through some examples of how they could be used.

I have previously blogged about macros in Dynamics GP and the webinar was along similar lines, albeit using different examples.

What are macros and how to use them ^

Like Excel macros, Dynamics GP macros allow you to automate repetitive tasks such as setting up a transaction, resetting user passwords or even entering whole master records or transactions.

I have, in the past, helped clients set up macros to create the basics of a bank transfer and while lots of clients have Integration Manager or other tools like SmartConnect, I still typically use macros to create fixed assets and the related book values.

In this webinar we run through the basics of recording and playing a macro and then showed how they could be created in a more advanced way using mail merge, Excel formulas and SQL queries.

Pitfalls ^

There are a few pitfalls/restrictions which need to be remembered when using macros.

Firstly, macros can’t cater for any differences; if you run a macro and an unexpected window or message pops-up the macro will crash. You will need to remove the data already updated and then restart the playback.

Secondly, macros require that Dynamics GP has focus. If another application, message box or screen saver take focus away from Dynamics GP then the macro will crash and need to be restarted (having removed the data already updated).

Thirdly, macros are not intelligent which means checkboxes especially have to be handled with care. A macro merely records that a checkbox has been clicked, not the state it is changed to. This means if you have a macro to check two boxes and one is already marked and the other unmarked, they will end up unmarked and marked respectively. this is one of the reasons why it is important to remove data already updated if the macro has crashed and is going to be restarted.

Fourthly, when recording a macro which will open a window, always use the drop down menus as the flat menu pages will record the click position rather than the specific item selected.

Recording and Playing Macros ^

Recording a macro is easy to do. I previously blogged about recording a macro to change a users password, but in this webinar I used the example of setting up a bank transfer.

In the macro I recorded steps to enter a description, select the from and two accounts and set a distribution reference for the from account:

# DEXVERSION=18.00.0028.000 2 2
  CommandExec dictionary 'default'  form 'Command_Financial' command 'CM_Transfer_Entry' 
NewActiveWin dictionary 'default'  form 'CM_Transfer_Entry' window 'Transfer_Entry' 
  MoveTo field '(L) Description' 
  TypeTo field '(L) Description' , 'Transfer to Petty Cash'
  MoveTo field 'Checkbook ID' 
  TypeTo field 'Checkbook ID' , 'UPTOWN TRUST'
  MoveTo field 'Distribution Reference' 
  TypeTo field 'Distribution Reference' , 'Transfer to Petty Cash'
  MoveTo field '(L) To ChekbookID' 
  TypeTo field '(L) To ChekbookID' , 'PETTY CASH'
  MoveTo field '(L) To DistReference' 

After recording the macro, I showed how the macro could be played back. I also showed how it can be added to the home page navigation pane with a keyboard shortcut so it can be launched using a key shortcut from anywhere without needing to return to the home screen. If you add a shortcut you could add it to the User Class folder allows you to make it available to a group of users instead of needing to set it up individually.

One thing I didn’t cover in the webinar, in part because of time constraints, is playing the macro faster. There is a Dex.ini switch which can be enabled and will (sometimes, but not always) allow the macro to be played back faster.

Macro by Mail Merge ^

So far the examples shown have always been for single item updates; we recorded these steps and will play them back exactly as they are. However, macros are much more flexible than this. One of the easiest methods for clients to use is the macro by mail merge approach.

This requires recording a macro as shown earlier in the webinar and then using Microsoft Word and mail merge to update the macro for all the records in a spreadsheet. In the webinar, we gave an example of updating the payment priority of vendors.

Prior to the webinar, I exported a SmartList of vendors with their current Payment Priority and added a column of new priorities.

I then recorded a macro of updating the payment priority, with the Creditor Maintenance window already open:

# DEXVERSION=18.00.0028.000 2 2
CheckActiveWin dictionary 'default'  form 'PM_Vendor_Maintenance' window 'PM_Vendor_Maintenance' 
  TypeTo field 'Vendor ID' , 'ADVANCED0001'
  MoveTo field 'Vendor Name' 
  MoveTo field 'Options Button' 
  ClickHit field 'Options Button' 
NewActiveWin dictionary 'default'  form 'PM_Vendor_Maintenance' window 'PM_Vendor_Maintenance_Additional_Information' 
  MoveTo field 'Payment Priority' 
  TypeTo field 'Payment Priority' , '1'
  CommandExec dictionary 'default'  form 'PM_Vendor_Maintenance' command 'OK Button_w_PM_Vendor_Maintenance_Additional_Information_f_PM_Vendor_Maintenance' 
NewActiveWin dictionary 'default'  form 'PM_Vendor_Maintenance' window 'PM_Vendor_Maintenance' 
  CommandExec dictionary 'default'  form 'PM_Vendor_Maintenance' command 'Save Button_w_PM_Vendor_Maintenance_f_PM_Vendor_Maintenance'

With the macro recorded, I was able to use mail merge to merge the data in the spreadsheet into the macro for playing back. In an previous blog post merge I showed how to use mail merge in Word to do this.

Macro by Excel ^

The mail merge method is probably the most user friendly method for clients to create and use their own macros, as long as they have knowledge of Excel, Word and mail merge. Another approach I take for clients for whom I am creating the macro, is to use an Excel formula.

This means I create a spreadsheet into which they can populate the data and the first column contains a formula which concatenates the macro with the data; when they click Save there is some VBA which allows them to save a macro file to a location of their choice for playing back in Dynamics GP.

This approach works well for clients without the ability to mail merge the data themselves as all of the work is already done in the formula. I did this as an example foe updating user passwords a while ago. The example I discussed in the webinar was for updating standard costs on items, which was based off a piece of work I did for a client.

I have also previously used this method to allow a client to create a macro which would update standard costs; this was quite a bit more complicated as I had to cater for variable amounts of data per item. For example, some items only had two price breaks whereas others had four or even five price breaks.

Early in the webinar, I said macros couldn’t vary and, strictly speaking they can’t, but you can use the Excel formula to generate a slightly different macro for different records.

Macro by SQL ^

The final method I briefly discussed was creating macros by SQL query; this is similar to the macro by Excel formula approach where the SQL query has the macro embedded and concatenated with the data. Like the Excel formula approach, you can also use the capabilities of the SQL query to vary the macro which is created for each record.

Conclusion ^

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 usually 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, but mainly for the ease of use for the client.