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:

My Support for ".FP7" Files Ends 2013 Dec. 31

UPDATE: As of 2013 Dec. 3, FileMaker Pro Version 13 is available, so old ".FP7" files are now even more out of date than before.

---

FileMaker Inc. (FMI) released FileMaker Pro (FMP) Version 7 in 2004. That was the one that created files with the ".FP7" extension. Among other virtues, it allowed us to pack multiple database tables into a single file. It was also tremendously robust. It was usable not only by Version 7 but also Versions 8, 8.5, 9, 10, and 11. Thus, as the user interface became more versatile, powerful, and user-friendly, the underlying file structure stayed rock-solid, and files created back in the mid-oh-ohs continued to be usable right up to the present. Frankly, it spoiled us, because it looked like it might last forever.

But computer years are like dog years, the ".FP7" file format finally ran its course, and the good folx at FMI needed to move on, particularly given the explosion of hand-held devices (iPad, iPhone, iPod Touch), which were barely a gleam in Steve Jobs's eye back in 2004 and which ".FP7" databases weren't really able to deal with. Thus, in April of 2012, after an unusually long delay following the release of Version 11 in 2010, FMI announced not only a new version (12) of the FMP software but also a new standard for the file structure, called ".fmp12".

Older ".FP7" databases could be converted (quite easily, in fact) to the new ".fmp12" format, but those created natively by FMP 12 weren't backward compatible; they could only be used by FMP 12 (and, eventually, 13, 14, etc.).

So about half my users jumped on board, upgraded their software, converted their files, and kept right on chugging along with the new, enhanced FileMaker Pro. The other half stuck with the older ".FP7" files, since they didn't really need the new features. And this was fine with me for awhile. I was perfectly willing to continue to support the older files, since I'd been doing it all along and couldn't, in all honesty, get everyone converted over all at once in any event.

But it's been a year now, and I've spent a goodly chunk of that year exploring some of the new capabilities within the ".fmp12" file structure and coming up with new standards that I've been installing in the ".fmp12" files I'm supporting. Older ".FP7" files aren't getting those features, and they're lagging further and further behind their brethren. It's not worth my time to do dual-track development even for those features that can be supported under ".FP7", since I've already done the work once for the ".fmp12" format, and I'd much rather work on something new than on repeating the same work under the older system for a dwindling client base.

At the same time, while the ".FP7" files haven't been advancing, they still require the occasional tweak and kick, and I haven't wanted to leave my users in the lurch, especially since I recognize that upgrading the FMP software to Version 12 is a cost item that they may not have budgeted for. But now I'm running into needed (or requested) changes to ".FP7" files that wouldn't have been necessary had they been equipped with the features that I'm making standard with ".fmp12" files, and again I have to question what's the best use of my own time.

So, in order to give everyone time to adjust, I am herewith announcing that, as of 2013 Dec. 31, I will cease supporting systems that use the ".FP7" file format. I will gladly pick up that torch again for the sole purpose of converting such systems to the ".fmp12" format, but I won't be working with ".FP7" any more after that date. That should give you plenty of time to acquire the latest version of the vanilla FMP or FMP Advanced software and get it installed on your system. Let me know if I can be of any help.

Follow This Blog by E-Mail

In the upper right corner of this blog you will find a box that enables you to enter your e-mail address and go thru about 15 seconds worth of additional clicking on yesses and OKs to sign up to receive an e-mail message every time something's posted to this blog, which will be rarely.

Changes to the "Universal" File

The Universal file contains the basic information about your organization: its name, logos, address, phone, eddress, website, etc. Up till now, there was just enuf of this information that it couldn’t fit conveniently on a single page, so we used 2 separate data-entry screens — General and Stationery — to accommodate it all.

Well, with the advent of wider computer monitors and the availability within FileMaker Pro of tabbed subscreens, we can now get the whole works onto a single screen (click to enlarge):


General information appears down the left side, while the right side contains 4 tabs:

1. “Stationery”, the frontmost tab, contains what used to be on the old “Stationery” screen.
   
2. “Logos and Finance” contains the 4 container fields for logos as well as 2 fields — Generic Benefits and Sales Tax Rate — with financial implications.
   
3. “Org Picker” is new. It lets you (well, me, really) jump to the organization you want to work with. That’s because, to make life simpler for myself, I’m now including base data for all my client organizations right within this single file. You’ll probably never need to use this tab.
   
4. “Agents” is also new. It lets you jump directly to the person you want in the Agents table.

There are also several new features in the Universal file.

There’s now a Motto field where you can enter a single-line slogan, statement of purpose, aspiration, or boast. Like everything else in Universal, once you’ve entered it there, it’s available everywhere else in your system.

The basic address-component fields have also undergone an expansion. This is what they used to look like:


We’d pre-assemble these components into 2 ready-made fields — Address Oneline and Address Multiline — that were available thruout the database system:


Well, it turns out that this didn’t address all situations, so now we’re looking at a few more components and a few new ways of assembling them. In particular, we didn’t allow for the possibility that a post-office box (mailing) address might have a different zip code, or even a different city, than the physical (shipping) address. So now we’ve made the distinction explicit:


As indicated by the blue-green color of the mailing-address components, each of those values is auto-entered to be identical to what you type into the corresponding shipping-address component. You can, of course, change it thereafter if appropriate.

We still pre-assemble these components into 2 ready-made fields — Address Oneline and Address Multiline — that present both types of address (non-redundantly if there’s duplication), but in addition we provide you with 2 new pre-assembled fields — Address Ship and Address Mail — for those occasions when you very specifically want exactly that.

In addition, 3 more consolidated fields — Letterhead, Contact Info Medium, and Contact Info Wide — continue to be available for use elsewhere. But now you can initially fill these with a reasonable guess at what you’d like by clicking the Guess button. Immediately thereafter you can customize the information to make it fit more neatly or to add more info that’s not part of your standard address components.

Standard Window Sizing and Zooming

Back when computer monitors were small, we wanted FileMaker Pro windows to be as big as possible, so I set them all to “maximize”, so they’d entirely fill your computer screen, completely covering up everything else (including other FMP windows).

Well, now that computer displays have expanded, that just seems greedy. OTOH, there are times when you really do want a particular file maximized. You’ve always had manual control over window size and shape, of course, but you don’t want to have to be doing it every time you switch to a different layout. So now, for each individual file, you can customize window sizing and zooming to your preferences. You get to the control settings from each file’s Router (welcome) screen, by clicking for info on This Particular File, followed by a couple of clicks on the "next page" arrow:


Standard Window. You can specify what you’d like the standard window to look like. These are your options:


As you navigate to each window in a file, its size will be set to the preference you've specified:

• Maximize expands it to fill your entire computer screen.
• Resize to Fit shrinks it so that it’s just big enuf for its contents.
• Restore resets it to whatever size you had previously set it at.

You can also change these sizes manually, from the “Window” menu, or via a script. In addition, you can manually choose 2 other settings:

• Hide makes the window vanish from your screen altogether. Bring it back by choosing “Show Window” from the “Window” menu.
• Minimize shrinks the window down to a minimal placeholder in your dock. Bring it back by clicking on that icon.

Manual controls are in the upper left corner:


Use the red dot to close the window altogether, the yellow dot to minimize it, and the green dot to toggle between maximized and previous size.

Standard Zoom. Similarly, you can specify what you’d like the standard zoom factor to be. These are your options:


As you navigate to each window in this file, its magnification factor will be set to the preference you've specified. You can also change these zoom factors manually or via a script. In addition, you can manually choose 3 other settings: 25%, 300%, or 400%.

Manual controls are in the lower left corner:


Use – to zoom smaller or + to zoom larger. Click on the number to toggle between 100% and whatever your previous zoom factor was.

Do It Now. You should do this for every individual file in your system. If other people also use these files, get some kind of consensus before you arrive at your standard settings.

Changes to "Tag" and "Obsolete" Buttons

You should be familiar with the following arrangement of housekeeping fields and their associated control buttons, because they appear on every data-entry screen in the database system.


Henceforth the buttons will be treated differently.

Tag Buttons. The Set and Find` buttons next to the Tag box will migrate up to the top of the screen, where they will now be known as Tag and Tag`, respectively.


As before, holding down any modifier key while clicking on Tag` will offer you a chance to clear all found tags.

There are 3 motivations for this transition.

1st, having those buttons right within each individual record falsely implied that they’d only affect that single record, when in fact they affect multiple records, which is what the top-of-the-page buttons normally do.

2nd, the tiny Find` button that affected only the Tag box was too easily confused with the big top-of-the-page button of the same name, which performs general searches.


3rd, in general, gold buttons that do searches are labelled everywhere else by the name of the field they search on, which Tag` will now do too.

Obsolete Buttons. The twin Set and Clear buttons next to the Obsolete field will be replaced by a single context-sensitive button that will read Set if Obsolete is empty ...


... or Clear if it’s got a date in it.


This button does affect only the record it’s in, which is why it appropriately remains there.

VCR-Style Navigation Buttons

Depending on when I last updated the buttons in your database system, you may see navigation buttons in either the old round "picture" style or the new oblong "text" style:

Well, they're both about to become obsolete, because the next generation of navigation buttons will abandon both pictures and text in favor of glyphs, which should be familiar from VCR or DVR controls:

The one that might not ring an immediate bell is the "#" one in the middle. Clicking on that one will let you jump to a particular record in the current found set. For example, if you've found 1000 records and enter "500" there, you'll jump to halfway thru your found set. If you try to enter "2000", it'll snark at you.

As you can tell by hovering your cursor over each button to summon up its tooltip, the "arrow-stop" buttons — "|<" and ">|" — will take you to the first and last records in the found set, respectively. The regular arrow buttons — "<" and ">" — will take you to the next record if you're in Form View (1 record visible at a time) or to the next page if you're in List View (multiple records visible at a time).

Reversed Effect of Tag Button

You’re probably acquainted with how I try to save screen space by giving added powers to some buttons if you hold down a modifier key (except caps lock) while clicking on them. These buttons are indicated by accent marks (`). Below are some examples:


Normal effect: creates new blank record
W/modifier key: duplicates current (existing) record


Normal effect: lets you delete the current record
W/modifier key: lets you delete all records in the current found set


Normal effect: goes to Router (welcome) screen
W/modifier key: goes right past the Router screen to the main data-entry screen for this table


Normal effect: enters Find mode; searches for subset
W/modifier key: finds all records

The pattern here is that the modifier key makes the button do extra work, over and above what it would normally do. But there’s been an exception to this pattern. It’s a button that appears near the various Tag boxes:


Normal effect: finds tagged boxes and offers to clear them out for you
W/modifier key: just finds the tagged boxes and stops

For the sake of consistency (and also because simply finding tagged boxes is probably more common than clearing them and should thus be the default), I’m reversing these effects. Henceforth, you’ll get this instead:


Normal effect: finds tagged boxes
W/modifier key: finds and offers to clear

The button color has changed as well. Gold means Find, which is now the basic function of the button; the orange border means delete or clear, which is now the augmented capability.