Custom Checkbox Farewell

Index to This Post

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

The Big Picture

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.

Back to Index

A Bit of History (optional reading)

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.

Back to Index

What's New in "Events"

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").

Back to Index

How To Create New "Event Links"

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.

Back to Index

How To Create New "Custom Flags"

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.

Back to Index

Mass Data Entry

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.

Back to Index

Finding a Flag

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.

Back to Index


New Prefix and Suffix Technique

In the past, I've had to create new value lists in every file that needed to have them for prefixes and suffixes. In keeping with my own oft-repeated advice that, whenever you find yourself doing the same damn thing over and over, there's probably an easier way, I finally figured out the easier way.

Henceforth, whenever I update an installation's "Universal" file, it will be with an expanded version that now includes the "PreSuf" table. That table contains prefixes and suffixes that can be used to make value lists within any other file in the system. And, since it comes pre-loaded with all the standard values, I don't have to recreate them each time.

Furthermore, since each prefix and suffix is associated with a "Sex" code (F = female; M = male; O = organization), this enables the drop-down menus in other tables to be context-sensitive based on the sex of the being identified in the record. For example, Mary ("Sex" = F) will see a different set of options than Richard ("Sex" = M), and both of them will see something different than what you get for The Snazzy Co. ("Sex" = O):

You may have noticed the absence of the 2 most common prefixes, "Mr." and "Ms.". That's because those are the standard values for name titles, and, since they are what will show up >90% of the time, they're the default values. What you see above are the overrides, which will replace "Mr." and "Ms." only if you take the time to select them.

To modify the contents of the drop-down menus, go to the "Universal" file and choose the "PreSuf" data-entry button:

That will take you to a screen where this is the left half of a typical record:

This particular one happens to be for a female, but, since lawyering is not a sex-specific occupation, there's another one, identical in every way, except for "Sex" = M.

Over on the right side of each "PreSuf" record is a further method of identifying it, by "Language" and "Category":

Astute observers will have instantly figured out that these 2 methods of describing the record are what accounts for the gobbledygook over on the right side of the drop-down menus shown above. "Language" and "Category" form the basis for sorting those menus. I wish there were some way I could display the menus with only the abbreviations, and not the gobbledygook — and perhaps some future version of FileMaker Pro will make it possible — but for now this is what it takes to make it happen. Don't worry, clicking on any of the options will insert only the abbreviation into the field, not all the extraneous crap.

Bear in mind that the drop-down menus are provided solely as a data-entry convenience. There's nothing to stop you from clicking in the field a 2nd time to dismiss the menu and just typing in whatever you damn well please. The menus serve only to make your life easier, not to limit the contents of the fields in which they appear.

And, as with all drop-down menus, you can navigate to your preferred choice in any of 3 ways:
  • by clicking on it
  • by arrowing up or down to it
  • by typing its initial letter
before accepting it by hitting "return".


New Signature Options

I've long made it possible to easily change the name that appears at the bottom of letters. The names (and associated data) come from a table within the "Universal" file called "Agents", and you can put in as many staff people, board members, officers, volunteers, parents, etc. as you want there. Each one must be assigned a unique "Agent Code", which can then be used by your letter layouts to pull in the info for that person.

On the letter layout, you select the agent from a drop-down menu that lists all of the possible agents for your organization (or your current organization, if you have more than 1). What's new from here on out is that your selection will now have a tooltip that tells you whom you've selected:

Also new is a similar drop-down menu, immediately below "Agent Code", called "Agent Format", that gives you 5 possibilities for how the info below the signature will be formatted, also explained by tooltips:

Lastly, right above the "Agent Code" is a new button that will take you directly to the record within "Agents" for the person you've specified, in case you need to change it: