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).

Macros in Dynamics GP a Primer for Auditors and Compliance Officers

Definition - Macro:  A set of instructions, which tell a computer to complete a specific task or tasks, typically repetitive tasks, with a well defined data set. 

Dynamics GP (Great Plains) has a tool, which enables users to record and play back macros.  Once a macro is initially recorded, a Word “mail merge” can be run against the recorded macro to map/provide a data set to speed repetitive data entry.  The macro can then be “played” back in the system, and the macro will enter the merged data into Great Plains, as if it were a user.

The advantages of this are obvious.  First, a macro can perform tasks much more rapidly than a human.  Second, a macro can perform tasks with greater accuracy than a human.  Finally, there are instances where tasks must be run during off-peak hours, where a macro can substantially automate these tasks.

Important concepts

Macros make use of the same security structure Great Plains users utilize – a macro cannot run without Great Plains being open, and a user logged into the system.  If this user does not have access to a particular window in Great Plains, and this window is called by the macro, then the macro cannot run.

Macros must obey business logic and workflow – a macro cannot overcome business logic constraints or damage referential integrity in Great Plains (e.g. it cannot do anything a user cannot do).  For instance, if you want to import an invoice for a particular customer, the customer must first exist in Great Plains.  Similarly, if you would like to DELETE a customer from the system, and this customer has existing transactions, Great Plains would not allow the customer to be deleted (by a user or a macro).

Macros are persnickety – if differences in macro data require different workflow in the system a macro will periodically fail as the data is processed.  For instance, if a dialogue box appears when various conditions are met; such as, when there is not enough quantity on hand to cover an inventory transfer from the referenced location, and only some locations meet this criteria, the macro will be interrupted every time this dialogue box appears.  

Consequently, it is incumbent upon macro designers and testers to uncover these issues, and divide a macro into “bite size” pieces to be processed in like data groups.

Risk and Mitigating Controls

Macros can be roughly grouped into three separate categories, which have vastly different levels of risk associated with them.  First, macros can be used to import transactions, which must be reviewed, edited and posted by users.  Second, macros can be used to create or edit master records in the system (i.e. customers, vendors, items, etc.).  Third, macros can be used to automate processes in the system.

The first category, macros used to import transactions requiring further processing, is as low risk as everyday processing in Great Plains by users.  Transactions are entered into the system, as if a user typed them.  Reports are printed and reviewed by the appropriate business owners.  After review, these transactions are either edited as necessary, deleted or posted.  Any mistakes made during macro processing in category one would closely mirror mistakes made in normal daily processing in Great Plains.

The second category, macros which create or edit master records in the system, pose greater risk than category one macros, simply because there is no review step inherent in the process.  The data is changed immediately when the macro is run; therefore, it is incumbent upon the designers and testers of the macro to ensure a higher level of care and data integrity during the design and testing phase (prior to deployment).

The third category, automating a task, risk level depends greatly on the task being automated.  For instance, we currently use macros to substantially automate the tasks associated with the transaction imports into Great Plains from other systems. These activities typically take place in the middle of the night, for a number of practical reasons - system performance being the most important.

In these cases automation poses the least amount of risk, because it has been designed and tested, and only the data imported changes each night.  The biggest risk to a business is posed by changes which might deleteriously impact this integration (i.e. not all information would be interfaced).  These risks can be substantially mitigated by a reconciliation process between Great Plains and the external system.

A word about macros and risk.  A macro can enter with 100% accuracy, thousands of fields of data in several minutes.  Humans cannot.  One study conducted by UPS, quotes a statistic; during data entry, a typical user commits a keystroke error every 300 keystrokes.   In my opinion, the rewards of using macros properly, far outweigh the risks associated with their use.

We recently used macros to enter the variances noted during a physical inventory – I cannot easily estimate the amount of time saved by performing the process using macros, but if I had to hazard an educated guess, it would be on the order of days, not hours.  Additionally, the level of confidence I have in the accuracy of the data imported into the system is much greater than if it were hand typed by teams of people.

Finally, macros are a tool.  Like any tool, it is incumbent upon the user to use it wisely, and treat it with the respect it deserves.   Macro use should include a review process, which includes a QA step prior to deployment.  This approach reduces the likelihood of macro-related errors substantially.