Dear FileMaker Hacks,
In my personnel database, how can I find all birthdays in a given range, for example, between September 8 and November 15?
— Perplexed in Peoria
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
Recently I developed a system where the client wanted each user to only be able to see his or her own entries, and thanks to Custom Menus, some judicious scripting, and a bit of trial and error, this was accomplished.
I was feeling rather pleased with myself when the client asked, “What about the Keychain?” Well, what indeed? The Macintosh OS has a feature called the Keychain that “helpfully” offers to remember login credentials so that users don’t have to repeatedly type them in.
So imagine a situation where User A has instructed the Keychain to remember login credentials. What happens if User B gains access to User A’s computer, and opens the database? That’s what the client was wondering… actually, he knew what would happen (User B would gain access to User A’s entries). The question he was really asking was, “What are you going to do about it?”
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?
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
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
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"
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
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
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