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

Adding Customer Item User Defined fields to SOP Invoice

Just recently I ran across a request to add the Customer Item user defined fields to the Sales Blank Invoice Form report in Report Writer. As usual with these type of requests, chances are it's been done before and/or it's probably documented in some KB article.

As it turned out, KB article 918943 outlines the steps required to add the Customer Item Number to the Sales Blank Invoice Form report:

How to add the "Customer Item Number" field to a quote, order, or invoice form in Sales Order Processing in Microsoft Dynamics GP

The KB article however, makes use of the Report Writer function rw_SOP_GetCustomerItemNumber to get around the issue. There's also an additional Report Writer function, rw_SOP_GetCustomerItemDescription which allows you to retrieve the description for the customer item. Now, that's all cool, but what about the user-defined fields?

Customer Item Maintenance

The screenshot above highlights the fields in question. As it turned out, there are no Report Writer functions to retrieve those.

In looking at possible ways to add these fields you could try 3 different things:

1. Use Modifier with VBA to retrieve the user-defined fields. While this is a viable solution, the partner was adamant about not using VBA because of lack of supportability on the Microsoft Dynamics GP 2013 Web Client.

2. Table relationships in Report Writer. After looking at the table keys on SOP_LINE_WORK (SOP10200) and sopCustomerItemXref (SOP60300), I concluded that table relationships would not work either. The problem here is that the SOP_LINE_WORK table does not contain the Customer Number field, required to be able to establish a relationship to the sopCustomerItemXref. After all, if the field existed, there would be no need for the Report Writer functions mentioned above.

3. Support  Debugging Tool. The SDT has the ability to encapsulate Dexterity code with it's Runtime Execute feature. That code can be saved to a Script ID. Furthermore, the Support Debugging Tool leverages the 6 generic Report Writer functions available in the Microsoft Dynamics GP dictionary. These functions were created to allow third party developers a way around an Alternate Report, with the ability to retrieve data from their tables.

For more information on the generic Report Writer functions, take a look at KB article 888884:

Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP

Given all the options and drawbacks, it was decided that Support Debugging Tool would be able to accomplish the job. Since it's a Dex application, it certainly would move forward to Microsoft Dynamics GP 2013 and Web Client, and in addition, would be able to work around the table relationships issue.

---

One thing I like about the Support Debugging Tool's Runtime Execute is the ability to use the Helper functions. In particular, the helper function needed is one that allows us to leverage a generic Report Writer function, the rw_TableLineString, while line items are being printed on the report - when a line item is displayed, we must display the corresponding Customer Item user defined field.

Insert Helper Function window

Since there's a template for the above function, all we need to do is click on the Ok button. As if the Support Debugging Tool wasn't already helpful, it now autogenerates some of the code needed to work with the rw_TableLineString:

Runtime Execute window with auto-generated Dexterity code

All that's left now is to add a script ID and enter the bit of code needed to retrieve the user-defined fields:

Runtime Execute script

The full version of the script can be found below:
Script ID: CUSTITMUDF

local string MBS_TableLineString;
local string MBS_Number;
local integer MBS_Type;
local currency MBS_SequenceOne;
local currency MBS_SequenceTwo;
local integer MBS_Control;
local string MBS_String;

{Declarations}
local string ItemNumber;
local string CustomerNumber;

call with name "MBS_Param_Get" in dictionary 5261, "Number", MBS_Number;
call with name "MBS_Param_Get" in dictionary 5261, "Type", MBS_String;
MBS_Type = integer(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceOne", MBS_String;
MBS_SequenceOne = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceTwo", MBS_String;
MBS_SequenceTwo = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "Control", MBS_String;
MBS_Control = integer(value(MBS_String));
MBS_TableLineString = "";

{ Add your code below here }

{ Retrieve the item number in question }
clear table SOP_LINE_WORK;
set 'SOP Number' of table SOP_LINE_WORK to MBS_Number;
set 'SOP Type' of table SOP_LINE_WORK to MBS_Type;
set 'Line Item Sequence' of table SOP_LINE_WORK to MBS_SequenceOne;
set 'Component Sequence' of table SOP_LINE_WORK to MBS_SequenceTwo;

get table SOP_LINE_WORK by number 1;
if err() = OKAY then
 set ItemNumber to 'Item Number' of table SOP_LINE_WORK;
end if;

{ Now, get the customer number }
clear table SOP_HDR_WORK;
set 'SOP Number' of table SOP_HDR_WORK to MBS_Number;
set 'SOP Type' of table SOP_HDR_WORK to MBS_Type;

get table SOP_HDR_WORK by number 1;
if err() = OKAY then
 set CustomerNumber to 'Customer Number' of table SOP_HDR_WORK;
end if;

{Since we have the customer number and item number, we should now be able to
 retrieve the customer item number
}

if not empty(ItemNumber) and not empty(CustomerNumber) then
 clear table sopCustomerItemXref;
 set 'Item Number' of table sopCustomerItemXref to ItemNumber;
 set 'Customer Number' of table sopCustomerItemXref to CustomerNumber;
 
 get table sopCustomerItemXref by number 1;
 if err() = OKAY then
  case MBS_Control 
   in [1] {UDF 1}
    MBS_TableLineString = 'User Defined 1' of table sopCustomerItemXref;
   in [2] {UDF 2}
    MBS_TableLineString = 'User Defined 2' of table sopCustomerItemXref;
   in [3] {UDF 3}
    MBS_TableLineString = 'User Defined 3' of table sopCustomerItemXref;
   in [4] {UDF 4}
    MBS_TableLineString = 'User Defined 4' of table sopCustomerItemXref;
   in [5] {UDF 5}
    MBS_TableLineString = 'User Defined 5' of table sopCustomerItemXref;
   in [6] {UDF Key 1}
    MBS_TableLineString = 'User Defined Key1' of table sopCustomerItemXref;
   in [7] {UDF Key 2}
    MBS_TableLineString = 'User Defined Key2' of table sopCustomerItemXref;
   in [8] {UDF Key 3}
    MBS_TableLineString = 'User Defined Key3' of table sopCustomerItemXref;
   in [9] {UDF Key 4}
    MBS_TableLineString = 'User Defined Key4' of table sopCustomerItemXref;
  end case;
 else
  MBS_TableLineString = "";
 end if;
end if;

{ Add your code above here }

call with name "MBS_Param_Set" in dictionary 5261, "TableLineString", MBS_TableLineString;

Cool balloons! One aspect of the programming was, how to leverage the same function (as opposed to creating multiple scripts that would perform the same operations) to pull in different UDFs? As it turns out, there's a parameter in the rw_TableLineString function, which allows you to specify a control value which determines the position of the piece of data to be returned. This control value is an integer type. Knowing this allowed me to send in a numeric value for the UDF I wanted to retrieve and define a case...end case statement evaluating the control parameter to return the proper UDF field.

---

Finally, the rest is just house keeping at the Report Writer level... First, we must create two currency calculated fields: one for the Line Item Sequence and another for the Component Sequence. As it turns out, our Report Writer custom function requires two currency parameters. Below is the rw_TableLineString function signature

rw_TableLineString()

in integer dict_id; {Dictionary ID}
in string script_id; {Script ID} in string sNumber; {control field 1}
in integer sType; {control field 2}
in currency cSequenceOne; {control field 3}
in currency cSequenceTwo; {control field 4} 
in integer iControl; {which piece of data to return}

Our calculated fields are shown below:

(C) Component Sequence calculated field

(C) Line Item Sequence calculated field

Finally, we need a calculated field to invoke our Report Writer function, which in turn will call our script ID created in Support Debugging Tool. In this specific case, we are going to retrieve the Customer Item user-defined field 1.

CustomerItemUDF1 Calculated Field

FUNCTION_SCRIPT( rw_TableLineString 5261 "CUSTITMUDF" SOP_LINE_WORK.SOP Number SOP_LINE_WORK.SOP Type (C) Line Sequence Number (C) Component Sequence 1)

We can now move our CustomerItemUDF1 calculated field onto the report layout:

After granting security to the modified report, we can print an invoice to verify the report works.

As you can see, once more we have leveraged the power of the Support Debugging Tool to deliver what would have otherwise seemed like a difficult or unattainable customization to a report. I hope this is yet another incentive to install the tools and begin taking advantage of its features.

Until next post!

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

Read More

Chris Roehrich on eConnect vs Web Services for Microsoft Dynamics GP

Chris Roehrich is a really cool dude, who is an Escalation Engineer and member of the Developer Support team for Microsoft Dynamics GP in Fargo, North Dakota. I have had the pleasure of meeting Chris in person and have to say this guys knows his stuff - note I can't use the other s-word here - plus, he's a really fun person.

Today, I came across a really cool article from Chris over at Developing for Dynamics GP which addresses the eConnect vs Web Services for Microsoft Dynamics GP dilemma, faced by integration developers.

One thing I must add to Chris' excellent article is that eConnect and web services are typically used in the data integration arena. David Musgrave and I covered ground on this and the user interface customization tools (Modifier with VBA, Dexterity, and Visual Studio Tools) at Microsoft Convergence Atlanta 2011. You can download the materials from that session on the Learning Resources page on this blog.

Also, you will want to check out the following white paper by Microsoft which has been a great resource over the years to help in the process of selecting a tool:

Choosing a Development Tool for Microsoft Dynamics GP


Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Brian Meier talks Microsoft Dynamics GP Business Analyzer

Business Analyzer is one of those products that I really dig: it's slick, it works, and it just makes life easier for executives and information consumers who don't necessarily need access to GP... but if they do, Business Analyzer also lives within GP, with some pretty cool context sensitivity, based on the info displayed by a specific item or items within a Navigation List object.

Brian Meier, Sr. Lead Program Manager at Microsoft, explains his role and provides an inside look at Business Analyzer.

Related Articles:

Kevin Racer talks Microsoft Dynamics GP 2013 Architecture
Chad Sogge describes the features process for Microsoft Dynamics GP 2013

Until next post!

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

Read More