Advancing Technique: Using Booleans to streamline data

While it may seem that in life there are very few black and white choices, in the database development world, the either/or option (on/off, yes/no, active/inactive, open/closed) is pretty common. We know these choices as Boolean options.

Data Structure

The beginning developer often makes the mistake of using a text field to store Boolean options, and storing the descriptors of the Boolean option in that field: in other words, you may have a field on your Contacts table called “Type” which contains only the values “Client” or “Vendor” and maybe another field in the same table called Status which contains the values “Active” or “Inactive.” While the performance hit and index space on storing these fields as text may be negligible with a small system on updated technology, it can become a real chore to keep up your Boolean options in a multi-table solution.

For example: Lets say you have a Jobs screen, and you want to see a portal of all active clients on this screen. With the text solution, you will need to add a global text field to your Job table called gClient — which contains the static value of “Client” and you also have to add a global text field gActive which contains the status value of “Active” — and you need to do this for every table that may need to reference active clients. When you start needing a list of Open Invoices (as opposed to Closed Bills) or Clients who have Yes in their “receive emails” profile, you can end up with a lot of global field clutter that can be a real chore to maintain. The problem will increase exponentially as your system grows in size.

A Messy TOG

However, if you were to assign a numeric 1/0 value to your Boolean values, you would dramatically reduce the amount of redundancy in your table and TOG structure. So if we were to say 1 = Active, 0 = Inactive, or 1 = Client and 0 = Vendor, instead of having to create discrete text anchor fields on each of your tables, you need only to create a gAlways1 field which is a global number field with the value of 1 on every table. From there, it’s a simple matter of joining to your other tables by saying either gAlways1 = Type or gAlways1 ≠ Type.

A much cleaner TOG

Pro Tip: Because FileMaker does not handle empty field values in a “does not equal” join, you will want to make sure that you force the fields to contain a value and not just assume it’s either 1 or Nothing. This can be tricky when you are using a simple “toggle on” checkbox. However, by creating an Auto-Enter calculation with the “Do not replace existing value for field (if any)” checkbox turned OFF, you can force the field to maintain itself: If(PatternCount(Self; 1) = 0; 0; 1) This tells FileMaker “if the field doesn’t have a one in it, make sure it at least contains a zero.”

By streamlining your Boolean options into simple 1/0 numeric fields, you will improve system performance, lower the index overhead, and make your own job a lot easier as you have to develop new features into your solution.

Interface

Let’s face it, immaculately-groomed data may make your job as a developer easier, but it’s going to make your end users short-tempered if they’re going to have to remember that 1 = Client and 0 = Vendor. So creating a seamless interface that obscures the Booleans is just as important as having streamlined data in the first place. The solution to this will use the commonly used Globals or System table, as well as a catch-all “Booleans” table. Let’s look at the Booleans table first.

The Boolean Table structure

The BooleanID field is actually not used, you’ll have to forgive an old Developer for requiring a Primary Key on every table, even if it’s not going to be important to the actual solution. The Boolean field is our simple number field which will store the 1 or 0 value for that grouping. The Name field will store the English translation of that field: Yes, No, Open, Closed, etc. And the Group field will indicate which records belong together: this is the YesNo group, this is the OpenClosed group, etc.

The data in our Booleans table

In order to build our value lists, we need to create global text anchor fields in our globals table to reference each of the groups.

Now we have to create Value List relationships to the Boolean table for each of these fields.

Booleans TOG -- Clean and Simple

While this may seem a bit like “messy TOG” up at the start of the post, the nice thing here is that we’re keeping the mess to only the globals table, because the value list is based on a global anchor field, it can be used in any table and we don’t have to repeat our work anywhere.

Pro Tip: You aren’t required to reference the Booleans table from a separate table. If your solution doesn’t use a system/globals table, you can tighten things up by simply making the Anchor fields part of the Booleans table and make it reference itself on the TOG.

Now that we have the TOG set up, we simply need to create our value lists. Let’s start with our list ActiveInactive:

Active/Inactive Value List Definition

As you can see, we’re referencing the Value List table occurrence. The first field is the Boolean (1/0) value which will be stored on the table, and the second field is the Name, which will show Active or Inactive. To keep it to just those two values, we’re only including our related values starting from the Globals table. Then, to keep the interface clean, we’re only showing the values from the second field (which prevents the drop-down or radio-button screen from showing 0 Inactive and 1 Active). It also sorts by alphabetical order for your name, putting Active first, and then Inactive second.

A sample of how the Value Lists might look on your layout

But what about when we don’t want our values sorted alphabetically? For example, most people prefer the On value to always come first, but if you sorted Yes / No alphabetically, Yes would come second. You can’t sort by the first field if you’re only showing the second field. So how do we get around this? The answer is not difficult, but it takes advantage of a little-used method of how Unicode sorts the alphabet. Unicode will always sort Capital letters before it sorts lower-case letters, so it will logically put Z before a. So if we go into our Booleans table and edit the “Name” fields so that any Off value that we want to force to appear as the second option has a lower-case letter at the beginning:

Y comes before n in Unicode!

We can then go into any value list that needs to use this method and check off the “Re-sort values based on:” option at the bottom of the Specify Fields pop-up and select “Unicode.”

Unicode sorting guarantees our list appears the way we want it

When you add a field to the layout using a Unicode-sorted value list, simply select the field and go into the Format menu and under Style select Title Case. This will smooth over your Unicode cheat so that the user sees Yes No as their options, instead of Yes no.

So there you have it! Your system is now set up to use Booleans to reference data instead of using complicated, inefficient Text strings. If you begin using this technique in your solutions going forward (even if you can’t perform a complete retrofit of a massive system), you will find yourself saving a lot of time and hassle when all you have to do to gain access to all your either/or values is to drop a single gAlways1 global field into your new tables, instead of recreating entire structures just to get a single filtered list.
Example File: Booleans.fp7 [zipped, 9KB]