There was much rejoicing from the FileMaker community when the Conditional Formatting feature was introduced in FileMaker Pro 9. The pains associated with writing custom programming scripts for making a field show up in Bold or in a different colour is now a thing of the past. In the simplest approach, Conditional Formatting provides the ability to change the formatting of the text in a field or layout object, as long as the layout object has the ability to display text. One or more conditions can be defined, which will manipulate the displayed text, when the condition(s) are met.

For example, with a Date field, you can define a condition of ‘Value is before today’ and assign the formatting of bold and text is red, for showing that a date has gone past the current date. Very handy for showing payments that are past due.

You can then expand upon that by adding another condition that states ‘Value is more than 30 days passed’ and the formatting is bold, text is red, and the fill colour is yellow, Which really grabs visual attention when payments are months late. This kind of formatting makes it much easier to glean information from a complex table.

By using the ‘Formula Is’ you can create sophisticated calculated Conditions for formatting

So many options Using conditions based on dates is just the tip of the iceberg. You also have the ability to base conditions on Values of:

  • Between (two different values)
  • Not between
  • Equal to (a specific value)
  • Not equal to
  • Greater than
  • Less than
  • Greater than or equal to
  • Less than or equal to
  • Containing (not to be confused with Equal to)
  • Not containing
  • Beginning with
  • Ending with
  • Empty
  • Before today
  • Today
  • After today
  • More than ‘x’ days passed
  • More than ‘x’ days ahead
  • Less than ‘x’ days passed
  • Less than ‘x’ days ahead
  • All these conditions can be combined for the creation of a complex set of criteria. Such as:

  • Value is before today, text colour is black, and
  • Value is after today, text colour is red and
  • Value is more than 30 days passed, text is bold
  • Value is more than 60 days passed, fill colour is yellow.
  • One thing that you’ll notice here is each condition is appended to the previous condition if the result is true. This starts with the top condition. In this example, once the bottom condition is met then the resulting text will display as red, bold, and the background fill colour will be yellow. You can change the order of the conditions to meet your needs.

    If a condition in the list evaluates as ‘false’ then the associated formatting will not be appended to the previous formatting. However, any following conditions will continue to be evaluated.

    Date and number formats It’s important to note that if you are using the FileMaker Pro formatting features for dates and numbers, that these settings will override any Conditional Formatting that you have defined. Within the Number Format dialog for a number field, if you set the text colour to red for negative numbers, but you have a Conditional Format for the same field to use black text, then the Number Format will override.

    However, if you set a different format feature in the Conditional Format, such as setting the Fill Colour to light pink, then both formats will appear for a negative number (Red text & light pink fill colour).

    Customisation If the defined set of conditions is not enough, you can use the powerful “Formula Is” condition. This provides the ability to define a custom formula with any of the functions and operators available in the Calculation Editor of FileMaker Pro. As an example, if your database provides search results for a housing rental service, you could build a formula based upon the values of other fields in the database record. Such as:

    Properties::Bedrooms ≥ Globals::gnBedrooms and
    Properties::Baths ≥ Globals::gnBaths and
    Properties::Footage ≥ Globals::gnFootage and
    Properties::Floors ≥ Globals::gnFloors and
    Properties::Fireplace = Globals::gnFireplace and
    Properties::Yard = Globals::gnYard

    With this example, if the fields in the Properties table match the criteria specified in the global fields then the Properties::Description field can have the formatting of showing the text in green.

    If this field is showing in a list layout, then the records matching the criteria will show in green and the records that do not fit the criteria will show in the default formatting for that field.

    Via the use of ‘Formula Is’, this list will format according to the selected criteria in the global fields

    The use of the ‘Formula Is’ condition can be combined with any of the fixed condition values as well. And the use of multiple ‘Formula Is’ conditions combined with multiple fixed condition values will place you in a whole new level of FileMaker geekyness.

    Impact on performance
    The power of Conditional Formatting comes at a cost. While it’s fun to use conditional formatting on your FileMaker layouts, remember that each condition is a calculation that FileMaker has to process.

    This requires additional clock cycles and can impact the performance of displaying data. If you have a layout with 40 fields and layout objects, and you have a conditional format defined for each of those objects, then you’re going to notice a performance hit every time you refresh the layout or move to a new record.

    Also be aware that if your Conditional Format is based on a ‘Formula Is’ and the calculation contains a reference to a field in a related table, then you can impact how quickly the object can be calculated and then displayed on your layout.

    The best rule of thumb is to test the use of the Conditional Formatting before you turn it loose on your users.

    High usability factor Keeping in mind the impact on performance if overused, the Conditional Formatting feature is one of the best, and highly utilised additions to FileMaker Pro version 9. It provides the ability to improve the usability of your FileMaker applications by visually showing various conditions and states of your data.