Conditional Invisibility, part 2

One of my favorite aspects of FileMaker Pro is reporting, and I appreciate it when a client poses a reporting challenge, because the phrase “we can’t do that” is not in my vocabulary (except when used as an example of a phrase not in my vocabulary). But recently I was given a reporting request that momentarily tempted me to utter those forbidden words.

Background: the client gives away prizes. Each prize falls into a “category”, and each category has one or more “types”.

The request: For a given date range, display and summarize prizes by category, and by type within that category, with each category starting on a fresh page… so far so good, right? The hierarchy is Prize Category → Prize Type, and FileMaker developers can crank out reports like that in their sleep.

But then came the twist: show different columns of information depending on category and type. And yes, the client wanted this all in a single report.

My first inclination was to design separate layouts for each category, run a report for each separately, and then assemble them into a PDF using FileMaker’s handy PDF “append” feature. But a) that wouldn’t address the need to see different columns per prize type, and b) I knew my conscience would forever trouble me if I resorted to a last-ditch maneuver like that without first at least trying to solve the problem in a more creative fashion.

I briefly wondered about using some sort of clever portal trick, but that didn’t feel like the right approach, and I thought about things I’d done in the past using overlapping calculated fields that would only display their values under certain conditions. But of course, if one wanted to do something like that in these enlightened times, one might be able to get away with using Conditional Formatting instead of having to define a bunch of extra fields… hmmm… well, why not?

In part 1, I explained at some length a trick to implement “conditional invisibility”, so I won’t retrace that ground here — suffice it to say that you can effectively render FileMaker text objects and fields (or their contents at any rate) invisible using conditional formatting by setting their text size to 500 pt. In the above example, I have layered merge fields two, three, and four levels deep, but of course most of those objects will be invisible for any particular combination of category and type during display, so the report is coherent, and the client is happy.

Did I need to use merge fields, rather than standard fields? No, but it made things easier — I often needed the same Conditional Formatting on both a “field” and the text label above it, so I would clone the merge field and then change the clone to the text label. This of course would not be possible if I’d used standard fields.

Here’s another recent example where conditional invisibility came in handy, and at first the request sounded so simple, it took me a couple moments to realize that some sort of trickery would be required. I built a report comparing the number of calls received per week this year vs. the same week last year.

The client requested that I add one additional column at the right, calculating the difference as follows: weeklyCallsThisYear – weeklyCallsLastYear, with a plus or minus sign in front of the number indicating whether the change was positive or negative (with no sign of course if the number happened to be zero).

The minus sign requires nothing beyond a rudimentary understanding of FileMaker’s number formatting options. But what about the plus sign? There are various ways to solve this problem, and I don’t claim that what follows is the only way or the “right way”, but here’s what I decided to do:

a) two overlapping versions of the field (one for positive, the other for negative or zero)
b) each one formatted to conditionally disappear using the 500 point text trick
c) currency formatting with “+” as the currency symbol on the “positive” version
d) standard negative number formatting on the “negative” version

Bear in mind that the conditional formatting determines invisibility, not visibility, so the positive version tests for <= 0, and the negative version tests for >0.

Here’s how it appears in layout mode on the final report, and since when I come back to this report, months or years later, I probably won’t remember that there are stacked fields, I decided place some reminder text next to them — once again utilizing the 500 pt conditional formatting trick.

In this case, I don’t want this text to appear except in layout mode, and simply setting the conditional formatting formula to “1” ensures that the text will vanish under all other conditions.