Sorting on a memo field.

Sunday 16 October 2016 @ 5:28 pm

A customer wanted to sort on one of the fields in his report. But when he went into the Record Sort Expert that field was not shown. So he sent me the report to troubleshoot. I noticed that the data type of that fields was Memo, which is a data type that has an unlimited length. Crystal can’t sort on a memo field regardless of the number of characters that are actually stored in the field. Crystal also can’t group on, or summarize a memo field.

But in this case, as in many cases, the memo field wasn’t being used to store paragraphs of text. It was being used to store a short description. So I showed him how to create a formula that returned only the first 40 characters of the memo field, like this:

{Order.Notes} [ 1 to 40 ]

This formula was more than wide enough to include all of the short description. And because it has a fixed length it can be used for sorting, grouping or summary fields. So now he can sort on his short description.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro