12 Replies Latest reply on Mar 29, 2016 6:19 AM by user23934

    Highlight Specific Words with Conditional Formatting

    StanW

      Hi

       

      I have a list of about 50 key words. I would like to have each specific instance of each word highlighted (by background color or text color) wherever it occurs in a field. So I would have the contents of a field automatically looking like this (assuming 2 of the key words were "list" and "describe":

       

      List at least 5 professional organizations and describe their purpose

       

      I suppose this might be doable by using a Custom Function and Conditional Formatting. I have tried a couple of Custom Functions, but I could get neither to work.

       

      Any ideas?

       

      Thanks!

        • 2. Re: Highlight Specific Words with Conditional Formatting
          philmodjunk

          Conditional formats apply formatting to the entire field or block of text, not individual words, so that won't work.

           

          In theory, you can can use the substitute function in combination with text formatting functions to do this in a calculation field, but in practice it get's pretty ugly--especially since subsitute matches subtext in a case sensitive manner.

          • 3. Re: Highlight Specific Words with Conditional Formatting
            StanW

            This is one of the custom functions I tried. I could not get it to work. Probably operator error

            • 4. Re: Highlight Specific Words with Conditional Formatting
              StanW

              This is how I tried it. I added the custom function. Under Conditional Formatting I added "formula is" HiliteMulti ( Self  ;  "list¶describe"  ;  RGB (255;0;0) ). I left all of the conditional formats (bold, underline, etc) unchecked. It does not highlight the words

               

              I am I doing something wrong?

              • 5. Re: Highlight Specific Words with Conditional Formatting
                comment

                weberst@u.washington.edu wrote:

                 

                I am I doing something wrong?

                 

                Yes. You cannot do this with conditional formatting - custom function or not. Try adding a calculation field (result is Text) with a similar formula, e.g.=

                 

                HiliteMulti ( YourTextField ; "list¶describe" ; RGB (255;0;0) )
                
                • 6. Re: Highlight Specific Words with Conditional Formatting
                  AlanStirling

                  Hi weberst (Can I call you 'Tom'?)

                   

                  I have played with highlighting text in the past and it can be quite nerve-racking! 

                   

                  This depends if you want the highlighting to show immediately on text that has just been entered, or if you can use a calculated version of the text field (which obviously cannot be changed) to display the highlighted version of the text.

                   

                  In the former case, you will find that you have to do calculations on the text that has been entered in a 'destructive' way, whereas in the latter case, you would not be changing the source text, just applying a calculation to it.

                   

                  Either way, you need to learn how to use the following functions;

                   

                  Substitute ( text ; searchString ; replaceString )

                  TextColor ( text ; RGB ( red ; green ; blue ))

                   

                  These can be strung together as follows in a Set Field Calculation (using the example text from your first post);

                   

                  Set Field[Result] to

                  Substitute ( ExampleText ;[ "List" ; TextColor ( "List" ; RGB ( 255 ; 0 ; 0 ))];[ "describe" ; TextColor ( "describe" ; RGB ( 255 ; 0 ; 0 ))])

                   

                  Each word that you need to highlight would need to be included in the above calculation by adding the following element inside the final closing bracket of the above calculation.

                   

                  ;[ "NextWord" ; TextColor ( "NextWord" ; RGB ( 255 ; 0 ; 0 ))]

                   

                  If you have 50 words, then you will need to insert 50 of these elements - it could get quite slow to calculate the result if your text field is long!

                   

                  I apologise if you find a typo in my text above, but I have not had the time or opportunity to test these calculations, but I wanted to show you a method that I have used in the past.

                   

                  So, Tom, I hope this helps you set up your highlighted text.

                   

                  Best wishes - Alan Stirling, London UK

                   

                  (Further apologies if I have inadvertently left a colon in the text and this email becomes truncated from there on. Since emails I send to this list do NOT get echoed back to me, I will just never know if it got mangled or not!)

                   

                   

                  On 20 Jan 2012, at 00:32:20, weberst@u.washington.edu wrote

                   

                   

                  created by weberst@u.washington.edu in Advanced Discussion - View the full discussion

                  Hi

                   

                   

                  I have a list of about 50 key words. I would like to have each specific instance of each word highlighted (by background color or text color)  wherever it occurs in a field. So I would have the contents of a field automatically looking like this (assuming 2 of the key words were "list" and "describe":

                   

                   

                         List at least 5 professional organizations and describe their purpose

                   

                  I suppose this might be doable by using a Custom Function and Conditional Formatting. I have tried a couple of Custom Functions, but I could get neither to work.

                   

                   

                  Any ideas?

                   

                   

                  Thanks!

                   

                  Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                  Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

                  Manage your email preferences.

                   

                  FileMaker Developer Conference 2012 • Miami, Florida • July 16-19 • www.filemaker.com/devcon

                   

                  • 7. Re: Highlight Specific Words with Conditional Formatting
                    LyndsayHowarth

                    Yes, Alan... that is where I would have gone with this too.

                     

                    In a single field... the recalculation and substitutions could take place using a script trigger onExiting the field.. to reformat what you've just typed... that way the formatting is there when you go back in to edit it.

                     

                    - Lyndsay

                    • 8. Re: Highlight Specific Words with Conditional Formatting
                      comment

                      Lyndsay Howarth wrote:

                       

                      In a single field... the recalculation and substitutions could take place using a script trigger onExiting the field.. to reformat what you've just typed... that way the formatting is there when you go back in to edit it.

                       

                      What if one user needs to highlight "list" and "describe" while another wants "professional" and "purpose"?

                      • 9. Re: Highlight Specific Words with Conditional Formatting

                        Hi, here is another custom function. http://www.fmfunctions.com/functions_display_record.php?functionId=268

                        Sorry I didn't test yours Comment, but if I'm correct, the differences are :

                        - does not change the case

                        - use styles instead of colors. (you can use the yellow highlight stuff with FM 11)

                        • 10. Re: Highlight Specific Words with Conditional Formatting
                          comment

                          Hi Fabrice,

                           

                          AFAICT, your function is practically identical to my HiliteSingle() function:

                          http://www.briandunning.com/cf/578

                           

                          which is then used in turn in the HiliteMulti() function I suggested earlier. The yellow highlight wasn't available back in 2006 - in any case it's a trival adjustment.

                          • 11. Re: Highlight Specific Words with Conditional Formatting
                            StanW

                            Thanks for all your suggestions.

                             

                            I solved the problem by using the following as an Auto-enter Calculation on MyField. It works ok, but as you pointed out it highlights word stems ("described" has the "describe" highlighted, but not the "d"). It is actually very fast. I cannot tell that it adds any time at all. When I enter the text and tab out, the words are highlighted instantly.

                             

                            Substitute ( MyField ;

                             

                                [ "Define" ; TextStyleAdd ( "Define" ; Bold+ HighlightYellow)] ;

                             

                                [ "define" ; TextStyleAdd ( "define" ; Bold+ HighlightYellow)] ;

                             

                                [ "Describe" ; TextStyleAdd ( "Describe" ; Bold+ HighlightYellow)] ;

                             

                                [ "describe" ; TextStyleAdd ( "describe" ; Bold+ HighlightYellow)] ;

                             

                                [ "Identify" ; TextStyleAdd ( "Identify" ; Bold+ HighlightYellow)] ;

                             

                                [ "identify" ; TextStyleAdd ( "identify" ; Bold+ HighlightYellow)] ;

                             

                                [ "Label" ; TextStyleAdd ( "Label" ; Bold+ HighlightYellow)] ;

                             

                                [ "label" ; TextStyleAdd ( "label" ; Bold+ HighlightYellow)] ;

                             

                                [ "List" ; TextStyleAdd ( "List" ; Bold+ HighlightYellow)] ;

                             

                                [ "list" ; TextStyleAdd ( "list" ; Bold+ HighlightYellow)] ;

                             

                               ...

                            )

                             

                            Stan

                            • 12. Re: Highlight Specific Words with Conditional Formatting
                              user23934

                              Sorry to drag out this very ancient thread, but I am currently pulling my hair out trying to work out what I'm doing wrong with this custom function.

                               

                              I've actually tried both Michael Horak's HiliteSingle function from briandunning.com, and Fabrice's TextHighlightWithStyles function, and I get the same problem - only the first instance of the search term is highlighted, then the rest of the text string is replaced by the search term itself!

                               

                              It must be something I'm doing wrong, because I haven't seen anyone raising this error, but I am at my wits end to know what it is. I've tested in both v14 and 11 - the behaviour is the same.

                               

                              Example:

                              My Calc field definition: HiliteSingle( Get ( QuickFindText ); shotlist)

                              Quicksearch: Latham

                              Shotlist field value (standard text field):

                              Int mcu Australian opposition leader MARK LATHAM iv sot

                              Ext walk LATHAM crossing road and entering studio sot

                              Int walk LATHAM entering studio sot

                              Int vs LATHAM iv sot

                               

                              Calc Result:

                              Int mcu Australian opposition leader MARK LATHAMlatham

                               

                              The result from the TextHighlightWithStyles is almost identical, but instead of the search term appearing, a number does!

                              Int mcu Australian opposition leader MARK LATHAM768


                              Can anyone shed any light on what on earth I'm doing wrong??

                               

                              Thanks, Caitlin

                               

                               

                              Nb. This is Michael's function:

                              */

                              Let ( [

                              // TEST FOR MATCH

                              pos = Position ( text ; searchString ; 1 ; 1 )

                              ] ;

                              // IF MATCH IS FOUND

                              Case ( pos ;

                              // BEGIN CALCULATION

                              Let ( [

                              textBefore = Left ( text ; pos - 1 ) ;

                              len = Length ( searchString ) ;

                              string = Middle ( text ; pos ; len ) ;

                              textAfter = Right ( text ; Length ( text ) - pos - len + 1 )

                              ] ;

                              textBefore & TextStyleAdd ( string ; HighlightYellow ) &HiliteSingle( textAfter;searchString)

                              ) ;

                              // ELSE RETURN TEXT

                              text )

                              )

                               

                              I tried replacing &HiliteSingle( textAfter;searchString) with just textAfter which confirmed that value is correct, but how to get the subsequent highlights to work?