Some Notes about Multiline Text fields in Sage CRM.

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Some Notes about Multiline Text fields in Sage CRM.

  • Comments 13
  • Likes

I have written this article as a starting point for a discussion on the customization of Multiline Text fields in Sage CRM.  Please feel free to comment as it will help me expand this article.

It is worth explaining a couple of points about field size and the display of data in Multiline Text fields.  When you create a field of type 'Multiline Text' in Sage CRM it then gets created as field in the database.  In Sage CRM all Multiline Text fields get implemented as the physical data type 'ntext' in an SQL Server database.   NText is basically a 'one-size-fits-all' data type and can hold 1,073,741,823 bytes of data.  

At sometime in the future Sage CRM will replace all the remaining usages of ntext with nvarchar(max) as ntext is being deprecated in the database.  The process of changing the fields from using ntext started in Sage CRM v7.1.  One of the fields that changed from an ntext field to an nvarchar field was the lead_details column in the Lead Table.  This is still described as a Multiline Text entry.

Basically nvarchar(max) and ntext fields are experienced by the user in the same way.  These are fields that allow us to enter free form text and consequently there is no 'size' for them.  That is why we show a length '0' in the size column.

But the field size is not quite the same as Entry Width.  Which can be set for the field. For example the Width for the oppo_note field is '50' and for the Lead_details is '30'.  This Entry Width controls the width of the field displayed when in edit mode.

Sage CRM will automatically truncate all text fields, (nvarchar and ntext)to 255 characters when they are included in a List block. The truncation of Multiline Text data within the list blocks is done for display reasons and to keep the amount of HTML returned to the browser a reasonable size for performance reasons.  The truncation does not take place within the SQL but is hard coded within the dll. 

Comments
  • The Truncation should be parameterised! So a customer which do not care about performance would be able to deactivate it and have for example all E-Mail Data in one scrollable List (still paged).

  • Would you want to select the individual fields that you don't want truncated in lists?

  • I appreciate nvarchar over ntext as they allow for more TSQL syntax to be used against them. In some custom views I converted them to varchar before anyway.

  • How does this affect database storage size? ntext fields used t e treated differently and only ever consumed 16 bits in a table row. A nvarchar(max) field will consume drastically more than this and that may have a large effect on tables such as communictions.

    If ntext is being deprecated then we don't have much choice but an increase in disk space requirements is omethignto be aware of.

  • Jon

    The reality is that what ever we feel are the advantages of ntext and text fields, we can not avoid that these data types are deprecated and we are told by Microsoft to "avoid using these data types in new development work, and plan to modify applications that currently use them".  cf. msdn.microsoft.com/.../ms187993.aspx

  • Yes Jeff, the crm is cutting of at 255 chars and then puts ... at the end of the text, but many customers do not want this behavior. Because they need to look inside a case for example to have the full details of the Problem. With a Function Parameter that says "Show xxx Charakters" or "Show All" as a checkbox would be very helpfull.

  • Hello Jeff, did this make it into the clientside API of 7.2? I want to 'Show all' on an out of the box listblock on a custom screen, and the only way I can think of doing it at the moment is hand craft something using a content block...?

  • What I have seen done is an 'expand all' tool tip.  Basically the API allows an cell in a table to be identified and then when the user hovers the mouse over it an Ajax call fetches the complete text of the note field to display on the screen.  The dll truncation of the fields didn't change in Sage CRM 7.2 so it isn't a question of 'widening' the column width in the display as there would not be more that 255 characters to show.

  • I appreciate the information about ntext vs nvarchar but my 7.1 SP2 multiline text fields are nchar(20) and there does not appear to be any way to increase the size.  BTW the text fields are nvarchar fields.

  • John

    community.sagecrm.com/.../converting-vanilla-ntext-fields-from-an-old-install-of-crm-to-nvarchar-max.aspx

    You should be able to change both the length and type of the fields.  See:  community.sagecrm.com/.../converting-vanilla-ntext-fields-from-an-old-install-of-crm-to-nvarchar-max.aspx

    Remember to back up and then test properly before doing anything on your live system.

  • hi , really interesting article. how can we use tooltip on list rows  to show more than 255 characters ( full data  of list ) ??

    Thanks

  • See this Forum thread for ideas on a ToolTip

    community.sagecrm.com/.../35329.aspx

  • And this article.