Importing to Dynamics GP without integration manager or SQL

Not all Dynamics GP users own integration manager nor does integration manager support every field, another way of importing data is to use a macro. This feature could be used to insert new records or make updates and it could be an unlimited amount of changes. It does become useful to update records for maintenance purposes or adding cards such as vendors or customers, but does not do well for recurring integrations. However, I don’t recommend this for recurring integrations where stability, performance, and error checking is required. These types of integrations are better served through integration manager, eConnect, or a custom-built integration through Scribe Software, Boomi, or a web service. I recommend starting small by creating a macro with only a 5-10 changes before attempting larger tasks. I have used this function for many Dynamics GP implementations over many of versions in the past 5-7 years so it’s worth learning especially if you don’t own integration manager and you’re an end-user of Dynamics GP rather than a developer or database administrator.

In this example, I’m going to update the descriptions for a set of general ledger accounts. Here is a list of steps I will explain in more detail later:
1. Create a macro to update your first two records which will create a text file.
2. Open the macro in a text editor and determine what code is needed to use in the macro.
3. Create an excel file of changes along with a named range.
4. Do the mail merge in Microsoft Word
5. Save the mail merge as new text file then add the first two lines of the original macro.
6. Run the macro from the same window you recorded it from.

Step 1 – Create a macro…

For my example I’m editing GL accounts so go to the account maintenance window, then start the macro from the tools>>macro>>record or Alt+F8. It will ask you to name the macro file and choose ok. Be prepared to enter two records of data without the use of any lookup windows. Now, go through these steps:
1. Enter in the first GL account you want to edit and choose the tab key.
2. Then edit the description field and choose ‘save’.
3. Enter in the next GL account you want to edit and choose the tab key.
4. Then edit the description field and choose ‘save’.
5. Stop your macro from tools>>macro>>stop

Step 2 – Open the macro in a text editor…

The unmodified macro I created in step 1 will look like the following, the first section is part of any macro specific to the window, the second section is the first account and the last section is the second account. The difficult part of any macro is figuring out what makes up the section you’ll use in the mail merge which is why I entered two accounts in the macro, and figuring out what fields you’ll use for the mail merge. You’ll use the second section or paragraph to use for your mail merge so highlight it and paste it into a MS Word document. Also, pay close attention to what your excel file will look like, notice my example will require each account segment in its own field.

# DEXVERSION=10.0.324.0 2 2
CheckActiveWin dictionary ‘default’ form ‘GL_Account_Maintenance’ window ‘GL_Account_Maintenance’

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1100’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Wells Fargo’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

ClickHit field ‘Save Button’
TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1101’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Canda-Bank of America’
MoveTo field ‘Save Button’
ClickHit field ‘Save Button’

Step 3 – Create an excel file of changes…

I am changing the descriptions of general ledger accounts so I enter the accounts and descriptions in excel, notice I split the segments into their own field through the use of functions (mid, left, right).

Seg1 Seg2 Seg3 Account Description
000 1100 00 000-1100-00 Cash-Wells Fargo
000 1101 00 000-1101-00 Cash-Canada-Bank of America

And I created a named range in excel and called in “Accounts” then saved the file

Step 4 – Do the mail merge…

Here’s the repeating part of the macro that should go into a new document.

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1100’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Wells Fargo’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

Start the macro by going to Mailings>>Start Mail Merge>>Step by Step Wizard, when you get to step 3 which is “Select Recipients” choose browse and go to the excel file.

For each field, you’ll highlight the field you want to replace, choose “More Items” highlight the field then choose insert, replacing the field on the left with the field on the right. Below is a screenshot of an almost completed insert merge.

Here’s what the document should look like before the merge

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘«Seg1»’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘«Seg2»’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ‘«Seg3»’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘«Description»’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

Step 5 – Save the mail merge as new text file…

During the merge when you get to step 6/6, choose “Edit individual letters” and choose to merge all records by choosing ok. Highlight everything by choosing Ctrl+A, copy it, then paste it into notepad, then open up your original macro and paste in the first two lines and save the file as a *.mac file.

Step 6 – Run the macro…

Go into the same window you recorded the macro from and with the cursor in the same position run the macro from the top of the window by choosing tools>>macro>>play and select the macro the merged macro. While your macro is running it will be very sensitive if you click on the mouse anywhere which will break it so I suggest you leave it alone, especially if it’s a long macro.

This entry was posted in Dynamics GP. Bookmark the permalink.

2 Responses to Importing to Dynamics GP without integration manager or SQL

  1. mario says:

    can i use this explanation for journal entries? i.e., bank postings? we have several bank postings on a daily basis and integration manager is not allowed… do you think it could work?

  2. Steve says:

    The only place I’ve found it doesn’t work is on scrolling windows so yes it should work. However, integration manager can be used to import journal entries or bank transactions. I would recommend using integration manager wherever possible since easier to use and more stable but not everyone owns. If you have frequent postings you might consider other more robust integration tools such as eConnect, Scribe, or boomi.com.

Leave a Reply

Your email address will not be published. Required fields are marked *