Level: Intermediate, Version: FM 8 or later

Birthday Challenges, part 1

Dear FileMaker Hacks,

My personnel database has a scripted routine that locates all employees with birthdays in the current month, like so:

When I sent a copy of the database to our sister organization in Australia, the scripted routine did not function properly for them. Could it have something to do with their computer expecting D/M/Y rather than M/D/Y?

— Perplexed in Peoria

Continue reading “Birthday Challenges, part 1”

Level: Intermediate, Version: FM 8 or later

Magic Key, part 2

I ended yesterday’s article promising a couple improvements on the techniques showcased therein. To avoid unnecessary repetition I am going to assume you have read it.

The first improvement is to eliminate the highlighted steps in the “parse current field” script. Is it really possible to create a related record and populate multiple fields in that record using a single Set Field step?

Absolutely, and you can follow along in today’s demo file, magic-key-multipredicate-check-box-reporting, if you are so inclined. Continue reading “Magic Key, part 2”

Level: Intermediate, Version: FM 8 or later

Magic Key, part 1

Today we’re going to look at a technique called Magic Key, which allows you to flexibly create and populate related records. I believe that the name originates with European developer Ugo Di Luca, and we’ll get to the details in a moment, but first let’s look at a scenario where it might come in handy. Have you ever built or inherited a system with data stored in text fields formatted as check box sets?

There are pros and cons to this approach, and one of the cons is that data stored in this manner can be a pain to report on. But I have good news: Magic Key can help alleviate that pain Continue reading “Magic Key, part 1”

Level: Intermediate, Version: FM 8 or later

Fast Summaries

Have you ever wished you could pull the values from a summary report, and use them in a script or a calculation? Back in the FileMaker 6 days, Mikhail Edoshin introduced a technique to do just that.

He called the technique Fast Summaries, and I came to employ it extensively in my own solutions. Continue reading “Fast Summaries”

Level: Intermediate, Version: FM 8 or later

ValuePosition: The Function FileMaker Forgot

25 Feb 2012: Custom function syntax corrected to fix a minor bug.

There are various FileMaker functions that we can use to extract one or more characters from a text string (or any data string for that matter), provided we supply the proper numeric coordinates. For example,

Middle ( "Literature" ; 4 ; 3 ) = "era"

or

GetValue ( "Winter¶Spring¶Summer¶Fall" ; 3 ) = "Summer"

The key is knowing the numeric address of the data we wish to extract. In some cases we know it in advance (e.g., the first item of a return-delimited list); in other cases we calculate it on the fly. Continue reading “ValuePosition: The Function FileMaker Forgot”

Level: Any, Version: FM 8 or later

Lookups and Repeaters

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, because it turns out that auto-enter calcs can only address a field’s first repetition.

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. Continue reading “Lookups and Repeaters”

Level: Beginner, Version: FM 8 or later

Unique Records Revisited, part 1

A while back I posted a technique to identify and count unique records. I recently discovered that while the technique is 100% reliable in terms of identifying unique records, under certain circumstances it will fail to count them correctly, due to what I believe is a bug in the way summary fields work. You can easily reproduce the bug by following the instructions in this demo file: broken summary field

Fortunately there are various ways to skin this particular cat, and today we’re going to look at one of them (demo file: broken summary field workaround).

The method for indentifying unique records hasn’t changed. Continue reading “Unique Records Revisited, part 1”