8 Replies Latest reply on Oct 27, 2010 9:36 AM by JohnWolcott

    highlight certain records based on field content



      highlight certain records based on field content


      I would like to highlight all records in a database that have a particular radio button checked.  Ideally, all of those records would have a different background, or blinking text, or something to call attention and distinguish them from other records that do not have that radio button checked.  I need this to work both when viewing all records and when doing finds which happen to include some of those records.

      The database has about 800 records and about 60 need to be distinguished in some way so users know instantly that a particular record is special.  The radio button has three values, YES, IP, and NO.  I need to flag all the NO records.  The problem is that users are failing to notice when the NO radio button is checked.  The page layout is fairly dense, and I have made the 3 buttons as large as I can.  All fields are formatted at 10 point, and the the radio buttons are at 14 point and bold, but this still isn't enough for some users.

        • 1. Re: highlight certain records based on field content

          You can do this with conditional formatting.

          You can change the color of the text in one or more fields. You can change the fill color of one or more fields to Highlight them.

          1. Enter Layout Mode
          2. Select the field or fields.
          3. Right click a selected field and choose conditional format
          4. Select the by formula option and enter YourTable::YourRadioButtonField = "No"
          5. Then select the text or fill color you want in order to draw attention to records where the "No" value has been selected in the radio button field.


          Conditional formatting can also be specified for layout text and buttons.

          • 2. Re: highlight certain records based on field content

            Is there a way to make this work in IWP?

            • 3. Re: highlight certain records based on field content

              Not with conditional formatting, but there are work arounds--some first used years ago before conditional formatting was a reality.

              You can set up a gobal container field and store a rectangle in it of the color you want to use as a highlight color. Set up a calculation field that uses the same expression inside an If Function:

              If ( YourTable::YouRadioButtonField = "No" ; GlobalContainerField ; "" )

              Select "container" as this calculation field's return type and place the field on your layout in the background behind all your other fields and layout elements. You can use Data Formatting on the data tab of the inspector to specify a reduce or enlarge image to fit and clear the "maintain original proportions" option so that your rectangle allways fills the field no matter how you resize it.

              You can either load this global field with the rectangle before you put the file up on the server or you can use a non global field in a related table where you use X instead of = in the relationship so that every record in your original table matches to the same single record in the related table where you put your global field loaded with your highlight color.

              • 4. Re: highlight certain records based on field content

                I am stuck, and I don't know how to upload a jpg file that is on my desktop into this text box to show you the problem.  I have created the global field and the calculation field.  The global field works, meaning the rectangle is there with the fill color on every record.  The calculation field works if I put quotation marks around the name of the container field and specify the result will be text.  By that I mean the name of the container field appears in the calculation field if the record matches the IF statement.  But when I remove the quotation marks and change the result to Container, I do not get the colored rectangle in the calculation field.



                • 5. Re: highlight certain records based on field content

                  Amazing the typos I am finding in my posts these days...Embarassed

                  It should read: 

                  If ( YourTable::YouRadioButtonField = "No" ; YourTable::GlobalContainerField ; "" )

                  Oh yeah, and the last part: ; "" isn't strictly necessary. You can leave it out and get the same results.

                  If you were including the table name, try removing the If function so that you just have the global container field reference and see if the rectangle appears. If that works, something isn't right with the first part of your IF function or the record doesn't have the data in this field that you thought it did.

                  • 6. Re: highlight certain records based on field content

                    The typo isn't the problem. Whenever I use a field name in a calculation, I always select it from the list of field names in the calculation dialog box.  If I have multiple tables, it will automatically insert the table::field name.  In this case I tried adding the table name, but that didn't do anything.

                    I created a test database with 4 fields:
                    Name = text field
                    time_of_day = text field (value list with radio buttons and 3 values: morning, afternoon, evening)
                    background = container field with fill color
                    highlight = calculation field

                    If I set the calculation field to equal Name or time_of_day and specify the result is text, then the value of that field appears in the calculation field.  If I set the calculation field to equal background and specify the result is container, nothing appears in the calculation field.  I have made the size of the calculation field much larger than the container field.  When I have the calculation displaying either of the text fields, the inspector does not allow me to format the data.  If I have the calculation set to display the results as a container field, then I can uncheck the "Maintain original proportions" box, but this has no effect on the contents of the calculation field, which is empty.

                    To create the container field, I created the field, specified type as container and under options selected global storage.  Next I went to layout mode, selected the field, right clicked on the field (pc), and selected fill color.

                    I'm sure this is something really simple, but I cannot see it.

                    • 7. Re: highlight certain records based on field content

                      Did you copy and paste a colored rectangle into Background? You can create the rectangle in layout mode, copy it to the clipboard, enter browse mode and paste it into the container field.

                      If that doesn't do it, compare what you've done to this demo file and see what's different:


                      • 8. Re: highlight certain records based on field content

                        OK, the missing step was creating a colored rectangle, not a field, and not selecting the container field itself and chosing a fill color, but just creating a rectangle and filling that with color and copying it while in layout mode, then switching to browse mode and pasting it into the container field.  It took a bit to get rid of the text base lines.  And, as you noted above, because the file is hosted by filemaker server, I had to do all of this with the file not hosted so the global field would populate correctly.  It is now being hosted and working as a web page.  Thanks sticking with my problem until it was solved.