Microsoft Dynamics GP, the next generation of end-user customization tools?

My new article is out on my Community column blog, In my humble opinion with The Dynamics GP Blogster. This time, I take a swipe at some significant improvements that could be added to the customization and integration tools like Modifier, Report Writer, and Integration Manager, just by switching out the programming environment and scripting languages, Visuals Basic for Applications and VBScript for Visual Studio Tools for Applications and PowerShell, respectively.

Far fetched? Not quite, go on and read my reasoning behind this, over at the Community's website.

Microsoft Dynamics GP, the next generation of end-user customization tools?

For more information on all the programming languages and environments, check the following links:

VBScript
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Visual Basic for Applications
   http://support.microsoft.com/kb/163435

Visual Studio Tools for Applications 2.0
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Scripting with Windows PowerShell
   http://technet.microsoft.com/en-us/scriptcenter/dd742419

Windows PowerShell Getting Started Guide
   http://msdn.microsoft.com/en-us/library/aa973757(v=vs.85).aspx

Modifier with VBA for Microsoft Dynamics GP 2010 Sample Applications
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9304

Microsoft Dynamics GP 2010 Tools Documentation: Integration Manager
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10955
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Customizations for the View Indented Bill (bmViewIndentedBillInquiry) window failing for GP 2010

In the past days I had been helping a Microsoft partner and good friend of mine in migrating some Modifier with VBA customizations from Microsoft Dynamics GP version 9.0 to version 2010.  In doing so, we came across an issue with one of the modified objects that needed to be converted: the Inventory module's View Indented Bill window.

View Indented Bill window - Original

The customization in question added a field to the View Indented Bill window to retrieve the quantity available for a given component item on a bill, as shown below:

View Indented Bill window - Modified

The rest of the customization included adding some VBA code to retrieve the quantity available for the component across all sites by using some simple ADO code. So far, good!

The error came into play when we attempted to print the Indented Bill Report, by clicking on the print button on the window.

Unhandled script exception error

After selecting the report type on the destination on the Report Destination window and clicking Ok to generate the actual report, Microsoft Dynamics GP was complaining about an Unhandled script exception an its inability to find script PrintTraverseCallback of form "[Not Found]".

As every good developer would know, troubleshooting this issue meant:

a) Testing that the error did not happen with the original form. Checked!
b) Testing that modifying the form did not corrupt the forms dictionary file, FORMS.DIC. Not Checked!
c) Testing that adding VBA code did not introduce the error, Not Checked!

In cases b) and c) even after rebuilding the forms dictionary and removing the VBA code, the error still occurred, but for case a) the system printed the report without presenting the Unhandled script exception error.

So being the, ehem!, well connected guy I am, I decided to call my good friend [Name excluded to protect the innocent] over at Microsoft. A test was performed to modify the same window and grant security to the modified version of the window, then run the report. The results were the same - Unhandled script exception. Now I could confirm that this was in effect a bug.

The bug also causes the BM Bill of Materials report would also not print the indented bill as expected.

BM Bill of Materials printed from original View Indented Bill window

Now, here is the report printed from the modified window.

BM Bill of Materials printed from the modified View Indented Bill window

Consequently, a problem report has been written up for this to be addressed in the near future - hopefully quick enough for the affected client.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

How to display an image at the User Interface level

One of the questions I often get is, can you dynamically display an image on a Microsoft Dynamics GP form or report. The answer is No, it's not possible. The reason for this is, Microsoft Dexterity, the tool used to developed your beloved Microsoft Dynamics GP, is only capable of displaying images stored within the dictionary itself. Since the picture control in Dexterity is binded to these pre-stored images, this obviously would indicate that there is no room for dynamic images to be added. In addition, the Dexterity picture control cannot be referenced or manipulated programatically as Dexterity does not treat this object like it does with any other fields.

One *exception* may be the adding these pictures via Modifier, but even so, this is not a dynamic manipulation of a picture control. In summary, not possible!

Of course, you can always resort to VBA. The objective of this post is to see how you can use VBA to aid in displaying images where needed. One typical application is displaying product images or employee pictures. Today's example will look at displaying product images. For this customization we will add a button to the Item Maintenance window, which in turn will call a VBA user form. Our user form contains a picture control which will display the product image once the window opens. We will use the image link on the Item Internet Information window to store the path to the actual image file.

NOTE: The Item Internet Information window can certainly display a picture, but it calls the default application associated with the extension of the file being loaded. Not practical if you want to avoid users damaging the actual image file.

1. Modify the Item Maintenance window to add a button control. To accomplish this, we will use Modifier to add the button control. Once the control has been added, we will return to Microsoft Dynamics GP to grant security to the modified window.



Item Maintenance window in Modifier
Note the button control added next to the other buttons (click image to enlarge)

Upon returning to Microsoft Dynamics GP, we grant security to the modified window and the result should be as follows:

Item Maintenance window

2. For the next step, we will add the window, the Item Number field, and the newly added Image button to Visual Basic for Applications. We can then proceed to create a simple user form with a picture control as shown below:

For this example, we have renamed the picture control object to ctrlImage and the user form to frmImage.

3. The following piece of code is added to the Image button' BeforeUserChanged event on the ItemMaintenance object:

Image_BeforeUserChanged()

' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub Image_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    If ItemNumber.Empty = False Then
        frmImage.Show
    Else
        MsgBox "Please select an Item Number to continue"
    End If
End Sub

The above code opens the frmImage user form if the ItemNumber field is not empty.

4. We can now add the code to load the image based on the path stored in the Internet Addresses table (dbo.SY01200) in the company database. For this, we will use the UserInfoGet object to open an ADO connection to the company database and use a recordset to retrieve the path stored for the specific item. In addition, we will use the LoadPicture() function to read the file containing the actual image into the picture control.

UserForm_Activate()

' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub UserForm_Activate()
    Dim oCn As New ADODB.Connection
    Dim oCmd As New ADODB.Command
    Dim oRst As New ADODB.Recordset, picRst As New ADODB.Recordset
    Dim oStream As New ADODB.Stream
    
    ' Establish connection to company database
    Set oCn = UserInfoGet.CreateADOConnection()
    With oCn
        .CursorLocation = adUseClient
        .DefaultDatabase = UserInfoGet.IntercompanyID
    End With
    
    If oCn.State = adStateOpen Then
        With oCmd
            .ActiveConnection = oCn
            .CommandType = adCmdText
                        
            ' Command to retrieve image path
            .CommandText = "SELECT INET4 FROM SY01200 WHERE Master_ID = '" & ItemMaintenance.ItemNumber & "' AND Master_Type = 'ITM'"
                    
            Set oRst = .Execute
                    
            If Not oRst.EOF Then
                ctrlImage.Picture = LoadPicture(oRst!INET4)
                ctrlImage.PictureSizeMode = fmPictureSizeModeStretch
                
            Else
                MsgBox "Could not find an image file for this item"
            End If
                    
            oRst.Close
        End With
    Else
        MsgBox "Could not connect to company database " & UserInfoGet.IntercompanyID, vbCritical, "Connection Error"
        Set oCn = Nothing
    End If
End Sub

5. Finally, some sample data... I will be using a picture of a processor, stored in the path indicated by the window field:

Internet Information window

Once we are done, we can test our customization by clicking on the Image button on the Item Maintenance window:

Item Maintenance window with Item Image user form window

It's great to see how VBA can help with everyday tasks that may otherwise seem complex. Hope you enjoyed the techniques used in this customization and provide your feedback. There are a few enhancements I can think of, but this should get you started in the right direction.

Downloads:

Item Maintenance and User Form package and Image zip file - Click here

Notes:

a) To test, change the path of the image on the item Internet Information card.
b) You may require a reference to Microsoft ActiveX Data Objects under the Visual Basic Editor to have this code execute properly.
c) This customization is compatible with Microsoft Dynamics GP versions 10.0 and 2010

Until next post!

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

Read More

Report Writer Week!

In this corner: David Musgrave

In this corner: Mark Polino

In case you have missed the heated debates - all within cordiality, though there was even a call for insanity - between David Musgrave and Mark Polino on Report Writer, I now join in to continue fueling the heat by declaring this the official Report Writer week!

To make this an even more attractive proposition for you the reader, The Dynamics GP Blogster is siding  (more like ganging up against Mark :-) ) with Developing for Dynamics GP to deliver some really cool Report Writer articles and to show some advanced techniques for developing reports... yes, with Report Writer.

Until next post!

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

Read More

Top 10 Microsoft Dynamics GP freebies

With the amount of money customers pay for software and implementations nowadays, it only makes sense that they would want to maximize their investment. What better than free (as in no strings attached!) software that works as intended. Because of this, I have set out to compile a list of the top 10 free add-ons -- in no particular order -- that I would drop into ANY Microsoft Dynamics GP implementation, no questions asked!

1) Support Debugging Tool. Ok, so I am biased! I have talked about this product in two Microsoft conferences and to outright show how biased I am, I am also personal friends with the product's author, David Musgrave. SDT is a collection of some of the most powerful administrative tools for Microsoft Dynamics GP currently available from anywhere, with rich features like Resource Information, Security Profiler, Dictionary Control, SQL Execute, Runtime Execute, XML Import/Export and the even more powerful Advance Debugger mode. So, where can you download this product? The product is available from Microsoft. If you are customer you MUST request it from your Microsoft Partner, and no, you shouldn't need to pay for it. The product is available via PartnerSource. For more information on SDT, visit the Support Debugging Tool Portal page over at Developing for Dynamics GP.

2) VersionPro. I really like when someone can identify a problem and produce a simple, yet powerful solution. Are you currently having problems keeping tabs of all the dictionary product versions throughout your entire organization? Version Pro is an administrative tool designed to ease the hassles of maintaining workstations on your Dynamics GP network. It keeps track of each product you have installed at a system level – and takes the next step in ensuring that each and every workstation on your network is on the same version for every product. VersionPro is available from Rockton Software -- yes, Mark Rockwell and his crew -- and can be downloaded here.

3) Password Expiration Notice. How many times have you been frustrated by passwords expiring with no prior notification to you or your system administrator? While I have addressed this issue from a SQL Server perspective in my article Enforcing Password Policy with Microsoft Dynamics GP, my friend and fellow blogger Michael Johnson, the MBS Guru takes it a step forward by implementing a Visual Studio Tools customization to check up on expiring passwords. You can download Michael's solution here.

4) Dynamics GP Productivity Pack Add-on. Do you work all day long with customer records? Won't you like to be able to capture information much faster and visit the typical inquiry and transaction entry screens just from the Customer Maintenance window? Matt Landis and the folks over at Landis Computer have the answer. You can download Matt's Productivity Pack Add-On here.

5) DexSense. Are you a die hard Dexterity developer? Wish you could have IntelliSense embedded in Dexterity to speed up your product development cycle? Just a few weeks aback I posted a call to vote on IntelliSense for Dexterity, but the answer came from the community. Tim Gordon at Alpine Limited in South Africa worked tirelessly to produce a solid first approach to IntelliSense with the product now being featured by MVP Leslie Vail in her new round of Dexterity training here in the United States. Now you can download Tim's DexSense here.

6) SpellCheck for Dynamics GP. Who knew right? Tired of misspelling every word typed into those pesky note fields? SpellCheck for Dynamics GP provides spell-checking on any Note, Comment, Text or String field in GP and 3rd party products. SpellCheck can be attached to any text entry field in the application. This add-on is an essential tool for environments that rely heavily on the accuracy of every single word stored in their database. You can download SpellCheck here.

7) Menus for Visual Studio Tools. This is another great free add-on that allows the developer in you to place your WinForms on a standard Microsoft Dynamics GP menu. This gem is also created by Microsoft's David Musgrave. For more information and links to download Menus for Visual Studio Tools click here.

8) Purchasing Account Required. Missing an expense account on a purchase order line item can be just a bit as annoying as having to figure out which line was missed. Michael Johnson puts out another piece of code that takes the thinking out of entering POs by making the Purchase Order account required in the process of entering the order. As if it wasn't enough, you can download both the VBA and the VST versions of the customization here.

9) SmartSort.Perhaps you’ve already discovered that getting your hands on information inside of Microsoft Dynamics GP may not always be an easy proposition, despite the range of tools available. EthoTech SmartSort speeds up your inquiries — and your data entry — by eliminating as many as 3 mouse clicks per lookup. Now you can automatically sort lookup information in almost every conceivable way! Quickly find the information you need by customizing your own view based on built-in advanced sorting options or SmartList favorites. AND… you can use SmartSort with over 40 Microsoft Dynamics GP lookup windows. Download SmartSort here.

10) SOP Batch Split. I couldn't have wrapped up the list without one of my own. Ever wish you could segregate invoices that have been transferred from orders in the same batch? What if all you want to do is change the batch name for a few sales transactions? Now you can do this with the SOP Batch Split tool. SOP Batch Split is a free add-on that uses Modifier with VBA and T-SQL stored procedures to perform a SOP batch transfer and is available here.

If you enjoyed this list and would like to know about other free products or suggest your own please add a comment to this post.

Until next post!

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

Read More

New Article on MSDynamicsWorld: "When It Comes to Customizations for Microsoft Dynamics GP, Which Tool Should You Rely On?"

"The term "customization" can mean different things to different people..."

My new article is out over at MSDynamicsWorld. This time, I go back to basics defining what is a customization and what tools are available to customize the Microsoft Dynamics GP user interface. This article is a good start if you are still trying to figure out your options for developing add-on solutions to Microsoft Dynamics GP. To read the full article, click here.

Until next post!

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

Read More

VBA - Opening a lookup window in expanded mode

Just recently, I came across a question from a partner asking if it was possible to have the scrolling window on a lookup form open in expanded mode, this is, showing information in detail. Traditionally, lookup windows in Microsoft Dynamics GP have always opened showing summary information, take for example the Item Lookup window shown below.


The partner wanted to see the window as shown below when first opened. Note that the scrolling window displays the additional fields added to the big line. This is accomplished by clicking on the switch arrow button that expands the scrolling window.



Of course, this is possible. The following VBA code can be used to open a Dexterity scrolling window in expanded mode by using pass-through Dexterity to set the switch value and run the expand window Dexterity statement.

Window_BeforeOpen()

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim Commands As String

    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")

    Commands = ""
    Commands = Commands & "if 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup  = 2 then "
    Commands = Commands & "   abort script;"
    Commands = Commands & "end if;"
    Commands = Commands & "{set shrink switch out}"
    Commands = Commands & "set 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup to 2;"
    Commands = Commands & "expand window IV_Item_Number_Scroll of form IV_Item_Number_Lookup, true;"

    ' Execute SanScript
    CompilerApp.CurrentProductID = 1493 ' SmartList
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & ""

    CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If

End Sub

NOTE: This script uses a method of calling Dexterity from VBA that is not supported by Microsoft.

Finally, this script can be enhanced by creating a generic function that would allow you to pass the name of the expansion button, the name of the form, main window, and scrolling window, which would then allow you to use it across almost all lookup windows throughout the system.

Until next post!

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

Read More

Microsoft Dynamics GP 2010 Technical Web Seminars

Prior to the Microsoft Dynamics GP Convergence 2010 event, I had been hard at work with my good friend David Musgrave, working on couple Live Meeting pre-recordings corresponding to the two sessions we co-presented at the Microsoft Dynamics GP Technical Conference 2009.

Now, these two Live Meetings have been made available to the partner community along with a number of other sessions presented at the Tech Conference, updated for Microsoft Dynamics GP 2010. Visit the following link to access all the presentations:

Microsoft Dynamics GP 2010 Technical Web Seminars (PartnerSource)

If you attended the Tech Conference, you may remember that David and I co-presented the following two sessions:

Microsoft Dynamics GP 2010: Customizing the Microsoft Dynamics GP 2010 Client with Visual Studio Tools, Dexterity and Modifier with VBA
(Microsoft Partner Learning Center)

Microsoft Dynamics GP 2010: Troubleshooting Your Developed Solution
(Microsoft Partner Learning Center)

Please be sure to check all these sessions out and update the surveys.

Until next post!

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

Read More

Microsoft Dynamics GP Architectural Foundations Series - Built to Fit

This is article 5 of 7 from the series Microsoft Dynamics GP Architectural Foundations Series - featuring Microsoft's Tim Brookins.

Tim's whitepaper was originally published in 1999 and it's reproduced here with his permission.

---

Built to Fit

Buyers in the midmarket require more than shrink-wrapped applications. It is imperative the system fit seamlessly into the customer’s overall business. The overall fit of the software is determined in two principal ways: customization and integration.

No matter how feature-rich a business management system, each customer will have unique needs not covered in the basic software. The Dynamics architecture must accommodate significant product customization as a basic part of the system. Additionally, the customized business management application must be integrated with all the other applications in the business.

The Dynamics architecture must also recognize that “Built to Fit” must not interfere with the “Built to Grow” philosophy. The process of customization and integration cannot modify the product in such a way that future upgrades are economically impossible. “Built to Fit” must work in a manner that allows cost-effective product upgrades.

Product Customization: What is VBA?

To understand the role of VBA in product customization, let’s begin with Visual Basic. Microsoft Visual Basic is one of the world’s most widely used rapid application development (RAD) environments. It is estimated over three million programmers know how to produce applications in the Visual Basic environment.

Visual Basic produces new, standalone applications. This does not meet the need for product customization. To enable customizations, we would need a special version of Visual Basic which, instead of producing new standalone applications, could “embed” itself inside an existing application. This special version of Visual Basic would allow customizations of the existing application without modifying source code.

It’s important the customizations are accomplished without modifying source code, because this greatly simplifies loading new versions of the business management system during upgrades. If the customization modifies source code, it becomes too expensive to reapply those changes to the new version. This defeats “Built to Grow.”

Microsoft Visual Basic for Applications (VBA) is designed to meet this need. Microsoft VBA is a special version of Visual Basic that can be embedded inside another application. VBA is included as part of the Office suite, and is the primary way end users customize Excel or Word.

In addition to the Microsoft Office products, Microsoft has allowed a select group of software vendors to license this technology. Some common names you might recognize are AutoDesk (AutoCad), Seagate Software (Crystal Reports), Adobe, Micrographix, Visio, and Cognos.

Visual Basic: Good or Evil?

At this point you may be confused. I spent significant time earlier in this document discussing Visual Basic’s weaknesses relative to C++. In this section, I am proudly proclaiming our support for this great Visual Basic technology, VBA. So is Visual Basic good or evil? The answer is both, and I intend to prove this is not just my opinion. Microsoft itself has come to the same conclusion.

The role of VBA in Microsoft Office

The diagram below depicts the role of VBA in the Office product. In the lower left, you see the block labeled “C++.” This demonstrates that Microsoft chose C++ as their base development environment for the Office products (Excel in this specific case). The block in the upper left communicates that the business logic and user interface of Excel is based on C++ code.

Note that the portion of the diagram to the left of the line represents the Microsoft “Internal” tools used to build Excel itself. To the right of the line represents the tools “External” users (i.e., end users) use to customize Excel.

This “Internal” vs. “External” differentiation is key to understanding the role of Visual Basic in Microsoft products. We can see Microsoft believed C++ was the best language to construct Excel. However, just because Microsoft chose C++ for its internal use doesn’t mean C++ was the best choice for external users. C++ is a powerful language, but it is complex and requires some intense training. As a RAD tool, Visual Basic is a much better environment for the public at large. Therefore, Microsoft chose to expose VBA as the primary customization tool for Office.

So is Visual Basic good or evil in this case? It’s clear Microsoft does not consider Visual Basic to be a good choice to build a complex commercial application. However, it is an excellent choice for allowing end users to easily customize the C++ built application.



The Role of Visual Basic in Dynamics

The diagram shown below should look very familiar. The role of Visual Basic in the Dynamics architecture is basically identical to the Office strategy.

The lower left quadrant indicates our choice of C++ as our primary development language. However, like Microsoft, just because we chose C++ as our internal language to build Dynamics doesn’t mean C++ is the best language for external users to customize Dynamics. For external purposes, we choose to support Visual Basic for Applications.

This is the perfect opportunity to deal with some other misconceptions other vendors may have about Dynamics. In some cases, you may hear: “Great Plains added VBA support to their latest release. They recognize Dexterity was a mistake, and VBA is just the first step of throwing out Dexterity and rewriting their whole application in Visual Basic.”

You should now have enough understanding of our (and Microsoft’s) strategies in this area to see how ludicrous this statement is. Because our strategies are identical, try substituting Excel into the same argument just for fun: “Microsoft added VBA support to the latest release of Excel. They recognize C++ was a mistake, and VBA is just the first step of throwing out C++ and rewriting the whole application (Excel) in Visual Basic.”

Of course, neither Microsoft nor Great Plains has any intention of moving away from C++ to Visual Basic as its primary development environment.

So is Visual Basic good or evil? As I promised, it’s both. We believe C++ is a great choice for building large, complex, high-performing commercial applications.We also believe Visual Basic is a great choice as a customization tool. When the roles are reversed, both products become “evil.” C++ would be a terrible choice for customization, and Visual Basic would be a terrible choice for internal development.



Will the real VBA please stand up?

Visual Basic has become a common customization tool for most business management software vendors. Several major business software vendors have released customization products based on some form of Visual Basic-like product. However, it’s imperative buyers understand these products vary widely in their level of Visual Basic support. There are three primary Visual Basic-like products in use today:

Visual Basic for Applications
VBA provides all the components needed to customize an application. The project window displays the objects you can customize. The property window displays object properties. You can leverage the MS Forms designer to add new windows, and then add ActiveX controls. Top this off with the full Visual Basic debugger and you get a powerful customization solution.














Visual Basic Script
Visual Basic Script is Microsoft’s solution for adding scripting to an application. You get the ability to write code (script), but there are few additional features. For example, there is no MS Forms support for adding windows, no ActiveX control palette, no project or property windows, and only limited debugging.















Mystic River SBL
SBL is a Visual Basic-clone product produced by a company called Mystic River Software. This product has a simple Visual Basic-like syntax in its scripting engine, but otherwise bears no resemblance to “real” Visual Basic. Programmers used to the modern Visual Basic environment won’t be familiar with this environment, since there is no forms support, a very basic editor, little or no debugging, no ActiveX control support, and limited COM support.

VBA: Separating the best from the rest
Now that we’ve reviewed the various products used in business management system customization, you are positioned to make an informed assessment of vendor capabilities in this area. However, be advised that vendors using other technologies will use the terms “Visual Basic,” “VBA,” or “VBA-compatible,” even if they are using proprietary, non-Microsoft products like SBL (which has no real Visual Basic code at all).

If a vendor tells you they are using VBA, be sure VBA is actually available in a shipping version of their software. Many financial management software vendors have licensed VBA from Microsoft (by paying a fee), but few are actually shipping software containing VBA. You may be wondering why Great Plains was the first vendor to ship VBA. There are two reasons for this. First, our “Built to Last” architecture allows us to quickly plug in new technologies like VBA without rewriting our business logic. Second, recall the previous section of this document entitled: “C++ vs. Visual Basic: Technology Support.” We discussed how C++ based applications are able to integrate new technologies quicker than products written in Visual Basic.

If you are wondering whether a vendor supports “real” VBA or not, simply ask for a demo. First open VBA inside Excel or Word (just hit Alt+F11 from inside either application and VBA will open). Compare the VBA environment from Office with the vendor’s version of VBA. You will be able to tell at a glance if the vendor’s VBA is the same as the real VBA in Office. Take a moment to look at the Dynamics VBA screenshot on the previous page: it looks just like VBA in Office because it is the very same application.

For an 18-page VBA white paper that describes Great Plains’ implementation of VBA in detail, we invite you to visit the Great Plains DynamicTools web page at www.greatplains.com/documents/downloads/vba_wp.pdf.stub. [Ed: Link Broken]

Built to Fit: Integration

Recall there were two components in the “Built to Fit” architectural value: customization and integration. We’ve covered customization in detail with our discussion of VBA, so we now turn our attention to integration.

Midmarket customers demand tight integration between their business management system and other parts of their business. For example, consider a business that has a custom-written order processing system that must integrate with the business management system’s receivables module.

To discuss our integration architecture, we’ll use a common framework that breaks down an application into three major components: User Interface, Business Logic, and Database.

Integrating at the database level

Integrating applications at the database level has become a very common approach. As the diagram shows, data is moved directly into the business management system’s database using integration standards such as Open Database Connectivity. This gives applications written in Access, Visual Basic, and PowerBuilder easy, standard ways of integrating at a data level. The Dynamics Import Utility also allows easy access to Dynamics tables:



There are some definite advantages to integrating at the database level (it’s typically very fast), but there are also some drawbacks. First, the end user must understand the structure of the database schema. There are usually hundreds of tables in a typical midmarket business management solution database. Compounding matters, a single “logical” document (like a receivables transaction) may need to be imported into several tables.

A second drawback to database level integration is once you have learned the datamodel and implemented your integration, the datamodel may change during a product upgrade. This is an impediment to product upgrade and goes against our “Built to Grow” philosophy.

Integration at the Business Logic level

To better support our “Built to Fit” and “Built to Grow” values, Great Plains introduces a second type of integration product: one that integrates at the business logic level. Called the Dynamics Integration Manager, this tool allows external systems to be quickly and easily integrated into the business management system.



Because the Integration Manager interacts with the business logic of the Dynamics application, no knowledge of the Dynamics database structure is required. Users simply point the Integration Manager to their external data via a graphical point and click interface, and the data is automatically imported into the business management system.

Any upgrade concerns are also eliminated with this product. Unlike database level integrations, which are susceptible to changes in table structure during a product upgrade, the Integration Manager never fails. When a new version of Dynamics is released, a new version of the Integration Manager is also released which automatically “knows” where to put the external data.

The Integration Manager contains a number of other invaluable features. For example, it is very common for external systems to use a different numbering scheme for identifiers like Account Numbers, Customer IDs, or Inventory Item IDs. The Dynamics Integration Manager can maintain translation tables and automatically translate the IDs during data imports.

Another useful feature is data synchronization. It is common for records like Customers to be tracked in more than one system. The Integration Manager allows these multiply defined resources to be synchronized. For example, if a customer address changes in an external system, that change can be automatically propagated into the Dynamics’ customer record.

In summary, the Dynamics Integration Manager is a key component of our “Built to Fit” architectural value. It allows nearly effortless integration of external data sources that will maintain their functionality across product upgrades.

---

You can download the original 18-page VBA white paper referenced in this article here.

In the next article Tim presents his "Conclusion".


Until next post!

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

Read More

VBA - Suppressing CTRL+Break or CTRL+C in VBA Customizations

VBA is by far one of the best customization tools available to Microsoft Dynamics GP developers and as such it is widely used across a number of production environments to deliver functionality that ranges in various degrees of complexity.

However, one of the issues with VBA -- at least until now -- is the fact that an end user can inadvertly press CTRL+Break (older keyboards) or CTRL+C and stop the execution of a script. Now imagine if that script is say, some code developed to calculate 401K contributions when payroll is ran... the results will certainly not be pretty!

With this in mind, we can use a USER32.DLL library funcion to disable user input when critical VBA code is required to be executed in a block. The following shows how to implement such code:

BlockInput function

Private Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As
Long

We can now use this function across any of our VBA customizations, as follows:

Sample Window_BeforeOpen code

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
  BlockInput True
  ' All the code you need to run here
  ...
  ' MAKE SURE TO RE-ENABLE THE INPUT
  BlockInput False
End Sub

There are several applications for this code, but whatever you do, don't forget to re-enable the user input, otherwise you will end up with one dead mouse and keyboard!

Until next post!

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

Read More

Parsing Long String fields in Extender for using with Report Writer

A few days aback I came across a Partner Forum question where the partner was attempting to add an Extender field to a Report Writer report, not without his share set of challenges.

In their infinite wisdom, the folks at eOne added a trigger to the rw_TableHeaderString Report Writer function which allows them to expose data to Report Writer without having to create alternate versions of a report in their application. In turn, with a few steps outlined in the Extender manual, users can invoke the rw_TableHeaderString as a user-defined function in a string calculated field to retrieve the piece of data needed from an Extender table by passing in the Window ID, the key fields, and the position of the field to retrieve on the Extender window. This is an example from such call:

Calculated Field: EXTENDER_KEY
Expression Type: Calculated
Result Type: String
Expression: STRIP( SOP_HDR_WORK.SOP Number )

Calculated Field: (C) AdditionalShippingInfo
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 )

This is all good! But here comes the issue... Extender Long String fields are 255 characters long and Report Writer string calculated fields support up to 80 characters. The partner tried to use the rw_ParseString Report Writer function to parse the Extender string in various lines as follows:

Calculated Field: (C) AdditionalShippingInfo_Line1
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 1)

Calculated Field: (C) AdditionalShippingInfo_Line2
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 2)

Of course, when the report was executed it threw an "Error in Equation" error as Report Writer does not support nesting of user-defined function scripts.

At this point, the only option available in order to be able to retrieve a long string and print it on the report is VBA, ADO, and a SQL Server view. The following is the process with references to articles that will help you with each step:

1. Create an Extender view of your data. You may start by reviewing Creating SQL Views of Extender Data over at Developing for Dynamics GP to get an understanding of this process. David Musgrave also outlines a sample view to get you started.

2. Create string calculated fields on your report that will be used to parse the Extender Long String field.

3. Add your report to VBA and add the string calculated fields created in step 2 to the VBA project. Also, add any key fields on the report needed to retrieve the data, i.e., SOP Number.

4. Use ADO to query the view for the information stored and store the data in the calculated fields. You may want to review Using ADO with VBA with Report Writer over at Developing for Dynamics GP for samples on the technique.

While the workaround might seem a bit lengthy, the results will speak for themselves, so don't give up on Report Writer just yet :).

Until next post!

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

Read More