Brain Teaser - Solution
Alright, so nobody solved this brain teaser. I’m sure this had nothing to do with people having clients and lives, and everything to do with the original post getting hosed and the challenge being too darn hard anyway 
So here, at long last, is the solution:
Let (
[
NewSubSummary =( GetNthRecord ( MyTable::MyKeyField ; Get(RecordNumber)-1) <> MyTable::MyKeyField ); //Test if record is the start of a new subsummary. MyKeyField is the sort field for my subsummary part.$on = $val; // $on will always be whatever $val was last
$val = If (NewSubSummary; If ( $val = 1;"" ; 1) ) // switch the value of val
];
$on // if $on=1, then apply the conditional format)
[edit:] Or, in much simpler terms:
Let (
[
$b = If ($b ;"" ;1 )
];
$b
)
To me, the interesting thing here is the way that these variable expressions are handled by FileMaker. First of all, the conditional formatting seems to get evaluated, as one might expect, from top to bottom of the page, allowing you to use variables to keep track of formatting decisions that were made earlier on the page, or even on previous pages. It seems there should be some much cooler implications of this, like multiple title headers and pseudo-headers (don’t know what I mean by that? Neither do I…). The other interesting thing is that the variables here are local variables, but they’re kept active while the whole page is rendered by preview mode, which is what allows me to make reference to the last evaluation of $val and produce the alternating effect (yeah, I know I could just as easily use global variables, but it’s handy to know I don’t have to)
The reason that this gave me a checkerboard on the first try:
was because I had placed the formula on a repeating field, meaning that each conditional format formula was evaluated for each repetition, left-to-right. Once I placed the calc on a regular field, the checkerboard was gone and I had nice alternating rows.
Brain Teaser
I was working on a crosstab report to show grade distributions and I wanted alternating highlights. I was using Mikhai Edoshin’s snazzy technique (http://edoshin.skeletonkey.com/2006/12/crosstab_report.html), which meant that each row was a subsummary part, which, at least in my copy of FileMaker, doesn’t support alternating fill. Like an idiot, I threw in a conditional format
————-
EDIT: This post got hosed somehow, and everything beyond the part where I refer to myself as an idiot was erased. I can’t remember what was idiotic about my original conditional formatting in this case (if I weren’t such an idiot, I’d probably remember). In any case, below is a reprise of the brain teaser:
———–
Each line in Edoshin’s crosstab technique is basically a repeating summary field based on a repeating summary calc. My goal was to get each one of these repeating fields to alternate its background fill. Using any kind of Mod-based calculation based on record number was going to fail, since these fields are on a subsummary, and there’s no way to predict how many records correspond to each subsummary part.
Taking a purely throw-rocks-at-it-until-it-stops-moving approach, I opened up the conditional formatting dialog on that repeating summary field, and hammered out a calculation. I was astounded, when I went back to preview mode, to see this:
My conditional formatting calc had inadvertently produced a checkerboard pattern. I realized, shortly thereafter, that by placing the same calc on a regular field, rather than a repeating field, I could get the alternating pattern as I desired:
The brain teaser challenge, before it got erased, was to figure out what sort of calculation could yield this effect on a subsummary part. The reward, if I recall correctly, was a signed copy of my memoir.
Tip of the hat
Having skipped DevCon in order to afford an iPhone, I’ve been dealing with my DevCon envy by exploring some of the wonderful sample files that people have been putting together for FM9.
The fuss over Andy’s conditional formatting tricks, for example, is certainly well-deserved (though let’s face it, we all would have worked that stuff out on our own, right? no?). After playing with those for a bit, a post about Ray Cologon’s “Progress Bars” ( http://www.nightwing.com.au/FileMaker/demos.html ) caught my attention, and I ended up spending a good afternoon hacking that file apart. Read more