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/