Highlighting part of a text field if it is like the parameter

[hsandwick]

Crystal Reports 9
SQL Server 2000

I am retrieving results when I pass a report parameter and the result in a text field is like the parameter, as follows:

{MedicalRecordsforPlaintiffsDecedentsinaCase.Comment} like '*' +{?Search Comment}+'*'

I'd like to take this a step further, and highlight just the portion of the text field result that matches what was entered in the parameter. How can I achieve this, please?

Thank you!

Helen

 

[lbass]

Create a formula like:

whileprintingrecords;
stringvar array x := split({MedicalRecordsforPlaintiffsDecedentsinaCase.Comment},{?Search Comment};
numbervar i := 0;
numbervar j := ubound(x);
stringvar y := "";

for i := 1 to j do(
y := y + x +
(
if i <> j then
"<font color = red>"+{?Search Comment}+"</font color>")
);
y;

Then format this formula by going to format field->paragraph->text interpretation: HTML text.

-LB

 

[hsandwick]

This is really great! Thank you, lbass - I can see many applications for use of this scenario!

One quick question - when testing, I was playing around with Uppercase/Lowercase items, and wondered how to address these. The search pulls in all results where the parameter exists, for example, if I search for the word "the". It may appear anywhere in a sentence, including at the beginning which involves an initial Uppercase letter. How, please, can I highlight the results that have a mix of Uppercase/Lowercase - so far, I've had no success. It only retrieves whatever is EXACTLY typed in as the parameter.

Very much appreciated,

Helen

 

[lbass]

Retrieving all instances regardless of case is easy enough, but this method then plugs in the parameter value for the particular instance, so it would always reflect the case of the parameter. Would it be okay to not only color the font red but also then use all uppercase letters for the instance that is plugged back in?

-LB

 

[hsandwick]

Yes, that would be ok.

What I'm finding is that any text that has the parameter, in any form, does show in the report. For example, I retrieve paragraphs/sentences that include "The" or "the" anywhere. As the formatting currently stands, only "the" is highlighted red if that was exactly what was typed in the parameter. Instances of "The" show, but are not highlighted.

So, yes, I guess it would be more important to highlight all instances, whether they include Uppercase, Lowercase, or a mixture of both. The emphasis is for people to recognize the results quickly.

Thank you.

Helen

 

[lbass]

Here is a different approach, that searches for whole words. If the word was "Job", it would pick up "Jobs", "McJobs", etc., but at least you would get the result regardless of case.

whileprintingrecords;
stringvar array x := split({MedicalRecordsforPlaintiffsDecedentsinaCase.Comment}," ");
numbervar i := 0;
numbervar j := ubound(x);
stringvar y := "";

for i := 1 to j do(
y := y + (
if ucase(x) like "*"+ucase({?Search Comment})+"*" then
"<font color = red>"+x+"</font color>" else
x) + " ");
trim;

Be sure to format it to HTML text.

-LB

 

[hsandwick]

Thank you, lbass. This works really well as long as the search is just for whole words, as you pointed out. I've been testing to find a way to combine your two examples so that I can highlight

one or multiple words that might or might have a mix of ucase/lcase, anywhere in the phrase ...

since I might want to search for, e.g., "No notes." or "no notes" or "Dr. McJob" or 'painless", etc.

No luck so far, but appreciate your help very much. Will keep testing, and let you know if I find a solution.

Helen

 

[hsandwick]

This works for any mix of ucase/lcase in a string ...

Create a Parameter, e.g., {?Search Comment}.
===========
Create a Formula, e.g., "Highlight Part of Text":
===========
Local StringVar Value := {Table.FieldContainingTextToBeSearched};

Local StringVar ValueToSearch := {?SearchComment};


Local StringVar TagStart := "<font color = blue>";
Local StringVar TagEnd := "</font color>";

Local StringVar Output := "";
Local NumberVar StartPosition;
Local NumberVar EndPosition;
Local NumberVar i;

// Search the string and add the tags around the searched value

If Length(ValueToSearch) <> 0 Then
(
While instr(Value,ValueToSearch,1) <> 0 Do
(
// Find the starting position of the word containing the searched value
StartPosition := inStr(Value,ValueToSearch,1);
i := StartPosition;

While i >= 1 Do
(
If Value = ' ' Then
(
StartPosition := i+1;
i := 0;
)
Else If i = 1 Then
(
StartPosition := 1;
i := 0;
)
Else
i := i - 1;
);

// Find the end position of the word containing the searched value
EndPosition := inStr(Value,ValueToSearch,1)+ Length(ValueToSearch)-1;
i := EndPosition;

While i <= Length(Value) Do
(
If Value in [' ','!','?','.',','] Then
(
EndPosition := i-1;
i := Length(Value)+1;
)
Else If i = Length(Value) Then
(
EndPosition := Length(Value);
i := Length(Value)+1;
)
Else
i := i + 1;
);

// Add the tags around the words
Output := Output + (If StartPosition > 1 Then Value[1 to StartPosition - 1]) +
TagStart + Value[StartPosition to EndPosition] + TagEnd;

// String remaining that will be searched to find any occurance of the searched value.
If EndPosition = Length(Value) Then
Value := ""
Else
Value := Value[EndPosition + 1 to Length(Value)];
);
);
// Display the result
Output + Value

Thanks goes to Rahul Singh at Business Objects for this solution!

Helen
===========