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:
mebeliLet (
[
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.
Pseudoportals with Alternating Fill
Among my longest-standing feature requests for FileMaker are portal rows that slide individually to accommodate their content. Although I can usually get by with constructing my layouts in the related table, that’s an approach that usually leaves something to be desired. My favorite workaround, to date, has been what I call a “pseudoportal” (though I’m sure there’re other names for the technique). Read more
Conditional Formatting Tips
Conditional Formatting is currently my favorite FileMaker 9 feature. It used to be that we had to add all these extra barnacles to our database in the form of fields, calculations or relationships to highlight a simple portal row. Conditional Formatting goes a long way towards eliminating all that nonsense. CF is a big deal because for the first time in FileMaker’s product history, you can actually apply an expression (a formula) to layout objects–you can now program field objects…at least…sort of.
Yes, it isn’t exactly the feature I had in mind. I would have preferred to have full-blown access to all object properties and a way to program them using the calculation engine. But, Conditional Formatting gets me very close.
Here are a couple of basic highlighting patterns solved using CF.
1. Highlighting a Focused Field
There was quite a bit of buzz about Andy Lecates’ DevCon 2007 session where he showed off this active-field highlighting in his demo app. Andy noted that he was almost embarrassed to tell people that the formula behind this technique was literally a “1″. This puzzled me a bit so I decided to reproduce the technique. I have been told the credit for discovering this should properly be attributed to Chad Novotony of The Support Group. Kudos, Chad; it is really quite brilliant in its simplicity.
Sailing the boundless RG - “Here there be dragons”
If you use Anchor Buoy design methods and find yourself building endless TOs just because you like the way they look, then you’ve probably already devised a number of tricks and rules to keep your sanity and productivity alive any time you need to venture into the unknown reaches of your relationship graph.
Read more