How to add Item Category Long Descriptions to reports using the Support Debugging Tool
I am currently working on a support case where the customer is making use of the six user defined categories for items. That in itself is nothing remarkable or worth blogging about. However, they are using Long Description field on the Item Category Setup window and wished to include the data from this field onto reports. That's the difficult bit.
The Item Category Setup window (screenshot below) is opened from the expansion button for User Category on the Inventory Control Setup window (Microsoft Dynamics GP >> Tools >> Setup >> Inventory >> Inventory Control).
The problem with getting these fields to display on a report is that there is no way to create a relationship to the IV_User_Category_SETP (IV40600) table from the array of Item Category Values fields stored agains the IV_Item_MSTR (IV00101) table. The reasons is that the Item Categories are stored using a primary key of an Item Category Number (1-6) and an Item Category Value. To create the relationship, we need to provide both the Number and the Value, we can get the Value from array field, but there is no table field containing the Number and the relationship functionality of Report Writer does not allow a constant value to be provided.
I checked the Dynamics.dic source code for a Report Writer function to be able to access this data and none were available. In fact, the Long Description field for Item Categories does not appear to be used anywhere else in the application.
To get this data to be available to a report, it is possible to use the Support Debugging Tool's support for the 6 user defined report writer functions described in the following Knowledge Base (KB) article:
- Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP (KB 888884)
- Using the Support Debugging Tool to assist with Report issues
The 6 functions; rw_ReportStart(), rw_ReportEnd(), rw_TableHeaderCurrency(), rw_TableHeaderString(), rw_TableLineCurrency(), and rw_TableLineString() are placeholder functions in the core Dynamcis.dic dictionary. They contain no code (just parameters), but are available for Dexterity developers to trigger against and return data to a report.
A great example of these functions in use is to include Extender fields on a report. The following KB article explains to to achieve this:
[Edit] The following blog post shows how you can use the Support Debugging Tool to overcome the 80 character limit on string calculated fields when long string fields in Extender:
The Support Debugging Tool has the ability to call scripts written in Runtime Execute from these 6 functions. In this example, we will use the rw_TableHeaderString() function to call our script.
DISCLAIMER: Use of this functionality of the Support Debugging Tool in this fashion is not supported by Microsoft Dynamics Support.
Below are the steps to create the script, however you can just load the script in using the Support Debugging Tool configuration file attached to the bottom of this article.
In the Runtime Execute window, we create the script:
- Enter the Script ID: ITEM_CAT_DESC.
- Enter the Script Name: Item Category Description.
- Select the Dictionary Context for the code: Microsoft Dynamics GP.
- Then for the code we can use the Help Function button to add the template for the rw_TableHeaderString() function.
- Finally, we need a bit of custom Dexterity code to get the record from the table and return the desired data. This code is fairly simple: set the primary key fields for the table, get the table by the primary key number, if no errors, return the desired data using a case statement.
Below is a screenshot of the resulting Runtime Execute window:
Here is the actual script, however it is easiest to load the configuration file attached to the bottom of this post.
Example code from Runtime Execute window
local string MBS_TableHeaderString;
local string MBS_Number;
local integer MBS_Type;
local integer MBS_Control;
local string MBS_String;
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, "Control", MBS_String;
MBS_Control = integer(value(MBS_String));
MBS_TableHeaderString = "";
{ Add your code below here }
'User Category Number' of table IV_User_Category_SETP = MBS_Type;
'User Category Value' of table IV_User_Category_SETP = MBS_Number;
get table IV_User_Category_SETP by number 1;
if err() = OKAY then
case MBS_Control
in [1]
MBS_TableHeaderString = 'User Category Long Description' of table IV_User_Category_SETP;
in [2]
MBS_TableHeaderString = 'Image URL' of table IV_User_Category_SETP;
else
end case;
end if;
{ Add your code above here }
call with name "MBS_Param_Set" in dictionary 5261, "TableHeaderString", MBS_TableHeaderString;
Now that the function has been created (or imported), it is possible to create a Calculated field in the Report Writer to use the function to add the Item Category Long Description to a report.
From the layout of your report in Report Writer:
- In the Toolbox window, select Calculated Fields from the drop down list and click New.
- Type in a name for the calculated field: eg. (C) Item Category Long Description 2.
Note: We called the field 2 as we will use this to obtain the category 2 long description.
- Select a result type that matches the data being returned: eg. String.
- Click the Functions Tab, click User-Defined.
- Select Core: System, Function: rw_TableHeaderString and click Add.
- Click the Constants Tab, select Type: Integer, Constant: 5261 and click Add.
Note: This is the Dictionary ID for the Support Debugging Tool.
- Still on the Constants Tab, select Type: String, Constant: ITEM_CAT_DESC and click Add.
Note: This is the Script ID from Runtime Execute.
- Click the Fields Tab, select Resources: Item Master, Field: User Category Values and click Add. Select the array index of 2 and click OK.
Note: This is the User Category Value needed for the primary key of the table.
- Click the Constants Tab, select Type: Integer, Constant: 2 and click Add.
Note: This is the User Category Number needed for the primary key of the table.
- Still on the Constants Tab, select Type: Integer, Constant: 1 and click Add.
Note: This is the number which decides the field to be returned as per the case statement in the code.
- Click OK to save the Calculated Field.
- Drag the field onto the report layout and format as desired.
Note: Formatting is easy using the Arrange Tools in the Toolbox to align and size, and then clicking on a field of the format desired, shift clicking on the new field and then pressing Ctrl-D and Enter.
Below is a screenshot of the Calculated Field Definition showing where to find the rw_TableHeaderString User Defined Function.
FUNCTION_SCRIPT( rw_TableHeaderString 5261 "ITEM_CAT_DESC" IV_Item_MSTR.User Category Values [ 2 ] 2 1 )
Once the calculated field has been added to the modified report, it will start returning the data as desired.
Please note for this method to work on all workstations, the Support Debugging Tool must be deployed in the recommended configuration by being installed on all workstation and using a central shared folder for the setup file location. Setting up the tool in this fashion is covered in detail in the User Guide Manual PDF provided with the tool.
Attached to the bottom of this post is an archive containing the Support Debugging Tool's configuration file and a modified SOP Blank Invoice report.
I hope you find this information useful.
David
09-Jul-2012: Added Link to blog article discussing Support Debugging Tool and Report Writer functions.
25-Mar-2014: Fixed Example Script not being formatted correctly and added link to article on long string Extender fields.