The Big Picture
A Bit of History (optional reading)
What's New in "Events"
How To Create New "Event Links"
How To Create New "Custom Flags"
Mass Data Entry
Finding a Flag
As I've said on many occasions, if you find yourself doing the same thing over and over again, there's probably an easier way.
Well, one thing I've been asked to do repeatedly is create custom checkboxes for certain subsets of records that users frequently need to call up. And these are different for different groups:
- One client has checkboxes for each of its 2 different newsletters plus a 3rd for people on Facebook.
- Another group has checkboxes for different projects that members participate in.
- A 3rd has boxes for "New to Project A", "New to Project B", "Send Postcard", "Sent Postcard", and "Needs Info".
- Yet another has a checkbox for each precinct in its city plus another for residents of a nearby suburb.
While the subject matter differs, the one thing all these users have in common is that they keep coming back for more subsets. Unfortunately, that means they usually have to wait for me to create the additional checkboxes and find a place for them on their layouts, which usually are already pretty tightly packed. And, in addition to the checkboxes themselves, I usually have to create global fields where the users can enter their preferred title for each one.
You'd think there must be a better way, and it turns out there is. All of my systems have a main table called "Beings", in which we store information about people and organizations. And a few of those installations already had a "BELinx" table (short for "being-event links"), which turns out to work almost exactly like a checkbox.
"BELinx" is what's known as a link, join, or merge table. It sits between 2 other tables (in this case "Beings" and "Events") and keeps track of all the legitimate combinations of records from them. For example, it might have a record that points to both Being #1234, Kelly O'Toole, and Event #56, Awards Banquet, along with the info that Kelly is the guest speaker for the banquet. This makes it possible to do a Find for "Awards Banquet" from the "Beings" table or for "Kelly O'Toole" from the "Events" table.
Really, tho, there's no reason why "BELinx" has to link people only to events. We could just as easily create an "Events" record called "Facebook" and link people to that instead of checking a box. It's just a single mouse click in either case. Furthermore, there's a fringe benefit, in that the "BELinx" table automatically remembers when its records were created, so you can tell when somebody was added to your Facebook roster (or, if they should flee screaming from Facebook, when that record went obsolete).
The greatest advantage, tho, is that it puts control over these subsets into the hands of the users — as many as they want, whenever they want — and they can do it all in Browse mode, without ever having to go into Layout mode, define or format new fields, or fiddle with scripts.
So I'm going to be converting a lot of the existing customized checkboxes to this more versatile "BELinx" approach. More details below.
It usually starts innocently enuf. Somebody says "Say, I live in the City of Sugar Maple, Wisconsin. Is there some way I could flag other residents of the city so I know whom to contact to lean on the city council to save Galena Creek?". And I, equally naive, say "Sure, ridiculously easy. We'll just create a checkbox for it." And so it begins:
Now, you may be wondering "Why don't you just have them do a Find in the 'City' field in the address?" 2 reasons: false positives and false negatives. You get a false positive when a resident of the outlying area — say, the Town of Propellor Seed — has an address served by the Sugar Maple post office but doesn't actually live and vote in Sugar Maple. And you get a false negative when you have an actual resident of Sugar Maple who lives in a part of town that butts up against the bigger Badger City and is served by that post office. In short, the post-office city isn't necessarily the same as the municipality of legal residence and can't be used reliably as a proxy for that purpose.
So we're in business with our checkbox. Some time later, however, it's "Hey, we want to be able to have constituents contact their individual city-council members specifically. Can we set up checkboxes for each of the 4 aldermanic districts within Sugar Maple?". And again, I'm all "No prob. We know exactly how to do it." And the burgeoning begins in earnest:
More time goes by. Now we want to lobby the town board of Propellor Seed (which also occupies part of the Galena Creek watershed), so we need to flag their residents. And we've also formed a group called "Friends of Galena Creek", so they get their checkbox as well:
You can see where this is headed, right? Every time the need for some new subset of people arises, it looks like a nail to someone whose only tool is a hammer, and we create a new flag for it. But look how much screen space we're starting to chew up with all those checkboxes. In particular, the 4 Sugar Maple aldermanic districts and the Town of Propellor Seed are geographically disjoint. Nobody will ever live in more than 1 of them at a time. Yet, because we don't know which one, we have to allow for all of them as possibilities.
It wasn't until someone asked "Can we create a checkbox for the people coming to our annual awards banquet?", and I replied "No, that's what the 'Events' table is for." that it dawned on me how similar the 2 processes are. And that's when I resolved to do something about checkbox proliferation, making use of a tool that I already had up and running in many cases.
Previously, the "Events" table kept track of 1 kind of thing — events — so there was no need to specifically say so in each record. But now that it's possible that it may also be tracking subset titles, it's useful to identify which is what. That's why, adjacent to the "Event Type" field, we now have the new "Record Type" field:
Every newly created "Events" record starts off with the assumption that you're dealing with an actual event, so that's what's entered by default in "Record Type", allowing you to go on to supply more details, producing a screen that looks like this:
But, if you set "Record Type" to "Flag", you won't need most of the additional info, so you'll end up with most of the other fields blacked out:
You could, if you wished, still enter information in them, but why?
Notice that the Packer-colored banner at the top of the screen tells you not only the title but also the record type ("Event" or "Flag").
Within the "Beings" table, you will continue to create new "BELinx" records the same way you always have. There will be a list of available events under "Event Picker", ...
... you'll click "Set" on the one you want to link to the current "Beings" record, and the link is forged:
You may still have to manually enter some info, like the role this being will play in the event and the timeframe involved. (This raises the possibility that the same person may be linked to an event more than once. For example, somebody could be running a workshop in the afternoon and be a speaker in the evening, or could be a sponsor for the entire event as well as an award recipient at the banquet. Each of these situations can be reflected in the "Role" field, sometimes in combination with the "Timeframe" field.)
The "Event Picker" portal will show only "Events" records that you have identified as having "Record Type" = "Event". Events you have flagged as "Obsolete" (almost always because they've already happened) get a caramel-colored background and drop to the bottom of the available options. Events that are still active are listed in reverse chronological order, which is how you're most likely to want to see them.
What about "Events" records that you have identified as having "Record Type" = "Flag"? Within the "Beings" table, they (and only they) will show up under the "Flag Picker" portal (usually found on the "Affiliations" tab):
And you'll use that the exact same way you would "Event Picker", by clicking "Set" on the flag you want to associate with the current being, and the link will show up next door under "Custom Flags":
If you set a flag by accident (as with "SMHS Faculty" in the example above), just click on its "Del" button to delete it. (At some future time, there will also be an option to set the link to "Obsolete", which is useful for tracking, for example, when somebody signed off of Facebook.)
As with "Event Picker", "Flag Picker" will deprecate flags identified as "Obsolete" by dropping them to the bottom of the list and giving them a caramel-colored background. However, unlike "Event Picker", items are listed under "Flag Picker" in alphabetical, not chronological, order.
So you've seen how you can flag a particular "Beings" record from the "Flag Picker". But suppose you wanted to do a whole batch of them at once (say right after you've imported the membership list of the Smallmouth Bass Society)? It would be tedious to have to page thru record after record, clicking on "Set" for each of them. Fortunately, there's an easier way, but it's finicky, and you have to make sure you've done things in the right order. The first thing you have to do is a Find within "Beings" for the records you want. (You don't have to call them all up at once; you can do them in batches if that's easier.)
Then go to "Events" and find (or create) the record you want. Let's say it is for the Smallmouth Bass Society. If it's newly created, it will have been set automatically as the default (indicated by the banner's yellow letters on the green background):
If not, it will be shown with white letters, as here:
In this case, you will have to click on the "Set As Default" button to make this the default event.
Now head on over to the "BELinx" data-entry screen, where you will find the "Import" button right next to "Create`":
Click on it, and it will ask you a couple of safety questions before it goes ahead and hauls in all the "Beings" records you want to link to the SBS. You'll have multiple opportunities to back out.
OK, so all of the above has been preparatory to actually finding a subset of records that you've so diligently flagged. As before, you will start by clicking the "Find`" button. And here we get to one of the tradeoffs that are inevitable in the wonderful world of computing. With checkboxes, all you had to do was click in the checkbox and hit "return", and you were off. Not quite so easy here. Now after clicking "Find`" you have to go to the "Custom Flags" portal, which is showing a magnifying glass in your flag-title field ...
... click in that field, and type in the name of what you're looking for:
I predict that you'll get up to about the 3rd time you have to type in "Sugar Maple AD 2" (not counting typos or memory errors such as omitting a space in "AD2") before you start longing for the simplicity of the old checkboxes. Fortunately, a solution is almost at your fingertips. Head on over to the "Events" screen and abbreviate "Sugar Maple" down to just "SM". That'll transform your "Flag Picker" thus:
More to the point, it'll also transform your "Custom Flags" references, so now you only have to search for "SM AD 2", which is easier to type.
But wait, there's more. Suppose you wanted to call up people in all 4 of the Sugar Maple aldermanic districts. You might think that you'd have to do that with a 4-part Find using the "or`" button:
You could, of course. And if you were still using checkboxes, that's what you'd have to do. But now there's an easier way. Just do a Find for "SM AD" (no number), and it'll pull in anything that has that letter combination (that is, anyone in any of the Sugar Maple aldermanic districts).
"Aha!", you're thinking, "I've got the hang of this now. If I want to rope in all residents of Sugar Maple, plus the high-school faculty (some of whom live just outside of town), I can do it by looking for only the letters they have in common." And so you try:
And it seems to have been successful until you start paging thru the found set and run across this guy:
Woops. It turns out that "Smallmouth" also begins with an "sm", so you've got a false positive. But here's where schmartness sets in. You head on back to "Events", find "SMHS Faculty", and insert a strategic space, causing your "Flag Picker" portal to look like this, with "SM HS Faculty" having leapfrogged above "Smallmouth Bass Society" because its 3rd character, a space, sorts ahead of the "a" in "Smallmouth":
And now when you do your Find in "Custom Flags", you do it for a character string enclosed within quotation marks, reflecting your desire for FileMaker to look not for "sm" (only) but for "sm[space]":
With a little practice, you'll get really good at learning how to avoid both false positives and false negatives while minimizing the amount of typing you need to do.
