Tuesday, September 16, 2014

How to Create and Run a SIMPLE Macro in Dynamics GP

For those new to Dynamics GP, macros require an introduction. There is a tool in Great Plains which allows a user to record their keystrokes and mouse clicks...  Tools > Macro > Record 

What you can do with this tool ranges from the simple to the complex.  

I will start with a high level overview of the macro process.  And like many of my blogs, it begins with a story.   Many moons ago, when I was a fledgling consultant, I was at a Great Plains event. Long before Great Plains was acquired by Microsoft.  A couple of gentlemen named Martin Hildebrand and Sean Mahoney from the Taylor Group, one of the preeminent Great Plains partners at the time, were having a discussion about the use of Macros...  

Apparently, you could record the act of adding an inventory item into Great Plains, then cut and paste the recorded Macro text into Microsoft Word.  In Word you could use a source file of inventory items in Excel (or Text) format in concert with the Mail Merge feature, typically used to automate form letter writing to replicate this simple macro code and combine it with the data from your list.  

The mail merge would duplicate the Macro code for each of the Inventory Items in the source file. Simply, paste the merged documents back into the Macro and run it in Great Plains.  Presto! The unbelievable result was the import of all the items in the source file.

That was it, using the knowledge acquired from this casual conversation, I dumped inventory items into an Excel file from Macola -- yes, Macola - fledgling GP consultants have to come from somewhere. 

Using these inventory records, a Macro and the Mail Merge process in Word, I created my first Macro, and before I boarded my plane, I had uploaded all of my inventory items into a test Great Plains database (which I am pretty sure was running on Btrieve). 

I never looked back – Macros could do things nothing else could.  Have a third party product, Macro.  Need to import data into custom fields, Macro.  Want to update records inserted using integration manager, Macro.  Want to delete a bunch or records from GP, Macro.  Want to update your counts when doing Physical Inventory – you guessed it, Macro!  I went absolutely bonkers for Macros.


This blog-post is meant to provide a simple example of Macro use.  In this instance we used macros to delete a number of customer records for a customer using WennSoft Equipment Management.  Let's call this operation, customer cleanup - subtitled, have the client remove undesirable records before you import them.

The first step in Macro recording is not, as you might suspect, to record entering a transaction.  It is to prepare to record a transaction.  You must ensure you touch every field you will require, in order to be able to map all the data necessary into the system - deleting records is no exception.  

Consequently, you need your import file - this will provide you a complete list of fields you need to find homes in GP for.  Once you have the list of fields, do a dry run on the window, to figure out if any dialogue boxes will pop up - any activity on the screen will be captured by the macro, superfluous activity is undesirable.  Once you've completed your dry run and know you can enter a transaction without errors/dialogues interrupting, you can record your Macro file.

You need to open the window, ensure it is in focus and the cursor is in the starting field and starting field is empty. Type the field value and move directly, via mouse, to the next required field - tabbing through fields will touch fields you don't need in your macro, which increases the length and complexity of your macro code unnecessarily. Worse yet, clicking on a look up button, rather than typing, will add a mess of useless lines to your Macro - trust me, don't do it.

Once you have completed your transaction click on the OK or Save button, in most cases your cursor will return to the starting field on the form.   If not, click clear, or something similar to get it there - it needs to be there, because macros require a loop to work properly.  By loop I mean, in order for Macros to work, they must cleanly move through the form, inserting data, save this data, and return to the first field on the form to begin processing the next record.

Now you have some Macro code - it should look something like this.

# DEXVERSION=11.00.0359.000 2 2
CommandExec dictionary 'default'  form 'Command_Sales' command 'RM_Customer_Maintenance'
NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'
  TypeTo field 'Customer Number' , 'SAMPLE'
  MoveTo field 'Delete Button'
  ClickHit field 'Delete Button'
# Are you sure you want to delete this customer record?
NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog
  ClickHit field OK 

It will be much longer than this, if you're doing something substantial, but this is an example.  What is important to note, is, as I said, Macros run in loops, so DO NOT cut and paste the entire macro into Word.

What you want is the start and end of your transaction:

NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'
  TypeTo field 'Customer Number' , '«CustomerID»'
  MoveTo field 'Delete Button'
  ClickHit field 'Delete Button'
# Are you sure you want to delete this customer record?
NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog
  ClickHit field OK 

Now that you have your transaction loop pasted into Microsoft Word, go to the Mailings tab in Word Navigation and Start Mail Merge > Letters. Then Select Recipients > Use an Existing List. Then browse to the Source file and select it.  Now you should have list of Merge Fields to choose from.  Select the area inside the single quotes around the customer ID, 'SAMPLE' you entered and select Insert Merge Field, choosing the <<CustomerID>> merge field - it should now look roughly like the image below.


Now choose the Finish & Merge button from your Word Mailings navigation.   This will create a new Word file with one transaction for each line in your source file.  Select all the records by clicking on Ctrl+A, copy them all by selection Ctrl+C and paste them back into your Macro File using Ctrl+V (Ctrl+P prints things), perform a 'save as' function, so you have your original Macro file as a template should you need it (this does not always go right on the first attempt).

Open Dynamics GP, navigate to the window you want to import your data into (this is critical path).  From the now open window choose Tools > Macro > Play and browse to your the macro file which contains all the records you wish to import.  Great Plains will begin to rapidly enter the information on your behalf, or in this case, delete and confirm the deletion of the customers in your list.

Finer Points:

When editing Macros you might want to use a product like Notepad++.

When troubleshooting macros, which will tell you what line they encounter a problem on, and not much else, you definitely want to be using Notepad++ (Notepad++ does not pay me for endorsements). 

https://notepad-plus-plus.org/

Notepad++ shows the line numbers in a text file, and when a macro stops, the dialogue box will note what line it stopped on. If your macro is thousands of lines long, you'll appreciate not having to manually count the line numbers.

You may also notice, when the macro stops, it stops on the record it was last working on, so if you don't have Notepad++ you can search your macro file for the master record your macro stopped processing on.

When saving a macro file, make sure you do not save it with txt extension, if you do, change it back to the .mac extension prior to running your macro. 

Apple keeps associating .mac files with Apple's QuickTime application - it cannot successfully open them - believe me, I have tried. You do not have to change the file association, as you will be browsing to your macro file from within Dynamics GP, and it will see them, even if the association has been made by Apple.

When editing macros, right click on Macros and select Edit or Open With > Select program, choose Notepad++.

Helpful Hint... When entering a significant number of records, it is not uncommon for something to interrupt the normal processing of a macro (they can be temperamental). If this happens, keep calm. It is possible to find the place your macro stopped, remove the preceding data/lines from the macro, which have already been imported and restart the macro, once you've fixed the problem. 

It is not uncommon to do this multiple times during the testing process. This can be invaluable, as you can note what caused the problems, and fix the problems in the live environment prior to running the macro there, where it should run uninterrupted.

One potential cause of problems is touching the mouse, which can take the focus off the window you're importing data into. When this happens, you'll get an error that roughly states the window is not active/or field does not exist in active window. These problems are best avoided, by not touching the mouse.

NOTE: A word about single quotes (').  For Macros, this character is CRITICAL - it defines the boundaries for the data entered by the macro (i.e. 'DATA').  From time to time, you will experience difficulties with imports, because data contains single quotes (apostrophes). Customer and Vendor Descriptions like O'Doule's, O'Malley & O'Reilly will reek havoc on a macro.  If you experience this type of error, you can perform data transformation, by using scripts to remove the undesirable character, or request the customer clean up the data prior to import.

Be not discouraged... I have been doing this for a very long time, I have had people, in-a-fit, going out of their mind, when a Macro isn't doing what they want, call me exasperated. I can typically look at the macro and tell them what the problem is.  Your initial attempts might fail, but keep after it.  I plan to write a few more blog posts on this subject covering intermediate and advanced applications for Macros.

Finally, A WARNING! DO NOT comment and say you deleted the entire customer list in your Live company with no backup while testing this example... There are so many things wrong with that sentence, I can't begin to enumerate them.  I will indeed laugh at you... No, I won't laugh, I may even cry and commiserate with you. Please leave yourself some options by having a backup handy.

If you're going to run macros, test them. When running them, make a backup first. Macros can update a lot of data, very rapidly. Watch your macro run, if it looks like it's doing something you don't like, you can stop it fairly easily. Almost any movement or click of the mouse will interrupt your process (conversely, when things are going right, leave the mouse alone).



23 comments:

  1. I might just add from own experience of Macros, that if you are running some sort of very long macro with many lines of data to be entered (as with pricing updates to all Inventory Items for example), that you must not have any screensavers enabled. The macro will stop running as soon as the screen switches off or to screensaver mode. As stated above, the active window must be visible at all times! (in focus I think was said).
    Make this change if you need to before you begin to run the macro- it's not much fun watching the first 1000 entries of a 3000+ line macro run for 10 or so minutes, and then the screen just switches off and the macro must be re started from the beginning!

    ReplyDelete
  2. If your macro quits running prior to completion, the best approach to the problem is to find the point it terminated, remove all lines prior to the macro stopping, and start the macro from the point it failed -- restarting the macro from the beginning is unnecessary. This fix requires a little finesse, but from experience, I can tell you there are a number of reasons a macro will stop functioning, so learning how to resume a macro is a valuable skill.

    ReplyDelete
  3. Excellent tool to use, especially when doing imports from a third party. Regarding your note about the single quote (apostrophy), if in your source data you substitute ~"(tilde quote) for the '(apostrophy) the macro will load the '.

    ReplyDelete
  4. This is excellent.. Now we don't have to pay big bucks to our service provider to create something for us. Thank you!!!

    ReplyDelete
  5. After adding an "INSERT PAUSE" in a macro, Say for adding a date field, how can you continue the macro with a Keyboard shortcut? Insert pause is a great way to add manual data into a field and then continue the macro. Currently, we have to click tools --> Macro --> Continue. A keyboard shortcut for continue would be great!

    ReplyDelete
  6. Nathin,

    Your idea is a novel one. There is a mechanism in Dynamics GP to allow for the assignment of Keyboard shortcuts to shortcuts maintained in the Navigation Pane, by right clicking and selecting shortcut properties; however, since the Continue command does not have a screen associated with it, it defies being configured in this way.

    ReplyDelete
  7. Love this post, in particular the relaxed style in which its written. Harry, you're a cool and most generous dude and I thank you as someone who was just about to spend hours (perhaps even days) updating some seriously mundane records to a GP instance.

    ReplyDelete
  8. I have a series of macros to import data via mail merge, updating the order with batch, date, text, transferring to invoice, as well as distribution to have funds allocated. I'd like to have another macro auto-populate the mail merge process in order to make the series of steps less cumbersome for other users in my group. Thoughts on creating a macro for the mail merge?

    Thanks in advance!

    ReplyDelete
  9. Congratulations, it's a very good article! You have a very knowledgeable and are very wise! Best regards and have a nice day

    ReplyDelete
  10. Just wanted to say thanks!

    ReplyDelete
  11. Can I use the Macros to edit the UDF user category fields on items?
    I stumbled onto a few hundred items that are missing 'item_type' category assignment.

    Cheers!

    ReplyDelete
    Replies
    1. Chris - You sure can. Since the Categories are on the Option Maintenance Window and the Item Number cannot be typed directly into this window, you will need to start on the Item Maintenance Window, and click the options button from there. Additionally, to keep the macro clean, you'll want to manually type, not lookup the category value. Good Luck!

      Delete
  12. Is there some way to trap when errors occur? Say for example in some cases there is a popup you need to click YES to and in other cases there is not. Can the macro detect if a form is present before it executes a command - and in some cases skip that command when it finds the form is not there? Thank you!

    ReplyDelete
    Replies
    1. Austin - add the following line to your Dex.ini file (located in the Dynamics Workstation directory:

      ShowAdvancedMacroMenu=TRUE

      Using the Advanced Macro Menu you can Jump into a Macro at a specific line, Step through the macro to debug it, restart the macro at a specific line and other options.

      Leslie Vail, a long-time MVP has a great post on this feature, located here:

      https://community.dynamics.com/gp/b/gplesliev/archive/2014/07/24/speed-up-your-macros

      Delete
    2. Austin - to be fair, you asked about trapping errors. That subject is not covered in the blog article I pointed out about the Advanced Macro Window. To trap errors you need to add the following line at the top of your macro, prior to running it. It defines the location of the macro log file, which can be reviewed for the exact errors encountered during the macro run.

      Logging file ‘:c:GP/Log/maclog.txt’

      Delete
  13. Austin - add the following line to your Dex.ini file (located in the Dynamics Workstation directory:

    ShowAdvancedMacroMenu=TRUE

    Using the Advanced Macro Menu you can Jump into a Macro at a specific line, Step through the macro to debug it, restart the macro at a specific line and other options.

    Leslie Vail, a long-time MVP has a great post on this feature, located here:

    https://community.dynamics.com/gp/b/gplesliev/archive/2014/07/24/speed-up-your-macros

    ReplyDelete
  14. Austin - to be fair, you asked about trapping errors. That subject is not covered in the blog article I pointed out about the Advanced Macro Window. To trap errors you need to add the following line at the top of your macro, prior to running it. It defines the location of the macro log file, which can be reviewed for the exact errors encountered during the macro run.

    Logging file ‘:c:GP/Log/maclog.txt’

    ReplyDelete
  15. We have setup a new company using the copy setup from another company. Then copied the customers and vendors over from the old company to the new company in SQL. I ran check links as well. The problem is the default accounts for the vendors and the customers are all different. Is there a way to remove the default accounts for the vendors and customers or correct them using your macro method?

    ReplyDelete
    Replies
    1. There are a couple of ways to perform this kind of change with a macro. If you have list of accounts for each master record and type of transaction and they are inconsistent, recording a macro that changes each account is the way to go. Realize that accounts are actually a collection of segments, so you will need a column in your source for each account segment. Alternatively, if they are consistent, you can use Class IDs to assign accounts (and other setup options), then use a macro to assign the classes to your master records, and roll down the changes for the class.

      Delete
  16. I am working on GP macro which new to me, While ply the macro i am getting error as cannot type to field.(Line#11)

    can anyone guide me to rectify this error.

    ReplyDelete
    Replies
    1. If you look at line 11 (literally the 11th line in your macro), it will have something like "TypeTo field 'Field Name' , 'CustomerIDData'"... There are a couple of ways to address this. Check for misspellings or imperfections in the line vs. your recorded macro code. And make sure the window you are running this against is "in Focus" - the window in the foreground. If you could provide a sample of your macro code, that would help.

      Delete
  17. Thank you Harry,
    Could you please help me with the macro code below and i would like to have code to run these macro for around 800 records, can you help me in doing this.

    # DEXVERSION=12.00.0311.000 2 2


    NewActiveWin dictionary 'default' form 'SOP_Entry' window 'SOP_Entry'
    ClickHit field 'SOP Type' item 3 # 'Invoice'
    ClickHit field 'SOP Type' item 3 # 'Invoice'
    MoveTo field 'Document ID'
    MoveTo field 'SOP Number'
    TypeTo field 'SOP Number' , INV0005892
    MoveTo field 'Customer Number'
    TypeTo field 'Customer Number' , BLA001
    MoveTo field 'Customer Name'
    TypeTo field 'Customer Name' , '701819-Prasanna Kumar Nair'
    MoveTo field 'Primary Shipto Address Code'
    MoveTo field 'Document Date'
    TypeTo field 'Document Date' , 07102016
    MoveTo field 'Batch Number'
    TypeTo field 'Batch Number' , 'Oct16Sales'
    MoveTo field 'Location Code'
    TypeTo field 'Location Code', 'COC'
    MoveTo field 'Customer PO Number'
    MoveTo field 'Currency ID'
    TransLinePrepare scrollwin 'Line_Scroll'
    # Key 1:
    MoveTo line 1 scrollwin 'Line_Scroll' field 'Item Number'
    # Key 1: '', '0', '0', '0'
    TypeTo line 1 scrollwin 'Line_Scroll' field 'Item Number' , 'ATC88'
    # Key 1: '', '0', '0', '0'
    MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Dropship' # 'FALSE'
    # Key 1: '', '0', '0', '0'
    MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Unit Price'
    # Key 1: '', '0', '0', '0'
    TypeTo line 1 scrollwin 'Line_Scroll' field '(L) Unit Price' , '10330435'
    # Key 1: '', '0', '0', '0'
    MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Extended Price'

    NewActiveWin dictionary 'default' form 'SOP_Entry' window 'SOP_Entry'
    MoveTo field 'Save Button'
    ClickHit field 'Save Button'
    CloseWindow dictionary 'default' form 'SOP_Entry' window 'SOP_Entry'
    NewActiveWin dictionary 'default' form sheLL window sheLL

    ReplyDelete
    Replies
    1. When troubleshooting a macro, it is often instructive to attempt to enter the transaction causing the errors in the window manually to see if any errors occur that indicate what problem you may be encountering. Your macro at line 11 appears to be correct. The field is available on the form, the data you are importing looks correct and fits within the space constraints of the field. I do not have a GP 2013 VM on my laptop, so I can't be sure my testing is one hundred percent accurate, but your test should shed light on the problem.

      Delete