Tuesday, June 26, 2012

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).

-- 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 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 
    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!

Mariano Gomez, MVP
IntellPartners, LLC

Monday, June 18, 2012

Errol Schoenfish: 25 years and still loving it

I read attentively Errol's recent post, 25 years and still loving it!, from beginning to end. Frankly, it reminded me of my own start in the industry and how I have evolved personally and professionally over the last 18 years.

So much was my interest in the article that I asked Errol to provide some additional insight into his early days working for Great Plains Software, how he's got here today, and what made those early days special. After all, we all have dealth with that one support case we will always remember.

"This is the group of 11 support people that I started with. A great group. Still with Microsoft are Sue (Ewing) Larson, Lynne Stockstad, Randy Gerhold, Cheri (Whelan) Schoenfish, and myself. Both Randy and I left Great Plains and returned later. Randy came back through the acquisition of Real World and I left the company for 3 ½ years to work for a reselling partner of GP in Fargo and came back 16 ½ years ago. I’ve been a support technician, quality assurance manager in Development, a reselling partner, a Fargo-based partner account manager, Regional Sales manager, National and Global Accounts Sales Manager, Director of Sales and Marketing of Australasia, CRM evangelist, and Director of Product Management for Dynamics GP and SL.

1987 Great Plains Support Team - From left to right
First row: Sue (Ewing) Larson, Barb Bradner and Linda Warner
Second row: Cheri (Whelan) Schoenfish, Joe Steffan, Lynne Stockstad, Mitch Ruud, Glen Altringer, Errol Schoenfish, Randy Gerhold, Tom Eide

In 1987, As a recent college grad your initial impressions of such a young and vibrant company are “Man, what a great place to work”. The average age of Great Plains Software in 1987 was around 24 years old, so you can imagine the type of culture that was perpetuated. We worked long hours and spent all of our waking hours together as a group because most of us were young and single and on our first jobs. I really did love my first job, there was a certain sense of pride in helping someone solve a situation with their software. I remember one call in particular; A lady had called in because she was having problems with a payroll check run and she had 50 migrant workers waiting outside her trailer office for their checks. After a few minutes and getting the check run going she said “You’ve literally saved my life today, thank you sooo much”. There were always challenging times doing phone support for a software company also, but we built a reputation of being the best software support in the industry and won many awards along the way."

You will agree with me that nothing beats the hairdos of the 80's... ahhh!

Big thank you to Errol Schoenfish and we DO expect you to be around for another 25 years, indeed.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Thursday, June 14, 2012

Leveraging Custom Links to track carrier shipments in Sales Order Processing

You know, I believe I will forward this to MVP Frank Hamelly for his next session at the upcoming GPUG Summit Seattle 2012, "13 MORE Classic, Underused Features in GP".

And now that I did the plug in for Frank's session...

This week I have a chance to review one of those truly underutilized features in Microsoft Dynamics GP, this time, as a result of some quality control work I am performing at one of our projects going live in 2 weeks.

My customer, a specialty food and fine chocolates distributor here in the Washington D.C. area needed to provide his customer service staff with the ability to see package delivery status when products leave any of the 5 warehouses around the country. To make matters more interesting, they are currently using Savant Software's Shipping Manifest module coupled with Savant Software's Warehouse Management System, both of which are integrated to Microsoft Dynamics GP. When orders are shipped from the Shipping Manifest solution, product tracking numbers are returned to the order document in Microsoft Dynamics GP.

Tracking Numbers on the Sales User-Defined Fields Entry window

My customer uses the four major delivery carriers: UPS, DHL, FedEx, USPS, and Ontrac.

We used Microsoft Dynamics GP Custom Links feature to provide the package tracking capabilities required.

Custom Link Setup

To setup a link to the Fedex tracking site, you select the Prompt to which you want to associate the link, in this case the "Tracking Number" prompt. You will then provide a prompt to display for the link, "FedEx Tracking". Next, you will want establish the field value for which the prompt will be displayed, in this case we want to display the Fedex Tracking prompt when the associated shipping method is FEDEX GROUND. Finally, we need the URL for the FedEx tracking website and need to pass in the tracking number string as a parameter to the URL. This is accomplished with the "%1" string substitution moniker at the end of the URL.

The result is shown below:

Tracking Number Custom Link

When the link is selected, the user is directed to the FedEx tracking site where information is displayed about the tracking number selected.

The following are tracking sites URLs for each of the major carriers, with the corresponding string substitution parameter for the tracking number:






Happy package tracking!

Mariano Gomez, MVP
IntellPartners, LLC

Saturday, June 9, 2012

Microsoft West Coast Customs 67 Mustang

This is not necessarily a Microsoft Dynamics GP post, but rather a post on how Microsoft is thinking of embedding some of its current Windows 8, Kinnect, and cloud technology into the automotive world. On the other hand, West Coast Customs is one of the top custom cars designer in the world and are pretty known for their Street Customs show on Discovery's TLC channel.

Both companies teamed up to produce one of the sickest rides I have ever seen in years. I think you can appreciate the craftsmanship from West Coast Customs in executing Microsoft's vision. Enjoy the video!

And yes... that was "250 to 300" thousand dollars, as in $300,000!!

The car was revealed at the Bellevue, WA Microsoft store. Here was the launch:

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC