Highlighting Parameter Search Results in Text Field 1

[MK_USF]

This is stumping me. The following is the formula I'm using to highlight key words I'm searching a text field for in my database. I need it to highlight/color the word each time it is found in a sentence.

The table and field is {ChatMessages.MESSAGE}
The prompt is {?Text_To_Search_for}
I have set text interpretation in format field to HTML

local stringvar notes:=lcase({ChatMessages.MESSAGE});
local numbervar i;
for i:=1 to count({?Text_To_Search_for}) do (
notes:=replace(notes,{?Text_To_Search_for},"<font color=#ff0000>" & {?Text_To_Search_for} & "</font>",1,-1,1);
);
notes

Can someone please assist me with this? Any direction/clarification is greatly appreciated. Thank you.

Mike

 

[BettyJ]

I believe the following link will help you:

http://www.tek-tips.com/viewthread.cfm?qid=1212085

 

[MK_USF]

Thank you. I thought the solution was here. It errors "Local StringVar ValueToSearch := {?Text_To_Search_for};" and says "This array must be subscripted..." I'm simply plugging in my database.field and the parameter. Any idea why its failing?

 

[BettyJ]

Try the following:

[tt]replace(lowercase({ChatMessages.MESSAGE}), {?Text_To_Search_for},"<font color=#ff0000>" & {?Text_To_Search_for} & "</font>")[/tt]

 

[MK_USF]

Nope. Still states this array must be subscripted. I subscripted your code as follows and replaced the line in my code:

replace(lowercase({ChatMessages.MESSAGE}), {?Text_To_Search_for},"<font color=#ff0000>" & {?Text_To_Search_for} & "</font>")
);

I got no errors but the text shows with no highlight

 

[pmax9999]

I did it this way, and it returned the result I think you are looking for:

Replace({Table.Field}, {?Text_To_Search_for}, '<font color=#ff0000>' + {?Text_To_Search_for} + '</font>')

I suspect the error message you are getting is because your parameter allows for multiple values. If that is actually required you would need to loop through for the text for each of the parameter entries, like this:

WhilePrintingRecords;
Local NumberVar i;
Local StringVar RESULT := {Table.Field};

For i := 1 to UBound({?Text_To_Search_for}) Do
(
    RESULT := Replace(RESULT, {?Text_To_Search_for}[i], '<font color=#ff0000>' + {?Text_To_Search_for}[i] + '</font>')
);

RESULT

Hope this helps.


Cheers
Pete

 

[MK_USF]

Thank you, Pete. I modified my code to match yours:

WhilePrintingRecords;
local numbervar i;
Local stringvar Note := lcase({ChatMessages.MESSAGE});
for i:=1 to count({?Text_To_Search_for}) do (
Note:= Replace(Note, {?Text_To_Search_for},'<font color=#ff0000>' + {?Text_To_Search_for} + '</font>')
);
Note

I replaced {table.field} with the text field from my database that I want to search {ChatMessages.MESSAGE}. Still did not work. I copied yours verbatim making the one change to {table.field} . Still didn't work.

I believe placing it in the Details section is the correct location and I simply suppress duplicates. I used ubound and count but didn't make a difference. Betty, Pete any other ideas? I didn't think this would so complicated.

 

[BettyJ]

Have you formatted the formula to HTML text? If not, change the text interpretation by right clicking the formula; select Format Field->Paragraph(tab)->Text Interpretation:[tt]HTML text.[/tt]
I forgot to specify it in my previous post, but in the link I first sent, it was mentioned in the post by lbass.

 

[MK_USF]

Yes. I changed to HTML text interpretation. This has me stumped.

 

[lbass]

I tested your last formula and it worked. Be sure that you are putting the formula on your report instead of the message field, and that you are formatting the formula to HTML text interpretation.

-LB

 

[MK_USF]

Thank you. Now I really am lost. I created a formula called "highlight". I put it in the details section of the report. I get no errors but the result displays in black font. Is there another formula I could try?

 

[BettyJ]

Try creating a new report and add the following formula:

@highlight

[tt]stringvar txt:="Hello World!";
replace(txt, "Hello","<font color=#ff0000>" & "Hello" & "</font>")[/tt]

Right click the formula; select Format Field->Paragraph(tab)->Text Interpretation: HTML text and see if Hello is displayed in Red font.

 

[pmax9999]

I tested my solution before posting so I know it should work.

If you are prepared to post a copy of your report file (with saved data) I'd be happy to take a look.


Cheers
Pete

 

[MK_USF]

Betty,

Your formula works. Hello is in red.

Pmax9999,

Unfortunately, I can't.

Question: I have 174 search terms. In the "default" list of my prompt, I imported a .txt file containing the terms surrounded with an *. The report works but it does not color those search terms red.

When I removed the * from both ends of the term, the report finds nothing.

To account for the *, I put {ChatMessages.MESSAGE} like "*"+{?Text_To_Search_for}+"*" in the select expert. This produced a hit on everything in the database including the search terms so that doesn't work.

Could this be my problem?

 

[pmax9999]

Is the parameter being used for both record selection and for your search? Is the reason for having the wildcard at both ends so that the report returns all records where the field contains the particular words?

Out of curiosity, can you create a second parameter that doesn't include the wildcard, and use that in the highlighting formula. If that works, we can look at a solution that will strip the wildcard out of the parameter when it is being used in the highlight formula.



Cheers
Pete

 

[pmax9999]

The following code will strip the wildcards from the parameter in the search/highlight formula:

WhilePrintingRecords;
Local NumberVar i;
Local StringVar RESULT := {Table.Field};


For i := 1 to UBound({?Text_To_Search_for}) Do
(
    RESULT := Replace(RESULT, Replace({?Text_To_Search_for}[i], '*', ''), '<font color=#ff0000>' + Replace({?Text_To_Search_for}[i], '*', '') + '</font>')
);

RESULT

Please let us know how this goes.

Cheers
Pete

 

[MK_USF]

Thank you, thank you, thank you.

Here is the final formula:

WhilePrintingRecords;
local numbervar i;
Local stringvar Note := lcase({ChatMessages.MESSAGE});
for i:=1 to ubound({?Text_To_Search_for}) do (
Note := Replace(Note, Replace(lcase({?Text_To_Search_for}), '*', ''), '<font color=#ff0000>' + Replace(lcase({?Text_To_Search_for}), '*', '') + '</font>')
);
Note

It was the * and lcase that I needed to deal with.

 

[pmax9999]

Glad it helped.

Cheers
Pete