Share via


Working with Text fields in Report Writer

David Meego - Click for blog homepageThis 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: 

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.

Comments

  • Anonymous
    January 26, 2011
    Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com/.../working-with-text-fields-in-report.html

  • Anonymous
    November 13, 2014
    Hi David, this is a very helpful post! I am trying to pull in the additional notes into a Project Accounting billing format using RW_GetNoteText using the Billing Master Notes Index. I am having issues pulling the fields; they are blank.  Any ideas?