x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

Some Notes about Multiline Text fields in Sage CRM.

1 minute read time.

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.

  • 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.

  • 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...?

  • 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.

  • 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

  • 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.

Generic Content
Generic Content

*Community Hub is the new name for Sage City