Highlighting a phrase within a larger field

Wednesday 27 May 2020 @ 1:58 pm

One of my readers shared a solution he recently found for highlighting a specific string within a larger field (like a comment, notes or memo field). To provide an example, I wrote a report against the Xtreme database and highlighted the word “graduate” the first time it occurs in the employee notes field.

There are two parts to his approach. First you write 3 formulas that split the memo field into three separate pieces: the target string, everything before it, and everything after it. Then you reassemble these three pieces by dropping them into a single text object. The target string formula can then be formatted separately so that it stands out within the text.

I used 3 additional formulas to make the process more generic. Here are the formulas I used:

//Target String - replace with your own target
"graduate"

//Target Start - replace Notes field with your Notes field
Instr ({Employee.Notes}, {@Target String})

//Target End
if {@Target Start} = 0
then 1
else {@Target Start} + Length ({@Target String}) -1

//Output Before- replace Notes field with your Notes field
if {@Target Start} <= 1
then ""
else {Employee.Notes} [1 to {@Target Start}-1]

//Output Target - replace Notes field with your Notes field
if {@Target Start} = 0
then ""
else {Employee.Notes} [{@Target Start} to {@Target End}]

//Output After - replace Notes field with your Notes field
{Employee.Notes} [{@Target End}+1 to -1]

The 3 “Output” formulas are dropped into the text object. Make sure the text object is set to “Can Grow”.

The process can be made more sophisticated if the target string varies in length, as long as you can clearly identify the character pattern that marks the beginning and the end of the target string. If you need help with that let me know and we can schedule a short session.

There is also an alternate approach where you embed HTML tags before and after the target string and then use Crystal’s HTML interpretation to change the format at runtime. However, this is limited to the HTML tags that Crystal supports.

Finally, thanks to Doug Weiner at Beacon Legal Software Services for suggesting this post.

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







Leave a Reply

Recrystallize Pro