A client has recently asked me: "I read in on of your articles that calculated fields are faster than conditional formatting. Why is that?"
Well, take a look at this example from my article When a millisecond matters:
Let's say you want to make the text red for overdue invoices, orange for those paid but late, blue for those paid on time, and green for paid early. Even though only one of these conditions can be met at any single time, with conditional formatting FileMaker has to always check them all, only to find out they aren't met.
Pre-calculated color values
Text styles are additive
Different text styles are simply named numeric constants you can add together. So, for instance, if you have an underlined text, adding Bold style text to it makes it underlined and bold at the same time. You can even combine them togheter. For example, TextStyleAdd ( original text ; Bold + Italic ) will add both the bold and italic styles to the original text.
There is one exception though, and that's the Plain style. Its numeric value is 0, so it has no effect if you combine it with other styles. But if you pass it to the TextStyleAdd function alone, meaning that the second parameter of the function is zero, it will actually remove all styles from the original text instead of keeping it intact.
Retaining styles by default
While the TextStyleAdd function adds styles by default and only removes them when you set the style to Plain, the TextColor function always enforces the provided text color. If you want to mimic the conditional formatting behavior and only change the styles under certain conditions, you can work around it by calculating your color and style first and then only apply them if they are not zero (or they differe from whatever you consider default):
Conditional fill color
What is just a little bit more tricky is calculating conditional fill color. There is no function for that so you have to use a trick. One option that was common in the past, even before conditional formatting was added to FileMaker as a feature, is to create a calculation whose type is set to container, and either take a single-pixel colored image from a pre-defined global field, or calculate it dynamically.
Calculated container can be a great solution especially for coloring large areas. And if you don't want to rely on statically pre-defined pictures, you can copy the necessary custom functions from my PNG Canvas and PDF Canvas examples for that.
If your primary concern is performance though, then you'll probably hardly find a solution that beats the colored string of "solid block" unicode characters:
This calculation takes advantage of that the "solid block" unicode characters should have no spacing between characters, so by making a string of these characters, you can fill a rectangular area with a color, simply by applying the TextColor function you alreay know from the above.
Faster calculations would be even better
Of course, avoiding unnecessary calculation evaluations is always a good idea. Or I could say, having calculations evaluated unnecessarily is a very bad idea. But there needs to be balance between development effort and the results. So even though not using conditional formatting is better from the performance perspective, not having to avoid it so much would surely let us produce more value in less time.
So let me wrap this tip up with a gentle reminder that you can still vote to get the FileMaker calculation engine significantly faster, and if you have already voted, you can still convince others to vote as well...
