Not long after FileMaker 7 was released, I happened to mention to one of my colleagues (Ilyse Kazar, in case you were wondering), “Now that auto-enter calcs have gotten so much more powerful, there’s really never a reason to use a lookup ever again, is there?”
“Actually,” she replied, “there is one thing that you can do with a lookup that you can’t do with an auto-enter calc…” and proceeded to tell me what it was. I promptly built a primitive prototype of today’s demo file, to see for myself, and she was indeed correct.
At first blush, it appears that every feature of the venerable FileMaker lookup option for fields can be replicated via auto-enter calculation — until you attempt to populate repeating fields that is.
If you ever need to set or clear a whole bunch of reps at once, you will appreciate how easy a lookup can make this operation. The secret is to have all the values sitting in another repeating field, so that when the lookup triggers, there is a one-to-one correspondence between the repetitions and each value transfers from the source repeating field (rA) to the same repetition in the target field (rB).
In today’s demo file, all the fields live in a single table, so the “x” and “y” table occurrences are both based on that table, but of course it would be fine for rA to inhabit a different table than rB.
Incidentally, I was in the middle of building today’s demo, when I realized I’d already built a decent one back in 2009, in response to a question posted on the FM Experts list. That thread includes a wonderful posting by Corn Walker on the relative merits of lookups vs. auto-enter calcs.
I’ll have more to say about repeating fields in an upcoming posting.