HIGHLIGHT SEARCH WORDS

[Kim296]

I would like to know if anyone else has figured out a good way to highlight "search words" in their reports. I am using Crystal Reports XI. I had to convert my notes section into a string. The following is how I find reports with the desired information:

IF INSTR (UPPERCASE({@NOTES}), UPPERCASE({?SEARCH1}))>0 THEN TRUE ELSE FALSE

This formula works well to pull back the desired reports, but sometimes it's still a long process finding the information that I am looking for. Now, I would like to take it a step futher and highlight the parameter "search word" in the queried results. Does anyone know how I may accomplish this task?

 

[pmax9999]

Hi Kim296

The only way I can think of to achieve what you want is to use HTML code.

I am no expert with HTML, but using the following code BOLDS and UNDELINES the search term (sadly, that is about the extent of my HTML coding knowledge).

1. Create the following formula and place it in the report instead of the actual the field:

WhilePrintingRecords;

Local NumberVar i;
Global StringVar OUTPUT;

For i := 1 to Ubound(Split({Table.Data}, {?Search})) Do
Output := Output + Split({Table.Data}, {?Search})[i] + 
If      i <> Ubound(Split({Table.Data}, {?Search}))
Then    '<b><u>' + {?Search} + '</u></b>';

Output

2. Change the formatting of the formula field to display:
[ul]
[li]Right click on field[/li]
[li]Select Format Field[/li]
[li]Click on Paragraph Tab[/li]
[li]Amend Text Interpretation to HTML Text[/li]
[/ul]

With the correct use of HTML you could change text colour and probably highlight the search word as well.


Hope this helps

Cheers
Pete

 

[BettyJ]

Hi Pete,
Didn't even know that such kind of HTML formatting could be done in HTML. Thank you.

I was trying to create a report to see how it works. This is how I did it.

My field is {NAME} . I tried to highlight all names containing for eg: 'John'

Create a static parameter SearchName (when run and prompted enter John)
Create 3 formulas

@SearchText
"*"+{?SearchName}+"*"

@Result
{NAME}

@BoldRedColor
whileprintingrecords;
replace({@Result},{?SearchName}, '<FONT COLOR="RED"><b>'+{?SearchName}+'</b></FONT>')

Selection criteria
{NAME} like {@SearchText}

Place @Bold in Details section

I hope I didn't miss anything.

 

[BettyJ]

Hi Pete,
Didn't even know that such kind of HTML formatting could be done in HTML. Thank you.

I was trying to create a report to see how it works. This is how I did it.

My field is {NAME} . I tried to highlight all names containing for eg: 'John'

Create a static parameter SearchName (when run and prompted enter John)
Create 3 formulas

@SearchText
"*"+{?SearchName}+"*"

@Result
{NAME}

@BoldRedColor
whileprintingrecords;
replace({@Result},{?SearchName}, '<FONT COLOR="RED"><b>'+{?SearchName}+'</b></FONT>')

Selection criteria
{NAME} like {@SearchText}

Place @Bold in Details section

I hope I didn't miss anything.

 

[Kim296]

Thank you Pete,

What you wrote looks similar to other code that I've seen. I must be doing something wrong because it pulls back the right text, but doesn't bold/underline or color the search word. I changed the code a little bit. Do you see an obvious mistakes that would prevent it from highlighting the search word red?

WhilePrintingRecords;

Local NumberVar i;
Global StringVar OUTPUT;

For i := 1 to Ubound(Split({@NOTES}, {?Search1})) Do
Output := Output + Split({@NOTES}, {?Search1}) +
If i <> Ubound(Split({@NOTES}, {?Search1}))
Then '<font color="red">' + {?Search1} + '</font>';

Output



Thanks,
Kim

 

[Kim296]

Oh, and I did change it to view as HTML Text

 

[Kim296]

This works to change the font color to red for my search word.

Shared StringVar SearchText := {@SEARCH1};
StringVar Htm1 := "<font color=#800000>";
StringVar Htm2 := "</font>";
StringVar Result := "";
StringVar Temp := "";
NumberVar Start := 1;
NumberVar Ln := Len(SearchText);
NumberVar Loc := Instr({@NOTES}, SearchText);
While Loc > 0 Do (
Temp := Mid({@NOTES}, Start, Loc - Start) + Htm1 + Mid({@NOTES}, Loc, Ln) & Htm2;
Result := Result + Temp;
Start := Loc + Ln;
Loc := Instr(Start, {@NOTES}, SearchText);
);
Temp := Mid({@NOTES}, Start);
Result := Result + Temp;
Result

 

[pmax9999]

One potential problem with your code is that it will only find the first instance of the search string, whereas my code will find and highlight all instances of the search string.

The code I provided was tested and did work. I have updated it to include the HTML code to make it RED.

WhilePrintingRecords;

Local NumberVar i;
Global StringVar OUTPUT;

For i := 1 to Ubound(Split({Table.Data}, {?Search})) Do
Output := Output + Split({Table.Data}, {?Search})[i] + 
If      i <> Ubound(Split({Table.Data}, {?Search}))
Then    '<b><font color="red">' + {?Search} + '</font></b>';

OUTPUT

The code can be simplified further using BettyJ's approach of using Replace.

Hope this helps

Cheers
Pete

 

[Kim296]

Thank you Betty J and Pete. I really appreciate both of you responding!! This forum helps me alot as I try to learn new things.

 

[Kim296]

Pete,
I have tried your code and Betty J's code on my computer and they both pull back the correct information for my query, but they do not change my search words to bold or red. I do understand that you have tested the code and it works on your report.I am baffled as to why it works for you but not me. I am using Crystal Reports XI, do you think that may be the reason?

The other code that I found and posted above does change the color of the search word in my query.

I really don't know how to change any of the codes to bold all search words that are found in my report text {?SEARCH1},{?SEARCH2},{?SEARCH3}, etc... All the codes above are for a single search word. Is there any online reference material that I can study, so that I understand what all this code means (structure and format). If I can read the right learning material, I may be able to understand why and how you are designing the code. I would love to learn how you come up with this code. Is it a specific language I can study?

Thank you,
Kim

 

[BettyJ]

Hi Kim,
Have you done the following.
Change the formatting of the formula field to display:

•Right click on field
•Select Format Field
•Click on Paragraph Tab
•Amend Text Interpretation to HTML Text

 

[Kim296]

Hi Betty,

Yes, I have done that. Your formula pulls back the correct query information, but doesn't change the font or bold with your formula. That's what's so puzzleing to me. The only thing that I couldn't figure out about your code is the last part. Maybe that's what the difference is.

I created @SearchText, @Result & @BoldRedColor. I had to convert my notes field from a "memo" to a "string" to use with my searchword parameter; my searchable field is {@NOTES} and not a regular table. So, I didn't do anything with the following part of your code because I couldn't figure out what to do.

Selection criteria
{NAME} like {@SearchText}

Do you think this is where my error is?

 

[BettyJ]

Can you try to take the notes field (instead of {@NOTES}) from your database and replace whereever I have {NAME}with your notesfield. Search Criteria should be included.

Also I think I forgot to write earlier that @Result should be placed in the Details section(and can be suppressed).

 

[BettyJ]

Hi Kim,
Combining Pete's formula in this forum and another formula in this forum I was able to get what you were trying to do.

I have used the Employees table from Northwind.mdb (MS Acccess sample db)

I have a parameter named SearchText(Allow multiple values - True)
I have three formulas
@SearchMutliple
whilereadingrecords;
numbervar i;
numbervar j := ubound({?SearchText});
stringvar x;

for i := 1 to j do(
if {Employees.Notes} like "*"+{?SearchText}+"*" then
x := x + {Employees.Notes} + ", "
);
{Employees.Notes} in x

@DisplayParameters
Join ({?SearchText},", ");

@BoldRed
WhilePrintingRecords;
Local NumberVar i;
StringVar color_Output;
stringvar array names:=split(Join ({?SearchText},", "),",");
Stringvar Array param_output;
Redim param_output[ubound(names)];
Local Numbervar param_i;
for param_i:=1 to ubound(names) do

(For i := 1 to Ubound(Split({Employees.Notes}, trim(names[param_i]))) Do
color_Output := color_Output + Split({Employees.Notes}, trim(names[param_i])) +
If i <> Ubound(Split({Employees.Notes}, trim(names[param_i])))
Then '<Font color=red><b><u>' + trim(names[param_i]) + '</u></b></font>';
);
color_Output ;

Record Selection Criteria
{@SearchMutliple}

 

[Kim296]

Betty J, I just want to thank you for all of the time that you've taken to help me figure this out. I'm going to print your last entry and study it when I go home tonight. I really appreciate your time; I will let you know in the am how it works with my reports.

Thank you,
Kim