Working with Text fields in Report Writer

This post will explain what is and is not possible when working with fields of text datatype in the Microsoft Dynamics GP Report Writer.

The text field datatype in Dexterity allows multi-line text to be entered up to a maximum length of 32,000 characters.  A shorter keyable length maybe defined when the field is defined in Dexterity.

The following information is from the Dexterity help file and explains some of the limitations for using Text fields on both text reports and graphic reports:

When you add text fields to a report layout, you should be aware that they can display no more than 10K of data, even though text fields can hold up to 32K of data. Text fields should only be placed in the body of a report.

Resizing a text field will have different results, depending on whether the report is a graphics or text report. If you resize a text field in a graphics report, data will be printed to fill the resized area. However, if the text field is larger than the amount of data in that field for a given record, the unused spaced for that field will still be included in the report. If you resize a text field in a text report, the field width you specify will be used, but the field height will be adjusted automatically to accommodate the text in the text field, up to the 10K limit.

For example, you could resize a text field to be two inches wide and tall enough to include 18 rows of data. If the data in that field for a given record will fill only 12 rows, then only those 12 rows will be included if it’s a text report, while those 12 rows plus 6 blank rows will be included if it’s a graphics report. Similarly, if the data in that same field for a different record contains 20K of data, up to 10K will be included if it’s a text report, while only the first 18 rows will be included if it’s a graphics report.

A question that is often asked is how to have a text field automatically resize based on the size of the content?.

Well ... as described above, that is only possible for text reports when the text field is in the body of the report, but does have the limitation of only display the first 10K of the possible 32K characters.

No resizing is possible on a graphics report, if there is too much text to display, it will be truncated and if there is not enough text to display, blank space will be left on the report.

[Edit] For the above methods to work you need to be able to create a table relationship between the tables. If the relationships need to use a table in a 3rd party dictionary and the report is in the Dynamics core dictionary, you will not be able to create a cross dictionary relationship. If the report is in the 3rd party dictionary, you should be able to create a relationship to the Dynamics core tables. In short: A 3rd party dictionary knows about Dynamics, but Dynamics does not know about 3rd party dictionaries.

One of the other options for displaying data from text fields on a report is to use Report Writer functions in calculated fields to break the text field into multiple string calculated fields.  The string calculated field does have a limitation of only allowing a maximum 80 characters, but you can break a text field into a number of lines of up to 80 characters.

The following Report Writer functions are available for breaking a text field into lines without cutting words in half as well as retrieving text data from tables and then parsing the text into lines. 

NOTE: The functions that retrieve the text data from the table can be used without needing to attach the table to the report.  

RW Function Name	RW Function Description	Data from Table
RW_ParseString	Breaks a String or Text field into lines	N/A
RW_GetCommentText	Read the specified comment and break into lines	SY_Comment_MSTR
RW_GetInternetText	Read the specified internet information text and break into lines	coINetAddrs
RW_GetNoteText	Read the Note for the specified Note Index and break into lines	SY_Record_Notes_MSTR
RW_POPHDRCommentText	Read the specified POP Header comment and break into lines	POP_POComment
RW_POPLINECommentText	Read the specified POP Line comment and break into lines	POP_Comment
RW_SOPHDRCommentText	Read the specified SOP Header comment and break into lines	sopUsrDefWorkHist
RW_SOPLINECommentText	Read the specified SOP Line comment and break into lines	SOP_LINE_CMT_WORK_HIST
RW_ConvertToWordsAndNumbersParse*	Convert the specific currency amount into words and break into lines	N/A

 * Available in Microsoft Dynamics GP 2010 onwards only.

We will have some examples of how to call these functions in an upcoming article. 

For more information have a look at the following posts: 

• Vaidy Mohan's post: Displaying Note Text on a GP Report
   
• Report Writer Functions Reference: Using the built-in Report Writer Functions
   
• Item Description example: RW - Showing the Full Item Description on Invoices Example
   
• Using Support Debugging Tool: Using the Support Debugging Tool to assist with Report issues

I hope this explains the options available.

David

04-May-2011: Added link for Item Description example.

17-Jul-2013: Fixed broken link to Vaidy Mohan's blog, added note about cross dictionary table relationships, and added link to Support Debugging Tool Report Writer Functions post.