Friday, October 31, 2008

Table Import article continues to gain traction

The folks at have picked up on my Table Import article and are running a header in their customary "From the Blogs" section.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Thursday, October 30, 2008

Developing for Dynamics GP - Weekly Summary

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft's new Statement of Direction for Dynamics GP will have on everyday product features. So let's get started!

Article 1: David first article -- Running SQL scripts against all GP company databases -- explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:

-- retrieves a list of customers from all companies
declare @companyID char(6)
declare c_company cursor for
select INTERID from SY01500

open c_company
fetch next from c_company into @companyID
while @@fetch_status = 0
EXEC ("select * FROM " + @companyID + "..RM00101")
fetch next from c_company into @company ID

close c_company
deallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word's Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, "Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.", and it's a shame because Report Writer, while very 'primitive' in it's behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don't forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool -- just kidding about the dangerous piece :-).

If you are not to engraned with the terminology, be sure to check Microsoft' ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: "Should I continue to develop in Dexterity?" That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don't get discouraged -- but don't fall asleep either -- if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Monday, October 27, 2008

Mark Polino on allocating payroll benefits to projects

I hope you have been following the series of interesting Project Allocation articles with Mark Polino, MVP. In this third installment, Mark explores the allocation of payroll benefits across projects in Project Accounting guiding you through with a full example with detailed steps and screenshots making sure you are clear on the process.

The following is the complete list of topics already covered by Mark on the subject of allocations:

· Project Accounting Benefit Allocations
· Project Accounting Unit Allocations
· Project Accounting Expense Allocations

Be sure to read these articles to enhance your knowledge on the Project Accounting series, but also to be familiar with some of the core features delivered with Feature Pack 1.
Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Friday, October 24, 2008

The often overlooked, yet powerful Table Import

Before Integration Manager, eConnect, Web Services, SnapShot, SQL Server Data Transformation Services (DTS), SQL Server Integration Services (SSIS), or any of the supercharged, techno-geek tool you can quickly think of, there was Table Import. Considered by many at the bottom of the food chain when it comes to integration tools, the fact is, it still holds its weight in today's XML-plagued world.


In past releases of Microsoft Dynamics GP, Table Import was an absolute best (and fast!) approach to import data, especially because tools like the ones mentioned above have never been able to cover the entire spectrum of Microsoft integrating products and third party applications available. Take for example Manufacturing or Field Service. While these products have long been around, there's little in the form of tools that can actually get data into their tables in a safe and validated way.

You may be thinking or asking, "well, how do I know what tables I need to import data into?". The fact is, Table Import does require an understanding of GP's table structures and their relations -- this includes all integrating solutions too! However, Microsoft has put great emphasis in providing Software Development Kits (SDKs) that outline these tables and their columns, and in particular what values are required to be passed in a record for it to be valid.

Table Import Overview

The following is an example that will import a few customer records using the sample records in the Customer.txt file under the Integration Manager samples directory.

1) Open Table Import. Go to Microsoft Dynamics GP > Tools > Integrate > Table Import. This will open the Table Import Definition window. One advantage of the tool is its ability to save import definitions. For this example, I will use CUSTOMER.

2) Select a Source File Format. Table Import supports files that have been formatted as comma-delimited or tab-delimited. The sample Customer file is a tab-delimited file.

3) Choose the Source File. Click on the folder button, then locate the file to import. The source file for this example can be located under:

C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples\Customer.txt

4) Select a Destination table. Click on the Ellipses button to open the Chose a Table window. For this example, we will select the RM Customer MSTR table. All columns in the table will be displayed in the scrolling window.

NOTE: Knowing your tables in any product will facilitate this process.

5) Map each source column in the file to the destination column in the table. Highlight each row, then click on the ellipses button next to the Source prompt on the scrolling window header to select a column from the source file. If you need to map a constant value, enter it in the Constant field on the window, then click Add.

NOTE: For the customer class, I will use the constant USA-ILMO-T1.

Before processing the import, the definition window will look like this:

6) Process the import. Lets go ahead and click on Import to bring in our records, choosing to Save when prompt to save our import definition.

Table Import will provide a status of the import while creating a rejection file. The rejection file contains the records that could not be processed and can be used to re-import the exceptions.

7) Run Check Links on the appropriate tables to build any missing records in related tables. This is quite critical, since most tables are inter related. The check links process will attempt to build those missing references.

The above illustration shows a check links executed after the import. In this case, the customer summary records and address record have been created.


Table Import can be an effective way for the end-user with some tech savvyness to get some data quickly into GP. Don't let the overwhealming amount of tools out there shy you away from using it, especially when these tools are not able to address parts of the application you are interested in integrating data into. Be cautious of the limitations -- data validation being one of them -- and arm yourself with all information possible before attempting any data import. Be sure to validate your data externally and apply common sense to ensure a safe import. Run check links and reconciliation where possible and if provided by the ISV or if importing into standard GP tables. But be sure to check the following resources.

NOTE: if importing data into third party tables, be sure to work closely with the product's ISV. They are better equipped to guide you and help you through the process.

Software Development Kit (SDK) Resources

Microsoft Dynamics GP

Microsoft Dynamics GP v10 Software Development Kit -- PartnerSource, CustomerSource
Microsoft Dynamics GP v9 Software Development Kit -- PartnerSource, CustomerSource
Microsoft Business Solutions - Greate Plains 8.0 SDK -- PartnerSource, CustomerSource

Field Service
Software Development Kit (SDK) for Field Service 8.0 -- PartnerSource, CustomerSource

Manufacturing Order Processing SDK for Great Plains 8.0 -- PartnerSource, CustomerSource

Other Useful Resources

Many of my fellow MVPs have also blogged at some point on table integrations:

David Musgrave, MSFT. David recently published an article with 14 different ways of obtaining table information in Dynamics GP. This article also includes links to other blogs were this topic has been discussed.

Victoria Yudin, MVP. Check her series of articles on GP Reports. Victoria provides complete details on some of the tricky flags that exists in some of the GP tables, to be considered when importing more sophisticated data, such as Sales Orders, Purchase Orders, etc.

Mark Polino, MVP. Mark has a posted a few downloads on his blog page. Take a look at his GP 10 Table Reference and GP 9 Table Reference Microsoft Excel files.

Former MVP, Richard Whaley continues to deliver some of the best books in the market on everything GP. In particular, you won't want to miss the Information Flow and Posting title. If you are interested, just click on the Accolade Publications link on the right of my blog.

[10/27/2008 - UPDATE] Tools Resources
This section has been added to include other tools that allow users to import/export data into GP, but that provide table information as well:

SnapShot - Click here to download SnapShot from David Musgrave's blog site. SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables.

Support Debugging Tool - I have written extensively in the past about this debugging tool. SDT has an XML import/export feature that allows users to export the data of a table into XML format and reimport it back. Click here to find all links to download SDT.

I will continue to update this article with more and more resources, so be sure to check regularly. However, feel free to submit your own resources by dropping a comment with a link to them.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Thursday, October 23, 2008

Filtering Third Pary Lookups with Dexterity

In the past days I had been struggling with a programming issue that I thought could be resolved very easily with standard cross-dictionary development techniques. In fact, I was convinced that most of what I needed could be found in past materials published by my dear friends David Musgrave and Mark Rockwell. In reading through the material and going through the recipe of steps to implement third-party lookup filtering, I realized the technique was using reject record statements to avoid displaying records in the scrolling window, and while I was able to implement it, performance quickly became an issue.

Business Situation

In principle, I am developing a new piece of code for one of my clients that would allow them to transfer Project Accounting contracts to Field Service contracts. However, I needed to limit the contracts in Field Service only to the contracts for the selected customer.


With the implementation of the technique outlined in Pushing the Limits III, the lookup would deliver the filtered results in over a minute! Not acceptable for me, not acceptable for the end-user.

Previously, I had filtered the Item Lookup window using a range where statement based on a few conditions my client needed to setup in Field Service, but this was much easier since the window resided in the DYNAMICS.DIC dictionary file. I was in fact sure that the technique outlined in Pushing the Limits III could be modified to use a range where statement, but was not sure how to accomplish it since the main issue was capturing a reference for the SVC_Contract_HDR (SVC00600) table buffer associated with the form, not any table buffer. To this point, I called my friend Mark -- David was sleeping at the time given the time difference -- and he pointed out that the client could use SmartFill to lookup any value typed in the Contract Number field associated to the lookup and that SmartFill would display its own lookup. Since my client owns SmartFill, this was certainly an option. However, this approach implied living with my inefficient lookup approach still, since I could not disable my lookup button.

Off I went to chat with David at COB and he pointed out that he had done this before. We started to brainstorm on the technique and he then realized that there were some challenges, but more than anything, realized that the Pushing the Limits material did not effectively address filtering third party lookups.

Giving David's nature, her came up with perhaps the ultimate article in Cross-Dictionary Third Party Lookups Filtering. Once again, David proves that the boundaries are just in your head and that the power of Dexterity as a customization tool truly relies on the ability to work around what can be many times considered as impossible.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Monday, October 20, 2008

Mark Polino on Unit Allocations in Project Accounting

In his series of Weekly Dynamic articles, fellow MVP Mark Polino continues to explore Project Allocations -- he first delivered an introductory overview on Project Allocations a couple of weeks aback. In this installment, Mark dives into how to allocate project costs based on units. He delivers important pointers on how to setup unit based allocations and clearly explains the options available as well as the pitfalls when configuring the associated cost categories.

Please stop by Mark's blog and let him know what you think about his article. Remember: these features have been delivered as part of Microsoft Dynamics GP Feature Pack 1. You must install these features from the DVD image.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Thursday, October 16, 2008

Developing for Dynamics GP - Weekly Summary

Ok, this may be funny, but trust me, if you have to keep up with David Musgrave, Mark Polino, Victoria Yudin, and all the other talented Dynamics GP bloggers out there you would soon understand why it's best to do a summary on these guys posts. So today, I will summarize David's post for the week.

Ever wonder why you receive an Illegal Address for field 'PowerUser' exception? Are you going balistic trying to find Dex.ini file after your upgrade to Dynamics GP 10.0? What are you going to do with your Dexterity customizations now that Micrsoft has changed up the Sales Transaction Entry interface buttons for the more sexy SOP Action button? Keep wondering why your DEXSQL.LOG file refers to an inexisting desSPRkmhBBCreh column in some table? Well, all answers are at Developing for Dynamics GP! Be sure to check David's posts and drop him a note on what you think about the articles.

For more information on some of the above topics please check my articles on Dex.ini and upgrading your VBA customizations to address the new SOP Action button.

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Microsoft Dynamics GP v10 Service Pack 3 now available

Microsoft has released (on time) Dynamics GP Service Pack 3. The direct link to the version 10.0 Service Pack 3 page is:
If you are upgrading from previous versions of Microsoft Dynamics GP, Business Solutions-Great Plains, or eEnterprise, be sure to check my new post on available upgrade paths. Also, please make sure to visit fellow MVP, Victoria Yudin's blog who is polling her readers on their intent for Service Pack 3.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC.

Wednesday, October 15, 2008

Understanding the rw_SelectAddrLine Address Line function in Microsoft Dynamics GP Report Writer

The Report Writer user-defined rw_SelectAddrLine function script is a very handy function that allows most Sales Order Processing documents to display addresses without those pesky blank lines in between say the first address line and the city, state, and zip code, especially when the second address line is empty.

For this explanation I will use the Bill To Address Lines on the Sales Blank Invoice Form, which is one of the most commonly modified reports throughout the system.

The current address lines on this report are predicated on 3 calculated fields (Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3). In turn, these calculated fields call a Dexterity script -- the rw_SelectAddrLine script -- that displays the address lines based on the parameters that are passed in. The implementation of this script is as follows:

function returns string rw_SelectAddrLine;
in integer nLineNum;
in string sLine1;
in string sLine2;
in string sLine3;
in string sLine4;
in string sLine5;
in string sLine6_City;
in string sLine6_State;
in string sLine6_PostalCode;
in string sLine7;

This script returns the address line for the specified position wherein blank lines are suppressed. Parameters are passed in the same order as the address lines to be printed. City/State/Postal Code are combined to form a single line. The line returned is selected from the parameter list by position but only non-blank lines are counted when calculating the position. This effectively suppresses the printing of blank address lines, which could be seen in earlier versions on Microsoft Dynamics GP.

Example: sLine1 through sLine5 are typically used for the contact person and fields Address 1, Address 2 and Address 3. sLine7 could be used for Country. But there are no restrictions on the values that sLine1-sLine5 and sLine7 actually contain. However, the line6 parameters of City/State/Postal Code are passed to the standard formatting method and thus should conform accordingly (or all left blank). If no line exists on the report for a given input line, pass an empty string for that parameter.

What this means to you as a user is, if you want to add, say the Country, you could potentially modify Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3, as follows:

Bill To Address Line 1 = FUNCTION_SCRIPT(rw_SelectAddrLine 1, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

Bill To Address Line 2 = FUNCTION_SCRIPT(rw_SelectAddrLine 2, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

Bill To Address Line 3 = FUNCTION_SCRIPT(rw_SelectAddrLine 3, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

In addition you will need to create a Bill To Address Line 4 to account for the Country. Let me explain. As is, the line addresses are only designed to account for an Address 1, Address 2, and City, State and Zip on a third line. If there is no Address 2 line, then only 2 lines get displayed. However, if you include Country, then in the cases where there are 2 address lines then the Country will not be displayed. With this said, Bill To Address Line 4 will look something like this:

Bill To Address Line 4 = FUNCTION_SCRIPT(rw_SelectAddrLine 4, "", RM_Customer_MSTR_ADDR.Address1, RM_Customer_MSTR.Address2, "", "", RM_Customer_Master_ADDR.City, RM_Customer_Master_ADDR.State, RM_Customer_Master_ADDR.Zip, RM_Customer_Master_ADDR.Country)

NOTE: The commas in this article are for readability sake, but in Report Writer these must not be included when calling a FUNCTION_SCRIPT.

Finally, to include the RM_Customer_Master_ADDR.Country field on the existing Bill To Address Line 1, Bill To Address Line 2, and Bill To Address Line 3, you will need to click on the last set of double quotes ("") then click the Remove button then add the Country field by selecting the Customer Master Address File from the Resources drop-down. Select the Country field then click on Add.

For more information on this and other Report Writer functions, please visit David Musgrave's blog and read up.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Microsoft Dynamics GP upgrade path

This is the time when many people start to consider and plan upgrades, on the verge of a new release. According to Rich Miller of Microsoft Dynamics GP Product Marketing, in a recent interview with MSDynamicsWorld, "more and more people are migrating since the release of [Dynamics GP 10.0] Feature Pack 1", and it will be no surprise that upgrade will increment with the release of Microsoft Dynamics GP 11. In addition, it has been one of those topics that frequently show up on the Microsoft Dynamics GP newsgroup.

With this said, I figured I would put together a list with the possible upgrade paths for each version of GP since Dynamics 6.o and eEnterprise 6.0.

You can update to Great Plains Release 8.0 from a previous release by using one of the following paths.

6.0.XX >> 7.5 >> 8.0
eEnterprise 6.0
Dynamics 6.0 for Microsoft SQL Server

7.0 >> 8.0
eEnterprise 7.0
Dynamics 7.0 for Microsoft SQL Server

7.5 >> 8.0
Great Plains 7.5

For a complete list of service packs for Microsoft Dynamics and eEnterprise 6.0,
click here.

For a complete list of service packs for Microsoft Great Plains 7.0 and 7.5,
click here.

If you are updating to Great Plains Release 8.0 from eEnterprise Release 6.0 or Dynamics Release 6.0 for Microsoft SQL Server, you must update to Release 8.0 in the following order.

• Update to Great Plains Release 7.5.
• Convert your data using Great Plains Release 7.5 Utilities.
• Update to Great Plains Release 8.0.

From Great Plains 8.0 Service Pack 5 on, follow these upgrade paths:

8.00g73 (Service Pack 5) >> 10.0 or any patch install
8.0 Service Pack 5 required

9.00.0281 >> 10.0 or any patch install
(Service Pack 2 - KB 923671 - English)
(Service Pack 2 -KB 927368 - Spanish Latin-American)
(Service Pack 2 - KB 929293 - French-Canadian)
(Canadian Payroll Year End - KB 928597)

9.00.0287 >> 10.0 with or without any patch install
(US Payroll Round 2 Tax Update - KB 930635)

9.00.0291 >> 10.0 with or without any patch install
(Hotfix 929445 - English)
(Hotfix 929446 - Spanish)
(Hotfix 929447 - French-Canadian)

9.00.0293 >> 10.0 with or without any patch install
(Hotfix 930941 - English)
(Hotfix 930942 - Spanish Latin-American)
(Hotfix 930943 - French-Canadian)

9.00.0294 >> 10.0 Service Pack 1 or later
(Hotfix 934049 - English)
(Hotfix 934050 - Spanish Latin-American)
(Hotfix 934051 - French-Canadian)
Click HERE to download the latest 10.0 patch release

9.00.0301 >> 10.0 Service Pack 1 or later
(Hotfix 936946 - English)
(Hotfix 936947 - Spanish Latin-American)
(Hotfix 938622 - French-Canadian)
Click HERE to download the latest 10.0 patch release

This 9.0 build also includes the 2007 Canadian and French-Canadian Payroll Tax Update

9.00.0310 >> 10.0 Service Pack 1 or later
(Service Pack 3 - KB 937795 - English)
(Service Pack 3 - KB 937796 - Spanish Latin American)
(Service Pack 3 - KB 937797 - French-Canadian)
Click HERE to download the latest 10.0 patch release

9.00.0314 >> 10.0 Service Pack 1 or later
(2007 US Payroll Year End Update - KB 941935)
(Hotfix 943807 - Spanish Latin-American)
(Hotfix 937798 - French-Canadian)
Click HERE to download the latest 10.0 patch release

9.00.0314 >> 10.0 2007 Canadian Payroll Year End Update or later KB 943980
(2007 Canadian Payroll Year End Update - KB 943978)
(Hotfix 943979 - French-Canadian)
Canadian Payroll Version = 9.00.0305

9.00.0327 >> 10.0 January Hotfix KB 947356 or later
(2008 US Payroll Round 2 Tax Update - KB 946605)
(Hotfix 946606 - Spanish Latin-American)
(Hotfix 946607 - French-Canadian)
Click HERE to download the latest 10.0 patch release

9.00.0337 >> 10.0 Service Pack 2 or later
(Hotfix KB 947362 - English)
(Hotfix KB 947364 - Spanish Latin-American)
(Hotfix KB 947363 - French-Canadian)
Click HERE to download the latest 10.0 patch release

NOTE: 10.0 Service Pack 2 for Spanish-Latin American and French-Canadian available soon!

9.00.0337 >> 10.0 2008 Canadian Payroll July Update KB 951101 or later
(2008 Canadian Payroll July Tax Update KB 951099 - English)
Click HERE to download the latest 10.0 patch release
Canadian Payroll Version = 9.00.0312

9.00.0352 >> 10.0 Service Pack 3 or later
(Service Pack 4 - KB 955672 - English)
(Service Pack 4 - KB 955673 - Spanish Latin-American)
(Service Pack 4 - KB 955674 - French-Canadian)

9.00.0358 >> 10.0 2008 Canadian Payroll Year End Compliance Update
(2008 US Payroll Year End Update - KB 957740)

Click HERE to download the latest Microsoft Dynamics GP 10.0 service pack.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

12/13/2008 - Included upgrade paths for 9.00.352 and 9.00.358 to 10.0. Added link to GP 10 update page.

01/20/2009 - Added links to eEnterprise 6.0 and Great Plains 7.0 and 7.5 service packs, courtesy of Victoria Yudin, MVP

Monday, October 13, 2008

Microsoft Convergence 2009 registrations opening soon

Microsoft Convergence 2009, to be held in New Orleans from March 10 to March 13, is Microsoft's premier customer focused event. Convergence has become the central point for customers to meet and learn about everything Microsoft Dynamics GP, from projects in the make, to interesting and fun keynotes, to action packed sessions, to future product releases.

Registrations will begin on December 3, 2008. Be sure to sign up for email reminders to take advantage of early registration discounts and secure a spot in what is already expected to be one of the best Convergence events yet.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Sunday, October 12, 2008

Using Menus for Visual Studio Tools from VBA - Calling native VBA forms

I was very intrigued by the Menus for Visual Studio Tools for Microsoft Dynamics GP (VSTM) application created by my friend David Musgrave. I studied the sample code provided in the PDF documentation submitted with the product and after a few hours of careful reading, it dawned on me that if I could expose the VSTM assembly to COM interop other developers, including VBA developers could also have their native VBA forms called as part of Dynamics GP menus. After a few attempts over a few days to register the assembly with COM by using the Microsoft Assembly Registration tool, I abandoned the effort due to an existing issue registering the Microsoft.Dexterity.Bridge assembly which is referenced within the VSTM assembly to be able to make calls to Dexterity.

This however, did not discourage me from seeking other alternatives. Going back to basics, I realized that the VSTM assembly methods are just references to function scripts implemented within the VSTM dexterity dictionary. In fact, these function scripts could be called via the Dynamics Continuum Integration Library as they had corresponding names to the methods exposed in the VSTM assembly.

After this "eureka" moment, I got on a conference call with David at 1:00 AM EST on Wednesday last week and asked him if VSTM could be leveraged from VBA to facilitate adding native VBA forms to the standard GP menus. After about 1 hour of brainstorming and debating the implementation, the deal was sealed! So off I went to work on a proof of concept.

My first attempt was very rudimentary as with all POCs, since I could only articulate so much of how VSTM really worked under the hood. However, one thing was still pretty clear: the registration of the menus had to occur during the lauch of GP. I thought I could use some sort of Toobar event to register my menus, but I was wrong. While I was able to register the event with the Toolbar's Window_AfterOpen event, the menus would not show. After a few exchanges with David, he pointed me in the right direction.

The Challenges

To understand the challenges, it is worth understanding how VSTM works. VSTM loads as part of the Dynamics GP runtime engine start up process when the Dynamics application is launched. In the process, VSTM checks for any command registrations that will in turn setup any menu items a developer has asked to register. Each menu item generates a tag that VSTM used to establish which item is being selected from a menu.

VSTM uses a hidden form called VSTM_Command_Form, which in turn implements an API window. The API window is used to process any menu item registration and deliver the call back item (the tag) when a menu option is selected. Since the window's Title property has been set to "~internal~" it invisible to Modifier and/or VBA. The first challenge then was to get this window exposed to be able to add it to VBA with its fields.

To expose the window, I used the Runtime Execute utility in the Support Debugging Tool, which allows the execution of sanScript code. The following is the implementation of that code in the Support Debugging Tool tool.

From there on I could use the window's Tools menu to add the window the Register button and Command ID fields to VBA.

The next obvious question was, with the hundreds of VBA forms and customizations out there today, how can I reference a form created in another product from the VSTM product? The obvious way would be to add a reference to the product needed from VSTM -- in fact that reference is needed. However, forms are not exposed via the references.

What was needed is a Module in the source product containing a Public Sub that in turn would display the form. It turns out that Modules are exposed via references and any Public Sub within the module can be invoked from another product's project.

The Implementation

Since I got David so excited about the idea of making VSTM workable from VBA, he set out on a race against the clock to develop the sample he provided in his VSTM application PDF guide in VBA. In fact, he was done with the code in little under 2 hours. In his words "not bad!". Please see David's article on Using Menus for Visual Studio Tools from VBA to gain some insight into the techniques used.

This was all good, because I could use his sample code as a base for delivering the form calls and show how to use VSTM in a practical way.

1) Open the Customization Maintenance window. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance.

2) Click here to download the package file for this project. Save onto your desktop.

NOTE: Menus for Visual Studio Tools is only available for version 10 of Microsoft Dynamics GP, hence this code will only work with Microsoft Dynamics GP v10.

3) Click on the Import button in the Customization Maintenance window and select the package file saved on your desktop. This will create 2 forms and add the necessary VBA code to open them. The package will also expose the Menus for Visual Studio Tools "~internal~" API window to VBA.

4) Compile the projects and exit the VBA editor

5) Exit GP, If prompted to save VBA projects, click on Yes.

6) Launch GP and go to Cards > Inventory, you will now see an entry at the bottom of the menu. When the entry is selected, two options will display showing sample forms that can be choosen. The first option, will open a form created within the VSTM product project. The second option will display a form created within the Microsoft_Dynamics_GP product project.

Menus for Visual Studio Tools Resources

Additional information on VSTM can be found at:

Menus for Visual Studio Tools for Microsoft Dynamics GP 10 at Developing for Dynamics GP.

Menus for Visual Studio Tools - The wait is over at Developing for Dynamics GP.

First Look at Menus for Visual Studio Tools at The Dynamics GP Blogster

Final Notes

Please note that Menus for Visual Studio Tools for Microsoft Dynamics GP 10.0 is only planned for release with version 10.0 as the plan is to have menu support built into Visual Studio Tools for the next version of Microsoft Dynamics GP.

This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Once again, it is demonstrated that VBA can be a very powerful tool in developing portable customizations that take advantage of all the technology currently available around Microsoft Dynamics GP and while no one can guarantee some of these technologies will prevail, at least you know that I will be on the look to find a way to move forward.


All my thanks to David Musgrave who has been a key contributor, mentor, and inspiration for a good number of my technical articles. Did I ever mention that he also likes to decorate cakes? This may well be a topic for a fun post.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

50 Microsoft Dynamics GP tips in 50 minutes!

Ok, you may think this is some sort of new Vegas casino gambling gimmick. Not far from reality! Fellow MVP Mark Polino is about to prove to the world that he can hand out 50 Microsoft Dynamics GP tips in 50 minutes at I.B.I.S's iSight customer conference to be held in Atlanta, GA on Wednesday, October 15, 2008; and Charlotte, NC on Thursday, October 16, 2008. Whether you are an I.B.I.S. customer or not, you will not want to miss this electrifying display of knowledge coming at you at bullet speed from one of the best darn Dynamics GP consultants in the United States -- and let me tell you, on an individual basis there aren't too many of us :-).

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC.

Scott Stephenson on Providing product feedback with Microsoft Connect

How many times have you wished a particular feature to be incorporated into the Dynamics GP application? How many times have you wished you could have some input on how a module should work or how it could be changed to work more efficiently? My dear friend Scott Stephenson, team manager for the Microsoft Dynamics GP Developer Support team in the US joins the Developing for Dynamics GP blog with a compelling article on providing product feedback.

Microsoft Connect was created with this purpose and has been a vehicle for users and developers alike to have their voices heard with Microsoft product mangers and developers. If you haven't done so, please visit Connect and register to begin entering your suggestions today.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Friday, October 10, 2008

David Musgrave on Table Auto Stored Procedures, Retrieving Table Information and VST LineFill Events

David was busy overnight (daytime for him) releasing three great articles:

a) The first one introduces the concept of Dexterity table Auto Stored procedures and how they are used to perform table operations.

b) His second article provides 13 known techniques for finding and retrieving tables and fields information in Microsoft Dynamics GP, exploring simple options as the GP's built-in Resource Descriptions all the way to his new Support Debugging Tool's Resource Explorer. David also provides a good number of other sources from fellow bloggers.

c) Finally, David explains the VST LineFill events by taking a look at how Dexterity fills scrolling windows and why the VST LineFill events weren't effective in accessing Dexterity table buffers.

At David's publishing rate, I will have to start a weekly summary section solely dedicated to compile his work as I am finding it hard to keep up with him. Please stop by David's blog site and let him know what you think about these great articles. The only reward we get from blogging -- besides sharing our knowledge -- is to know that you are following and enjoying these articles as much as we enjoy writing them

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC.

Wednesday, October 8, 2008

Dynamics GP Support Schedule Timeline

Fellow MVP, Victoria Yudin has pur together a great article and a support matrix with links to Microsoft's CustomerSource showing the scheduled timeline for each Dynamics GP product version support. Did you know that if you are still running GP 6.0 your support expired some 4 years ago? This means that you will also pay additional fees to become compliant again. If you have a need to upgrade (you think?) and would like to get started visit my site or contact me for more information.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

New Article on MSDynamicsWorld: "Implementation Champions" by Mark Polino

In his new MSDynamicsWorld article, fellow MVP Mark Polino explores the importance of identifying a project champion during the Dynamics GP implementation phase. In addition, he provides key pointers on how to spot one and retain that person during the implementation.

Being a project manager myself, I can say that a project champion is generally that person from within the client's ranks who will drive a project to completion and will make use of his/her skills and charisma to get the entire organization motivated towards the end goal. Think of the project champion as a cheerleader, the person who will advocate the benefits of the project.

For those of you project champions, you know who you are, you know you are eager to come out and "show off" a little while ensuring everyone in your organization that GP was the right choice. Come forward and give us a hand!

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Tuesday, October 7, 2008

Implementing PayPal exports with EFT for Payables

Recently I had a chance to look into a post on the Microsoft Dynamics GP newsgroup where the consultant required the ability to upload hundreds of payments into PayPal as a business process using the EFT for Payables module.

At first, I thought the issue was more related to the interfacing abilities with PayPal, but soon discovered this was not API-related -- PayPal also provides an API that can be called via web services.

It turns out, PayPal allows its customers, in its simplest form, to upload a mass payment tab-delimited text file containing the email of the payee or recipient, the amount to be paid, currency, a unique identifier for the recipient (which can easily be the vendor ID from GP), and a note or transaction description. For more information on PayPal's formatted file click here.

Knowing this, the first limitation with EFT Payables is its ability to generate tab-delimited files. However, it does provide the ability to create fixed length files and XML files which can certainly be converted to tab-delimited with either Microsoft Excel. Nonetheless, I decided to explore what it would take to implement PayPal and here are the steps I came up with.

Setting Up the EFT Payables Options for PayPal

NOTE: the following steps are for Microsoft Dynamics GP v10. However, they can easily be adapted for previous releases.

1) Setup a checkbook ID for PayPal. Go to Cards > Financial > Checkbook and enter minimal information as shown below.

2) Click on the EFT Bank button to add EFT information for PayPal. Choose Other 1 from the Bank Country/Region drop-down. Also, set the ISO Currency Code field to one of the supported PayPal currency codes and preferably that of your functional currency. For a list of PayPal supported currencies click here.

NOTE: PayPal only supports payments made in one currency code per file.

Leave all other fields empty.

3) Click on the Payables Options button to open the Checkbook EFT Payables Options window to setup the file format and the default file output options for the EFT file. Set the EFT Payment Numbers radio group to Use EFT Numbers -- this information is irrelevant to PayPal, but necessary to configure EFT; setup the Default Output Files as needed.

In the File Format radio group select Single Format and type PAYPAL in the field. Add the new format when prompted. This will open the EFT File Format Maintenance window. You will add 5 fields at the Detail line type level as shown in the following illustration.

NOTE: I used a flat file format, but XML can also be used as a format defining the tags and mapping the data in a similar manner.

For each field map the following data:

* Email Address maps to Address Electronic Funds Transfer Master.EFT Bank Account
* Amount maps to PM Paid Transaction History File.Document Amount
* Currency ID maps to Checkbook Transaction Electronic Funds Transfer.ISO Currency Code
* Vendor ID maps to PM Paid Transaction History File.Vendor ID
* Description maps to PM Paid Transaction History File.Transaction Description

4) Click on the Save button to continue. Click here to download a copy of the PAYPAL file format that can be used to be imported into EFT File Format Maintenance window.

5) On the Checkbook Payables Options window, click OK to save and exit the window.

6) On the Checkbook EFT Bank Maintenance window, click OK to save and exit the window.

7) On the Checkbook Maintenance window, click on Save to save the changes.

Setting Up the Vendor Card

1) Open the Vendor Maintenance window. Go to Cards > Purchasing > Vendor and select a vendor ID.

2) Click on the Address button, then choose an address ID (typically the one used for remittances).

3) Click on the EFT Bank button to open the Vendor EFT Bank Maintenance window. Choose Other 1 from the Bank Country/Region drop-down list. Since PayPal uses emails instead of bank accounts, the Bank Account Number field will be replaced with the Vendor Email address.

NOTE: Internet Address information is not exposed to EFT; hence the email address associated to the Vendor Address cannot be used.

This should complete the setup! Save all information and close all windows.

Testing it All!

Use the EFT Prenotes window to generate the prenotes. This will pick up all transactions created in the past and will allow you to get a glimpse at the new PayPal file. Use Microsoft Excel to convert the generated payments file to a tab-delimited file, required by PayPal.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Monday, October 6, 2008

Microsoft Dynamics GP Password Implementation

David Musgrave answers the most common questions about Microsoft Dynamics GP user password implementation by exploring a number of topics including, but not limited to the history of passwords in GP, password encryption, password policy enforcement, and Windows authentication. Be sure to check this article, because this is a very frequent topic that comes up in various newsgroups and discussion boards and the answers are as varied depending who you ask.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Thursday, October 2, 2008

New Article on MSDynamicsWorld - "When Cash Is King: 8 Strategies for Using Microsoft Dynamics GP to Wring Extra Money from Regular Transactions"

Hi folks! After a long writing hiatus from, my new article has finally made it to the headlines. In this edition, I discuss 8 comprehensive strategies to improve your purchasing to disbursement business process and match the Microsoft Dynamics GP application components required to effectively support the proposed strategies. As always, please let me know your thoughts and/or let me know what you would like to see posted on this site.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Wednesday, October 1, 2008

Integrating Customer Maintenance and Letter Writing with Microsoft Outlook

I love a challenge when I see one, but more important, I love to share my knowledge when I get to the bottom of it all. On the Microsoft Dynamics GP Newsgroup a user expressed her interest in seeing more integration between GP's letter writing capabilities and Microsoft Outlook as it relates to Customers and Vendors. While one could argue that you could use already built-in email submission capabilities in Microsoft Word to send documents from there, the fact remains, that all the customer internet information would be ignored, forcing the user to switch between Outlook and GP to retrieve and manually enter email contact information into Outlook and perhaps even having to click through mounts of directories for files just previously saved with the Letter Writing Assistant.


1) With the Customer Maintenance window open, press CTRL+F10 to open Modifier.

2) Move the INet1 and INet2 fields to the window area on the Customer Maintenance window. Do not be concerned about the placement of the field since later on they will be moved back to their original positions.

RM_Customer_Maintenance window as seen in Modifier

NOTE: This customization re-uses the INet1 and INet2 fields available to support Small Business Manager functionality.

3) Save the changes and return to GP (File > Microsoft Dynamics GP).

4) Grant security to the modified window:

· In v10: Microsoft Dynamics GP > Tools > Setup > System > Alternate/Modified Forms and Reports.

· In v9: Use Advanced Security or the Security options to grant security to the modified window.

5) Open the Customer Maintenance window and add the form to Visual Basic. Press CTRL+F11 on your keyboard.

6) Add fields to Visual Basic. Press Shift+F11 to activate the visual cue. Click on the following fields: INet1, INet2, Customer Number, Write Letters button

7) Open the Visual Basic editor, locate the Microsoft Dynamics GP Objects in the Project Explorer pane, and double-click on the CustomerMaintenance (Window) object.

8) Download and paste the following code in the Code Editor.

WARNING: This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

9) From the menu bar, choose Tools | References. Mark the following references: Dynamics Continuum Integration LibraryMicrosoft Outlook 1x.0 Object Library

Click Ok to continue.

10) From the Debug menu, choose Compile Microsoft_Dynamics_GP.

11) Save the project and close the Visual Basic Editor.

12) From the Customer Maintenance window’s Tools menu, select Customize, then choose Modify Current Window, or press CTRL+F10 on your keyboard.

13) Move the INet1 and INet2 fields back to their original position.

14) Save changes and return to GP.

15) Open the Customer Maintenance window and click on the Write Letters button drop-down list. You will now see the new "Send an Email" option. You will need to select a customer first to continue.

By default, the customization will open the directory specified for Letters in the DEX.INI file.

Techniques Demonstrated

1. Working with Microsoft Outlook via COM Automation. There are six main steps to sending a Microsoft Outlook mail message by using Automation, as follows:

a) Initialize the Outlook session
b) Create a new message
c) Add the recipients (To, CC, and BCC) and resolve their names
d) Set valid properties, such as the Subject, Body, and Importance
e) Add attachments (if any)
f) Display/Send the message.

Outlook Automation has remained virtually unchanged since Outlook 2000, but you can refer to Microsoft KB article 220595 for more information on the subject.

2. Pass-thru sanScript implemented with Dynamics Continuum Integration Library, to add an item to the letter writing Button Drop-Down List control (for more information on how to dynamically populate a list control read David Musgrave's article on the subject).

Pass-thru sanScript is also used to call the RW_GetInternetInfo function to retrieve customer email address and to read the Letters Path key from DEX.INI file which will in turn serve as a default directory for the attachment dialog.

3. Use of the Common Dialog Box API to return the path to the email attachment. Please refer to my previous article on Using the Win32 Common Dialog Box API to return a file path to a Dynamics GP modified form field as it explains in great detail the use of this API.


You can always download the package files and import using the Customization Maintenance window under Microsoft Dynamics GP > Tools > Customize > Customization Maintenance. Don't forget to include your references as indicated in step 9 above.

Dynamics GP 9.0 package file - Click here
Dynamics GP 10.0 package file - Click here

Once again, I have demonstrated the power of Visual Basic for Applications in Microsoft Dynamics GP and how you can achieve high degrees of integration with the inner works of the system itself and with other desktop applications like Outlook, and even the Windows operating system elements. As usual, please drop me a note if you have any comments on this article or if you would like to see a particular topic discussed on this site.


When choosing references to the Microsoft Outlook Object Library, consider the active version of Outlook you are currently using. The following is a summary of Object Library versions related to their corresponding version of Microsoft Outlook.

Microsoft Outlook 2007 --- Microsoft Outlook 12.0 Object Library
Microsoft Outlook 2003 --- Microsoft Outlook 11.0 Object Library
Microsoft Outlook XP --- Microsoft Outlook 10.0 Object Library

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC