How to display more than 80 characters of an Extender Long String field in reports using the Support Debugging Tool

  • Article

David Meego - Click for blog homepageHere is another support case that was resolved simply and quickly using the Support Debugging Tool.

The customer was using Extender to add an additional comment to the inventory stock transfer transaction. They created an Extender ID "ST_COMMENTS" and used the Long String field type in Extender which is a 255 character string. This worked fine.

Then the customer wanted to include the Extender field on the posting reports. There is a Knowledge Base (KB) article that explains how to use a calculated string field and a user defined report writer function to return Extender to a report. click on the link below for more details:

The instructions are to use the rw_TableHeaderString() report writer function and pass through the following parameters:

  1. Product ID: For Extender 3107
  2. Extender ID: In our case "ST_COMMENTS"
  3. Key Field: In our case IV_TRX_WORK_HDR.IV Document Number, but I will use Table.FieldName
  4. Not used: Integer constant of 0
  5. Field Number: In our case 1

So below is the definition of the Comment calculated field: 

Comment = FUNCTION_SCRIPT(  rw_TableHeaderString  3107  "ST_COMMENTS"  Table.FieldName  0  1  )

To break the 255 character string into 4 lines of up to 80 characters each the following calculated fields were also created using the RW_ParseString() report writer function:

Comment1 = FUNCTION_SCRIPT(  RW_ParseString  Comment  80  1 )

Comment2 = FUNCTION_SCRIPT(  RW_ParseString  Comment  80  2 )

Comment3 = FUNCTION_SCRIPT(  RW_ParseString  Comment  80  3 )

Comment4 = FUNCTION_SCRIPT ( RW_ParseString  Comment  80  4 )

In theory this should work perfectly. In practice it fails badly.

The problem is that there currently is a 80 character limit on the size of string calculated fields in report writer. This limit is discussed in the following post: Hybrid - Cheque Amount in Words Example and required additional code to be written to solve it for the one function: Announcing Report Writer Function RW_ConvertToWordsAndNumbersParse. The problem report number for the 80 character limit is PR 5239.

The end result was that only the first 80 characters of the Extender long string was returned and so only the first line of the 4 Comment lines was populated. It was at this point that the support case was logged.

This is discussed on the following KB Article, but I have a better solution... read on:

When I took the case, I suggested a number of possible workaround solutions (some supported and some not supported):

  1. You can use Visual Basic for Applications (VBA) (if registered) with ADO to access the SQL data directly. This method is supported, but you would have to create your own function to break the 255 character string into lines of less than 80 characters (without cutting words in half).
     
  2. You can use Dexterity to write your own function to call rw_TableHeaderString() and RW_ParseString() and return your data in strings less than 80 characters. This is supported.
     
  3. You can use VBA with passthrough Sanscript to re-use the Dexterity functions for rw_TableHeaderString() and RW_ParseString() to save your lines into the  SY_User_Object_Store DUOS table. Then you can pick up the values from VBA using the DUOS commands. This method is not supported.
     
  4. You can use the Support Debugging Tool (SDT) to create a Runtime Execute script which can be called via rw_TableHeaderString(). This in turn could call rw_TableHeaderString() and RW_ParseString() to get your extender data in lines of less than 80 characters. This method is not supported and will require the SDT installed on all machines with a shared setup file.

We settled on the fourth option as the customer did not want additional VBA or Dexterity code and went through the short process of setting up a shared location for the SDT setup file, point the SDT to that location and setting it as the Administrator controlled location and setting Advanced Mode on by default. The steps for this are detailed in the SDT User Guide Manual (Debugger.pdf) and in the FAQ.

So using Runtime Execute (Microsoft Dynamics GP >> Tools >> Support Debugging Tool >> Options >> Runtime Execute), we created a Script ID using the same name as our Extender ID. We then used the Helper Function button to select the helper function: Template for rw_TableHeaderString. The template code use used to read in the parameters stored by the SDT when the rw_TableHeaderString() report writer function is called and to write the parameter to return as the result.

All you need to do is write the code to take the input parameters and return the result desired. In our case we just needed to use the same function to get Extender to return the Long String field (all 255 characters), and then parse the resulting string into lines returning the requested line. We hardcoded the line length as 80 characters and used the unused parameter 4 to specify the line number to be returned.

ExtenderString = rw_TableHeaderString(3107, "ST_COMMENTS", MBS_Number, 0, MBS_Control);
MBS_TableHeaderString = RW_ParseString(ExtenderString, 80, MBS_Type);

Below is a screenshot of the complete script in the Runtime Execute window as well as the script itself as text:

 

Runtime Execute Code

local string MBS_TableHeaderString;
local string MBS_Number;
local integer MBS_Type;
local integer MBS_Control;
local string MBS_String;

local string ExtenderString;

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 }
ExtenderString = rw_TableHeaderString(3107, "ST_COMMENTS", MBS_Number, 0, MBS_Control);
MBS_TableHeaderString = RW_ParseString(ExtenderString, 80, MBS_Type);
{ Add your code above here }

call with name "MBS_Param_Set" in dictionary 5261, "TableHeaderString", MBS_TableHeaderString;

 

The final step was to modify the report to call the ST_COMMENTS script in the Support Debugging Tool by changing the dictionary number from 3107 to 5261 and adding line number needed as parameter 4. The resulting calculated fields are shown below (changes in red): 

Comment1 = FUNCTION_SCRIPT( rw_TableHeaderString  5261  "ST_COMMENTS"  Table.FieldName  1  1 )

Comment2 = FUNCTION_SCRIPT( rw_TableHeaderString  5261  "ST_COMMENTS"  Table.FieldName  2  1 )

Comment3 = FUNCTION_SCRIPT( rw_TableHeaderString  5261  "ST_COMMENTS"  Table.FieldName  3  1 )

Comment4 = FUNCTION_SCRIPT( rw_TableHeaderString  5261  "ST_COMMENTS"  Table.FieldName  4  1 )

So now when the report prints, it calls our script which in turn calls the Extender script to get the Long String fields, then our script parses the long string into lines and returns the individual line requested. 

That's all folks.

 

[Edit] This example is now available as an attachement at the bottom of this article.
 

For more information on Report Writer functions have a look at these related posts:

Enjoy.

David 

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)

06-Dec-2011: Added Knowledge Base (KB) Article 927390 which mentions 80 character issue. Changed KB links to public URLs.

05-Mar-2012: Added Example code in attachment.

09-Jul-2012: Added Link to blog article discussing Support Debugging Tool and Report Writer functions.

Debugger Settings Stock Transfer Comments Long String.dbg.zip