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
begin
  EXEC ("select * FROM " + @companyID + "..RM00101")
  fetch next from c_company into @company ID
end

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!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/