Conditional formatting is a great tool to enhance your layouts and add additional functionality to your solutions, e.g. for calendaring. But sometimes conditional formatting can slow down the display of your layouts, because FileMaker has to calculate all the conditions you have defined. Today we are going to look at a technique that allows you to speed up your calculations by caching some results temporarily.

Our example
Download the example files: File 1 File 2

We have built a small calendar, that shows Cynthia's appointments. The calendar is built with a repeating field with 42 repetitions.


Conditional formatting
The calendar is built with a couple of custom functions, but that is not important for this exercise. To display the appointments we are using conditional formatting.


We have defined two conditional formatting rules: one for displaying the weekends in blue and one for displaying the appointments in pink. FileMaker will always calculate both rules starting from top to bottom and merging the format of all rules that are true. If we have an appointment in the weekend, the field will be pink. If we had switched both rules, it would have been blue.
Let’s look into the detail of the conditional formatting rule of “Highlite Days”.


In the conditional formatting rule, we check if the date displayed in one repetition belongs to the related appointments. We are using the List function. The list function will get all related dates and give it back as a value list.
Since we have 42 repetitions in, FileMaker will calculate this list function 42 times, which will result in 42 times the same list. Imagine we would display the calendar of a whole year, then FileMaker would have to calculate the same list 504 times (12 x 42).

Caching
Instead of calculating the list 42 times (or 504 in case of the year view), we can calculate the list once and put it in a global script variable. Then we can use the global script variable in our conditional formatting and at the end we will clear the global script variable, to be sure we don’t have any mistakes when creating new appointments or switching to other layouts.
To create and clear the global script variable, we will not use any script or plug-in. The only tool we will be using is the conditional formating. In order to understand what we will do, we need to have a closer look at how FileMaker is calculating the conditional formatting.
FileMaker evaluates the objects one by one. Starting with the objects in the back and ending with the frontmost objects. We can take advantage of this behavior, by creating an object that will create the cache in the back and a second object on the top layer that will clear the cache.


Creating the cache
On the bottom layer object, we have added conditional formatting. We check first to see whether there is already a cache, and fill the global script variables “$$DateList” with the list.

Displaying the calendar
The formula is almost the same as our first example. We have only replaced the list function by the global script variable “$$DateList”.


Clearing the cache
The object on top also gets conditional formatting.
We check whether there is a cache and clear the $$DateList.

Conclusion
By using this technique you can speed up your layouts that use a lot of recurring calculations. In a solution of one of our customers we could speed the time it takes for the layout to be drawn from 0,5 seconds to 0,02 seconds. We used the mFMb_DS_GetSeconds function from the myFMbutler DoScript plug-in to measure this.
A little side effect, since we clear the global script variable immediately, the global script variable will never appear in the data viewer. To debug, you can disable the conditional formatting of the clear cache object.

Download the example

This example requires that the DoSQL plug-in be installed. You can download DoSQL here.

Download the Clock example

Ever wanted to create your own navigation system based on your layout names? With the introduction of conditional formatting, this has now become a piece of cake.

This example shows you how you can implement a simple navigation system in your solution.

Download the example file.

Step 1. Determine the completion status of your script.

The completion of a script can be represented by a percentage. In FileMaker there are some functions that can be used to determine the completion of a script.
If you for instance want to loop through all your records, you can use the Get(FoundCount) and the Get(RecordNumber) to calculate the completion.

Let
(
[
thecurrentStep = Get(RecordNumber)
;
theTotalNumberOfSteps = Get(FoundCount)
;
theCompletion = Truncate ( (thecurrentStep/theTotalNumberOfSteps) * 100 ; 0)
]
;
theCompletion
)


If your table has 300 records and you are currently on record 100, the formula will return 33. This means you have already accomplished 33 % of your script. The truncate function is used to be sure the formula will return an integer.

Step 2. Drawing a progress bar

In the next step, we will create the progress bar.

- Go to "Manage > Database" and create a new text field "progress bar".
- Select the field and choose "Options...".
- Click on the tab "Storage"
- Make the field global and fill in the number of maximum of repetitions: 100

Now we have a repeating field with 100 repetitions. Every repetition will represent one percentage of the progress of the script.

Add the field to your layout and be sure all 100 repetitions are on the layout.

Step 3. Make the progress bar working
If your table has 300 records and you are currently on record 100, the first 33 repetitions must be blue.
To establish this we will make use of the new conditional formatting feature in FileMaker 9.



- Go to layout mode and select the "progressbar" field.
- Right-click and choose "Conditional Formatting"
- Click on the "Add" button
- Choose "Formula is" in the condition value list and click on "Specify ..."

Add the following formula:

Let
(
[
thecurrentStep = Get(RecordNumber)
;
theTotalNumberOfSteps = Get(FoundCount)
;
theCompletion = Truncate ( (thecurrentStep/theTotalNumberOfSteps) * 100 ; 0)
]
;
theCompletion ≥ Get ( CalculationRepetitionNumber )
)

The function "Get(CalculationRepetitionNumber)" will return the number of the current repetition.
For repetition 1, the result of the calculation will be true.
For repetition 33, the result of the calculation will be true.
But for repetition 34 or higher, the result of the calculation will be false.

The only thing you have to do now, is to choose the fill color and you are finished.

Download the example file