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

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!

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

Read More

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:

DHL
http://www.dhl.com/content/g0/en/express/tracking.shtml?brand=DHL&AWB=%1

FedEx
http://fedex.com/Tracking?action=track&tracknumber_list=%1

UPS
http://wwwapps.ups.com/etracking/tracking.cgi?submit=Track&InquiryNumber1=%1&TypeOfInquiryNumber=T

USPS
https://tools.usps.com/go/TrackConfirmAction_input?qtc_tLabels1=%1

Ontrac
http://www.ontrac.com/trackingdetail.asp?tracking=%1

Happy package tracking!


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

Read More

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!

Game Trailers - E3 2012 - Rides Whips & Hoons

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!

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

Read More

Year End Closing... NOT!

It's been a quiet, quiet few weeks over on this blog, but trust me, it's not because I have forgotten about you out there, but rather because my work load has increased over the past weeks, overseeing 5 projects (3 of which are going live in the next few weeks), ramping up some new IntellPartners ventures - more on that later - and welcoming new implementation challenges in South America. But that's not all... I am also gearing up for the GPUG Summit 2012 in Seattle and the Microsoft Dynamics GP Technical Airlift 2012 in Fargo, North Dakota.

However, and despite the hectic schedule, I wanted to bring to your attention a case that I had a chance to work on recently. This was for one of my clients here in Atlanta with manufacturing facilities in China. My client hosts Microsoft Dynamics GP here in their Atlanta headquarters and provide Citrix access to their subsidiaries in China to enter information into the Chinese company database. Things move at a different pace there so the office in China just got around closing the 2011 year. The accounting department quickly noticed that all beginning balances for 2012 where zeroed out - including all asset accounts.

Of course, the immediate questions came to mind.

1. Did you follow KB article 888003 - Year-end closing procedures for General Ledger in Microsoft Dynamics GP. Upon close inspection of the fiscal periods configuration, it was noted that 2011 did not appear to have been closed, this is, the year was still not marked as historic.

2. Can you run a trial balance as of December 31, 2011? After running a summary trial balance as of December 31, 2011, things appeared even more puzzling. The following is a result of that trial balance (some names have been obfuscated to protect the innocent).

Trial Balance Report

The trial balanced showed all accounts - except for a few - with zero ending balances.

3. The next question at hand was simply, when did you noticed this and what changes or entries have been posted in your system lately?

After some back and forth with the Chinese accountants, it was noted that they posted "couple entries" to "close the year". A few SQL queries later and we found the culprits causing the balances to net out to zero. We removed the journals and ran a reconcile on year 2011 and we were back in business.

This time around, the accounting staff in Atlanta executed the year-end closing procedures and all balances rolled forward as expected and everyone left home just on time to beat rush hour traffic. An ensuing memo is awaiting the Chinese staff with the proper year-end closing procedures.


Until next post!

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

Read More

Adding more comment lines to POP Purchase Orders

Just recently, I was asked by a customer to address an issue with their line item comments truncating at 4 lines. In essence, the customer wanted the ability to print more than 4 lines of comments at the line item level on their purchase orders.

My customer happens to be in the Nuclear Waste Management industry, and as it turns out, they have strict requirements to provide contract information, start/end dates, and a number of regulatory and compliance information on purchase orders. Some of this information is very critical to the transportation of certain products on United States highways. So as you can imagine, the request for additional comment lines was critical.

The following shows an example of what the customer was facing:

Purchasing Comment Entry window and POP Purchase Order Blank form

As you can see, the comments are printing only 4 lines and truncating the rest of the text.

Before going into the details, they are a few articles that guided me through the solution:

How to Display More Than Four Line Item Comments in the "SOP Blank Invoice Form" Report in Sales Order Processing in Microsoft Dynamics GP

Adding more comments to SOP Documents (Developing for Dynamics GP)

Of course, these articles refer to SOP, but the underlying techniques used in them will allow us to make the necessary changes to the report. This blog post should be used in conjunction with the How To article above to provide some additional steps to get the best result for making the changes to the POP Purchase Order report.

Background

Comments for Purchase Order Processing (POP) transactions are entered using a text field which allows for a maximum of 500 characters, as shown above. When the comments are saved, the text field is divided into 4 string fields of 50 characters each, which is printed on the report. The code that parses the text field into the 4 string fields is smart enough to avoid splitting a word oddly.  The code will fit as many words as it can up to the last space before the 50 character mark or up to a carriage return (new line). When the POP documents are printed, it is these 4 comment string fields which are printed on the report. This means that if you use carriage returns or type a comment longer than 180-200 characters, you are unlikely to see all of your comment text shown on the report.

If you look at a POP document report, such as the POP Purchase Order Blank Form, you will see that the 4 comment lines are printed using 4 additional footer sections.

POP Purchase Order Blank Form Report Writer Layout showing 4 footers (F3 - F6)

Looking at each of these additional sections (Tools > Section Options), you will see that each one is configured to be Suppressed when the appropriate 'sSuppressCommentX' calculated field is empty. The 'sSuppressCommentX' calculated field is defined to look at the comment string array report field and return an empty string or the actual comment to be printed on that footer. The reason for all this complexity is so that the space for the comment line strings is only taken up when there is actually a comment line to display. If this technique with additional footers with conditional suppression was not used, there would be 4 lines between every POP line item whether it was used or not - not an efficient use of already cramped space.

Solution

The solution is to use the RW_POPLINECommentText user-defined Report Writer function in the system series in a set of calculated fields.  The same recommendations apply as with the SOP article above, this is, making each line the maximum 80 characters supported by Report Writer strings and creating 8 lines. This gives the potential for 640 characters and so should easily be able to display the 500 characters of comment text (even without breaking words in half or additional carriage returns).

Why 8 lines? This is so we can place two lines on each of the 4 additional footer sections. If we update the suppression logic correctly, it will mean there will only be the possibility of one blank line between SOP line items. With only 4 section breaks, we can only print 0, 2, 4, 6 or 8 lines at a time, so if there are actually an odd number of lines to be printed, there will be one blank line printed. If there are an even number of lines, then there will be no blank line.

For an explanation on why not using additional footers instead, see Adding more comments to SOP Documents over at Developing for Dynamics GP.

The parameters for the RW_POPLINECommentText function are as follows:

RW_POPLINECommentText()

function returns string OUT_string;
in integer IN_Type;   { POP Type of Document }
in string IN_Number;   { POP Number of Document }
in long IN_Ord;    { Ord Number of Line }
in integer IN_characters;  { Number of Characters per Line }
in integer IN_line;   { Line Number to Return }

Implementation

The steps below assume some knowledge of Report Writer. If you need detailed step by step instructions please look at the How to document.

Line Comments

1. Create 8 calculated fields of result type string for the 8 Line level comment lines:

(C) Line 1 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number popPOLineRollupTemp.Ord 80 1 )

(C) Line 2 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 2 )

(C) Line 3 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 3 )

(C) Line 4 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 4 )

(C) Line 5 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 5 )

(C) Line 6 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 6 )

(C) Line 7 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 7 )

(C) Line 8 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 8 )

Note: The only difference for each calculated field is the line number in the field name and the line number constant as the last parameter.

2. Expand each of the comment additional footers to 2 lines height.

3. Move the 4 existing Comment fields to the right of the report.

4. Double Click on each of the 4 old Comment array fields and set the Visibility to Invisible.

Comment Footers and Invisible Fields

5. From Calculated Fields drag out the 8 new calculated fields placing two fields in each section. Drag out the field width as desired (at least 370).

Calculated fields on footer sections

6. The 4 sSuppressCommentX calculated fields will need to be changed to conditional fields and the return type changed to Integer, as shown:

Calculated Field definition

sSuppressComment1 = F6_LAST (C) Line 1 Comments = "" AND  F6_LAST (C) Line 2 Comments  = ""

sSuppressComment2 = F5_LAST (C) Line 3 Comments = "" AND  F5_LAST (C) Line 4 Comments = ""

sSuppressComment3 = F4_LAST (C) Line 5 Comments = "" AND  F4_LAST (C) Line 6 Comments = ""

sSuppressComment4 = F3_LAST (C) Line 7 Comments = "" AND  F3_LAST (C) Line 8 Comments = ""

After all these changes, it's time to save the report and test:

Purchase Order Blank Form

Now we can see the full line item comment displays nicely on the report.

Downloads

You can download the package file for this customization here.



POPBlankFormExtendedNotes.zip

Until next post!

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

Read More

Theresa Nistler talks Microsoft Dynamics GP 2013 Features

Theresa Nistler, Sr. Program Manager with the Microsoft Dynamics GP team, explains the balancing act of defining features and incorporating those into the application. Theresa also works hand to hand with the Support teams to compile feedback from clients aand resolve bugs.

Until next post!

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

Read More