Extracting data from Microsoft Dynamics GP company databases using SQL Server FOR XML and XMLNAMESPACES

I truly love what I do. Really!

My job takes me just about everywhere around this great country of ours and beyond its borders in the quest of helping clients and partners get the best out of their Microsoft Dynamics GP application and data.

In reference to the latter - data - I had been asked recently by a client how they could produce XML formatted data from their Microsoft Dynamics GP databases to be consumed by some web services applications they had developed. Some conditions around this request:

• The client did not want to implement eConnect Requester, though I have to admit this would have been a slam dunk with MSMQ queues. 
• The XML documents needed to be rather available and changeable very quickly to serve other needs.
• No additional investments in third party products, middlewares or the likes could be suggested since budget was pretty tight.

In other words, no eConnect, no third party products, and lots of flexibility...

The answer could only be one: use the powerful XML capabilities of T-SQL to get data out in the format required by the client.

Some theory

There's a powerful option when querying data from SQL Server for use with third party applications and/or web services. You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions.

The FOR XML clause has some great benefits:

• It allows a SQL Server developer of Microsoft Dynamics GP consultant to write critical pieces of integration architecture without having to learn the destination system's schema.
• Additional table columns - pieces of data, if you will - can be added to the results with relative ease.
• It's an efficient way to process data and reduces the number of components that must be developed.
• It can be formatted to match target schemas in order to simplify mapping and/or middleware configuration.

There are a number of options related to using the FOR XML clause in SQL Server. The most appropriate way I have found - best practice, if you will - is to declare your own namespace using the WITH XMLNAMESPACES clause and to format the XML specifically as expected with the PATH mode.

Keep in mind that SQL Server has an AUTO mode which allows it to automatically format the XML document for you, relinquishing some control from you the developer or consultant.

For more information on SQL Server FOR XML and the WITH XMLNAMESPACES clauses, please take a look at SQL Server Books Online:

MSDN - FOR XML clause - http://msdn.microsoft.com/en-us/library/ms178107.aspx
MSDN - WITH XMLNAMESPACES clause - http://msdn.microsoft.com/en-us/library/ms177400.aspx

Now a practical application...

This is a simple example on how to implement all of it together. Let's take the case of a Customer with multiple addresses. The following query should produce XML data with our customer master (RM00101) and address master information (RM00102).

CustomerExtract.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

WITH XMLNAMESPACES('http://sql.customer.extract' as "ce0")
SELECT NULL
    , (  SELECT RM00101.CUSTNMBR AS [ce0:CustomerNumber]
   ,RM00101.CUSTNAME AS [ce0:CustomerName]
   ,RM00101.CHEKBKID AS [ce0:CheckbookID]
   , ( SELECT RM00102.ADRSCODE AS [ce0:AddressCode]
    , RM00102.ADDRESS1 AS [ce0:Address1]
    , RM00102.ADDRESS2 AS [ce0:Address2]
    , RM00102.CITY AS [ce0:City]
    , RM00102.[STATE] AS [ce0:State]
    , RM00102.ZIP AS [ce0:Zipcode]
    FROM RM00102 
    WHERE RM00102.CUSTNMBR = RM00101.CUSTNMBR
    FOR XML PATH('ce0:Addresses'), TYPE)
   FROM RM00101
  FOR XML PATH('ce0:Customer'), TYPE)
 FOR XML PATH ('ce0:CustomerExtract'), TYPE

The results are pretty straight forward:

FOR XML output (formatted for display purposes only)

Nothing but XML greatness!

The above query could have been encapsulated in a stored procedure with a parameter for customer number, which could have driven the results displayed. As you can tell, getting the data you need for any destination, will depend on you specific requirements, but it's doable with the power of SQL Server and T-SQL.

Until next post!

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

Read More

Mark Polino shows some love for Microsoft Dynamics GP integration tools

Like Mark, a lot of the work I do centers around large and complex systems integration with Microsoft Dynamics GP. It's good to see that Mark addresses some of the glories, pains, and considerations for systems selection and integration to Microsoft Dynamics GP in his new article Dynamics GP Integration Flexibility and Frustration over at his Community column Mad Mark, The GP Road Warrior. In addition, he points out some of the integration tools and methods he's used to get these to happen.

And speaking of integrations...

Please come join me at the GPUG Summit 2011 for the Optional Academy Training Classes. I will be delivering a full day training class on Data Migration and Integration Techniques where I will be highlighting some of the issues I have faced throughout my career when migrating and integrating data from and into GP.

My training curriculum is almost locked down, but if you are attending this class and want to see a particular topic being discussed please do so by adding a comment at the bottom of this post.

Until next post!

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

Read More

How to schedule Dynamics GP to automatically log in and run an Integration Manager integration - revisited

With my newly found stride, thanks mostly to Microsoft Dynamics Convergence Atlanta 2011 and the number of interesting questions I fielded during the event, I thought it was time to revisit the issue of scheduling Integration Manager integrations as part of our deep dive session, DDGP03 Microsoft Dynamics GP Customization & Integration Tools Review - see Microsoft Convergence Atlanta 2011: Day 3 for more on what transpired that day.

If you have been a follower of my blog for sometime now, you will recall that back in January of 2009 (ok, I don't expect you to remember this) I posted an article on the subject - see How to schedule Dynamics GP to automatically log in and run an Integration Manager integration. In short, the article looked at using the Windows Task Scheduler to launch a batch file, which in turn would launch Microsoft Dynamics GP, which in turn would use a macro to log into the system, then run a previously created shortcut to run the integration...in summary, a very complicated set of steps if you ask me. This method also presented a problem for users running Windows Server 2008 and above, since the Windows Task Scheduler no longer supports desktop interaction, which is required by the macro system to execute a macro.

Fast forwarding one year and almost half later, and I still get this question regularly: How can I schedule an Integration Manager integration?

Here is my new secret...

I use a (non-Microsoft) product called System Scheduler Professional by Splinterware. It turns out that System Scheduler has no ties to the Microsoft Windows Task Scheduler - none whatsoever! In turn, it allows the product to do some really cool things like running as a service application which is just exactly what's needed if you are going to schedule stuff when locking down your computer before leaving for the day is a must.

System Scheduler - Event Setup

System Scheduler allows you to setup an event or a list of events to be executed and even more cool, it allows you to send key sequences to an application with a complete help file that illustrates what's possible. In older systems (like Windows XP) you can even set it up to unlock the computer and lock it as part of the list of events. Due to restrictions in the way Windows 7 is built this is not possible, but still, not needed if you consider that System Scheduler can run as a service under the LocalSystem account or a named account.

System Scheduler - Advanced Options

You can download the product with the link below:

System Scheduler Professional by Splinterware.

The Professional version allows you to try it out for 30 days before you need to register it. It is really very simple to use and if you had had any exposure to Windows Task Scheduler then this should be a breeze. Now, instead of the complex steps mentioned in the previous article, you can schedule Microsoft Dynamics GP to launch with the typical parameters and use a macro to log you in (if not using Microsoft Dynamics GP 2010 to remember the user and company for you). Once GP is up and running, you can use a the SendKeys function to lunch IM (or IMRun) and execute the integration of your choice.

As it turns out, you can also have a multi-event schedule that first launches GP then launches IM with the integration as a parameter. Please try the tool out and let me know what you think.

Until next post!

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

Read More

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Part 2 of 2 - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.

---

In the previous installment, I explained the technique that I otherwise use to relate source systems records with imported records in Microsoft Dynamics GP for which a Use Default field rule has been set for the key field. This article demonstrates the key event scripts needed to obtain the record.

Before Integration event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Secure a connection against the company database we will be running
' the integration against.

Const adUseClient = 3

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient

GPConnection.Open(oCn)

' Make sure the connection is valid
If (oCn.Status = 1) Then
   ' Setup global connection variables
   SetVariable "gblConn", oCn   
   SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
Else
 CancelIntegration
End If

The Before Integration event script will allow us to secure a connection against the company database we will be running our integration against. By opening the connection in this event script, we will avoid having to open a connection for each transaction being integrated, further on, creating any loss of performance in the process. It is important to highlight that we need to save the successful connection to a global variable, to be able to use it in other event scripts. In this case, we will assign the oCn connection variable to a global variable, gblConn, using the SetVariable statement.

Once Integration Manager has integrated the document, we will use the After Document event script to retrieve the record integrated. At this stage, we are assuming that the mapping of source fields to destination fields provisions a user-defined field or description field for the source key field. In the past, I have also used note fields to store these key fields when it has not been feasible to use a standard Microsoft Dynamics GP field.

Note: the After Document event script will only execute upon success of the document being integrated. If Integration Manager is unsuccessful integrating the document, the Document Error script will execute instead. This event can be used to report failure to the source system, which may facilitate new attempts to integrate from the source system by reporting different event statuses.

After Document event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the assigned Sales Transaction number
Set oCmd = CreateObject("ADODB.Command")
With oCmd
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdText

 .CommandText = "SELECT SOPNUMBE FROM SOP10106 WHERE USERDEF05 = '" & SourceFields("mySourceQry.KeyField") & "'"
  Set oRst = .Execute

 If Not oRst.EOF Then
                SopNumber = oRst!SOPNUMBE
 End If
 oRst.Close
End With  

' From here on you can open a connection to your source system and update the 
' some status flag and the column provisioned to track the GP document number

I hope you find this technique useful. Of course, this is a technique I have been using over the years. I would like to find out from you what methods you have used to accomplish the same.

Until next post!

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

Read More

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 1

Part 1 of 2 - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.

---

One of the things often frustrating for Integration Manager users and developers alike is knowing what document number will be assigned by GP to an integrating transaction when the document number property in the mapping is set to Use Default. Typically, the developer will need this key piece of data, because the integration will perform some write back to a source system to mark a specific record as processed, but also passing back the document number that was assigned in GP.



The UseDefault rule is shown for a Sales Transaction mapping

But, how to know the journal number, or the order number, invoice number, or voucher number that GP has assigned to a submitted document after the record has been integrated?

Let's start by saying that I have seen techniques that read the next document number from the Microsoft Dynamics GP setup tables, then assign that document number to the key field in Integration Manager for the record being integrated. The problem with this approach is that it can cause the system to lock up and crash if users are processing transactions too. Why? Because most developers do not use transactional methods to retrieve and update the next sequence number in the Microsoft Dynamics GP tables. Imagine updating the next SOP number while a user is creating a SOP transaction at the same time. Without proper transactional methods in place, this can wreak havoc in the system.

I have also seen techniques that immediately query the transaction tables looking for the highest DEX_ROW_ID and the transaction associated with it. This technique is also not reliable, especially if they are users processing transactions at the same time the integration is being executed. In summary, you can end up retrieving the wrong document number for your source integrating transaction.

So what's the solution?

Over the years I have developed a technique to overcome this hurdle. The technique assumes that the source transaction records have a unique identifiable key (and in fact, they should). In the case of journals, orders, or invoices, this key is assigned by some source system where the transactions are being integrated from. You can easily spot this key as it typically allows header records to be linked to the detail records to create the relationships between these. For example, if you are integrating orders from your source system into invoices in Microsoft Dynamics GP, it is assumed that the Order Number from your source system is the key.

The idea is to pass this source key to a Microsoft Dynamics GP user defined field (via mapping) or other data field where it can easily be queried after the document has been integrated.

Tomorrow, I will describe the scripting elements that make it possible to retrieve a document number assigned by GP once a source document is integrated and the field rule for the key field is set to Use Default.

Until next post!

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

Read More

Custom Serialization Assemblies for eConnect 2010

Yesterday it was Bryan Prince demonstrating in-memory XML serialization, today is Chris Roehrich over at Developing for Dynamics GP. Chris shows a technique to create a Custom Serialization Assembly for eConnect outside of the method exposed in the eConnect Programmer's Guide.

Please be sure to visit Developing for Dynamics GP to read Chris' article.


Until next post!

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

Read More

Integration Manager: Integrating journal entries with Analytical Accounting Information

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.

eConnect Adapter - Journal Entry# field with Use Default rule value

However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal



Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc
 
 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 .Execute 
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJournal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP's dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper 
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL 
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry 
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

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

Edits:
05/25/2011 - fixed IM global variable name as it was not matching between the Before Document script and the Journal Number Field Script, causing nothing to be assigned to the field and erroring out the integration.

Read More

Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM's Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week's series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 - Creating a CLR assembly and working with CRM web methods

08/20/2010 - Configuring SQL Server and creating table triggers

Until next post!

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

Read More

eConnect Integration Service for Microsoft Dynamics GP 2010

One of the biggest improvements in eConnect 2010 is the addition of the new eConnect Integration Service. The eConnect installer now creates a new Windows Service application named eConnect for Microsoft Dynamics GP 2010 integration Service. The eConnect Integration Service is a Windows Communication Foundation (WCF) service that replaces the (very unstable) eConnect COM+ object available in previous versions of eConnect.

The eConnect Integration Service supports the operations of the eConnect .NET assemblies, the BizTalk adapter, and MSMQ interfaces. In addition, you can use the service directly from an application that adds a service reference to the eConnect Integration Service.




If you add a service reference, you do not need to add the Microsoft.Dynamics.GP.eConnect assembly and namespace to your development project.



To add the eConnect Integration Service to an application, you must first add a service reference to the Visual Studio project for that application.

To add a service reference to a Visual Studio project, the properties for the project must specify the target framework as .NET Framework 3.5. The following is a typical URL for the eConnect Integration service.

net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/


The interface provided by the service reference includes the same methods you find in the Microsoft.Dynamics.GP.eConnect assembly and namespace. Before you use a service reference to access eConnect Integration Service, you should become familiar with WCF development concepts.

Until next post!

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

Read More

IM - Integration Manager Service Packs

Have you ever wonder how many service packs have been issued for each version of Integration Manager 10? Have you asked the question more than 100 times without a straight up answer? Suffer no more, the following is a list of service packs and hotfixes issued so far for Integration Manager 10.0

To check your version and build number, launch Integration Manager and click Help and then click About Integration Manager

Integration Manager version 10.0

RTM Version                     10.00.0836
Service Pack 1                  10.00.0932
Service Pack 2                  10.00.1072
Service Pack 2 Hotfix           10.00.1134
Service Pack 3                  10.00.1221
Service Pack 3 March Hotfix     10.00.1305
Service Pack 4                  10.00.1378
Service Pack 4 September Hotfix 10.00.1384
Service Pack 5                  10.00.1592

Remember that Integration Manager service packs do not rely on Microsoft Dynamics GP service pack level and can be installed at your discretion.

Until next post!

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


[Edits]
09/17/2010 - Included Service Pack 5 build number

Read More

CRM - How to match product information between CRM and GP

I have had the fortune to work on a very challenging Microsoft Dynamics CRM and Microsoft Dynamics GP multi-system integration for the past 3 months, requiring lots of custom code to manage above and beyond the common interfaces that can be had with some of the tools available in today's market. Of course, one of the main challenges has been for the folks building the reports. So today I start a series of quick and easy queries showing how to obtain data from both Microsoft Dynamics CRM and GP, that hopefully will help you build some quick reports, but also begin to find similarities between the two products. Today features the Product catalog from CRM and the Item Master from GP.

select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from ProductBase a
 inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

If you have custom fields in CRM for your Product, you can use the following query to tie these in.

select a.ProductNumber, a.ProductId, a.Name, c.ITEMTYPE, c.STNDCOST, c.CURRCOST, c.ITMCLSCD
from ProductBase a
 inner join ProductExtensionBase b on (a.ProductId = b.ProductId)
 inner join IV00101 c on (a.ProductNumber = c.ITEMNMBR)

Note that in the above query, I have not included any columns from the ProductExtensionBase table. The reason? It's custom! You can include any name for the custom columns you have created here.

If you are one of those who are comfortable using the CRM views, then this query will provide a one-stop shop to obtain all the information from both the ProductBase and the ProductExtensionBase tables.

select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from Product a
 inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

Note: the above query assumes you have a linked server connection between your Microsoft Dynamics GP and CRM servers and that you have created synonyms for the objects referenced or that you have at least arranged for these tables to be replicated to a single location.

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

IM - How to group Integration Manager transactions based on transaction date

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 - 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.

---

The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:




Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:



When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

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

Read More

IM - How to filter source query records dynamically

Ever wanted to give users the ability to limit source records dynamically? How about being able to filter records from in a source query by a date range? Well, I encoutered this situation working on my current project.

Out of the box, Integration Manager offers the ability to retrieve records and set static filters to source records. However, in many cases users may want to dynamically (at runtime) establish a date range or any other range parameter for the records, then have these ranges applied to the source query.

This is possible by using some old fashioned VBScript with Integration Manager. Consider the following records:

DOCUMENT_DATE JOURNAL_NUMBER ACCOUNT_NUMBER DEBIT_AMOUNT CREDIT_AMOUNT
12/01/2009 100  000-1200-00 100.00  0.00
12/01/2009 100  000-6620-00 0.00  100.00
12/15/2009 200  000-1201-00 22.50  0.00
12/15/2009 200  000-6630-00 0.00  22.50
01/06/2010 300  000-1200-00 120.00  0.00
01/06/2010 300  000-6620-00 0.00  120.00
01/20/2010 400  000-1201-00 52.50  0.00
01/20/2010 400  000-6630-00 0.00  52.50

In order to make our integration interactive, we must first prompt the user to enter the date restriction in the format required to filter the data:

Before Integration

Dim startDate, endDate

Do
  startDate = InputBox("Enter the start date for your transactions (mm/dd/yyyy).")
  If Not IsDate(startDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(startDate)

Do
  endDate = InputBox("Enter the end date for your transactions (mm/dd/yyyy).")
  If Not IsDate(endDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(endDate)

If CDate(startDate) > CDate(endDate) Then
 MsgBox("The start date must be greater than the end date. Integration will end now.")
 CancelIntegration
End If

' Store the user input in global variables that can be retrieved later on
SetVariable "gblStartDate", startDate
SetVariable "gblEndDate", endDate

Now we can apply the user's input as restrictions to our source query data by invoking the AdditionalCriteria property of the Query object.

Before Query

Query.AdditionalCriteria = "DOCUMENT_DATE >= '" & CDate(GetVariable("gblStartDate")) & "' AND DOCUMENT_DATE <= '" & CDate(GetVariable("gblEndDate")) & "'" 

Note that the Query.AdditionalCriteria will only work on source queries that use a Text or Simple ODBC DSN. The AdditionalCriteria property will not work on Advanced ODBC queries.

Until next post!

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

Read More

New article on MSDynamicsWorld: "Integrating Microsoft Dynamics CRM with Microsoft Dynamics GP"

"No, this is not the Chris Tucker and Jackie Chan movie..."

One of my goals for this year is to become technically and functionally skilled in Microsoft Dynamics GP CRM. However, I never expected this to happen so quickly and abruptly as it did some 3 weeks ago with a "weekender-all nighter-gotta-get-it-done-by-Monday" project I worked on with my business partner. I collected some of the best practices and experiences from that project and put them together in my new MSDynamicsWorld article.

Until next post!

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

Read More

New article on MSDynamicsWorld: "Putting to Rest Four Myths About Microsoft Dynamics GP Integration Manager"

Folks, this time I take a hard swing at four common myths around the *beloved* Integration Manager. I know there are a lot of mixed feelings out there when it comes to poor IM, but this tool has saved my *ss in more than one occasion and in this article I make a case for its use in serious mission critical application integrations.

So head over to MSDynamicsWorld and read up! After reading the article, I would like to get your comments and experiences with IM.

Until next post!

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

Read More