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|
|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
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
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
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!
Mariano Gomez, MVP