The requirement: A set of mutually exclusive fields, to be stored as Booleans (i.e. 1 or 0, on or off).
Demo file: Mutually exclusive Booleans
Note – There may be several reasons why these must be separate Boolean fields, rather than one non-Boolean (which would obviously be easier to implement) – totalling and reporting, for example, can be much more straightforward with Booleans.
In a simple example (see demo file), we have 3 fields, isRed, isGreen, and isBlue. If any one of them is set to 1, the other two must be set to zero.
This is how I do this, following advice and tips from Technet members and others.
First of all, each field is shown as a checkbox set, using values from a value list called “true”. This VL actually only has one value in it, i.e. “1”. The field is sized on the layout to show only a single checkbox. Like this:
Then, each field is defined as a number field, with two auto-enter settings:
- Data – 0 or 1 (i.e. this determines the default setting),
- Calculated value – as shown below:
So, basically, this calc says:
- If the field currently being updated is isRed, set isRed to whatever the user has selected, i.e. 1 or not. But, because we want to store a zero to indicate the “off” state, use the GetAsBoolean function to achieve that.
- If the field currently being updated is isGreen, and if isGreen is “on”, set isRed to “off”.
- If the field currently being updated is isBlue, and if isBlue is “on”, set isRed to “off”.
Each of the other two fields have a similar auto-enter calc applied to them, like this:
Note that the confusingly-labelled setting “Do not replace existing value of field (if any)” is set to OFF.
There’s another post on this blog advocating the use of conditionally-formatted objects to indicate on/off states of Booleans, but I’m now beginning to prefer this approach. As ever, if you know of a better way to skin the cat, do let me know!
In the process of re-writing “FOCUS” in FM12, I’m experimenting with different ways of recording people’s attributes. I’ve realised that there’s logically no difference between a person’s gender, and the fact that s/he likes football – both of these can be regarded simply as attributes, i.e. “IsFemale” and “LikesFootball”. In terms of reporting and counting, I think it will be much easier to treat all attributes equally, i.e. that a person either has them or doesn’t.
A while ago, I documented the way that I’ve been handling simple “on/off” attributes, which I’ve called “tags” in the current version of the system. Here’s that blog post – it seems to work pretty well, and the users have said that they like it.
However, because this deals only with “on/off” tags, the technique needs a bit of refinement – if I’m going to use it for things like gender, sexuality, ethnicity, etc., I need to build in the “switching off” of any currently held setting. After all, if a person is already on the system as “female”, and we realise that we’ve recorded their gender wrongly, we can’t simply add the “IsMale” attribute – we also need to remove “IsFemale”.
So, on the Attribute record, in addition to holding the Attribute itself (“IsMale”, “Likes football”, etc.), I’m now also storing an indicator, “IsExclusive”, and an attribute type (“gender”, “sexualilty”, “tag”, etc.). If the “IsExclusive” indicator is set to 1, it means that if a person has one attribute of this type, s/he can’t hold another.
I have to say that all this seems a bit over-complicated when the alternative is simply to have a “gender” field, possibly with a couple of Boolean calcs (IsMale and IsFemale), to be used for counting and reporting. But there’s a real attraction in having all attributes in one place, and handled in the same way.
If you have any thoughts, opinions, or experience in this area, please comment!
I’m really enjoying my first forays into FM12 – many things seem much easier, and often more elegant.
One example is the handling of buttons to switch a Boolean indicator on or off (on attendance registers, access indicators, etc.).
For example, I have a field called “IsDisabled” in the People table. It’s on (i.e. set to 1) if the person is disabled, off (0) if not. (In the old days, this was a “yes/no” field, but this caused me all sorts of frustrations and limitations – Boolean is the way to go.) To amend and display this field, this is what I do:
- On the layout, rather than the field itself, I have a small button. It’s 15×15 on the desktop version of the layout, 34×34 on the iOS versions, with nicely rounded corners, consistent with the excellent River (or River Touch) theme that I’m using.
- The button just toggles the field, e.g. Set Field [PPL::IsDisabled; not PPL::IsDisabled]
(Note that the “not” has the effect of reversing the current setting – 1 becomes 0, 0 becomes 1.)
- The button is filled with a suitable colour – I use a solid green, to indicate “on”, with conditional formatting to show “no fill” if the field is not set. So the formatting is:
Buttons in FM12 have several states, and these can be used to fine-tune the user experience. I choose, for example, to use the solid green colour for the “Hover” state, as well as the “Normal” state – i.e. if it’s currently “off”, it will show green when the user hovers over it, to show what will happen if s/he selects it, but if it’s “on”, it will continue to show green on hover. When making this decision I was led by Google Apps, and it does seems the most “intuitive” way to present it.
What I like about this technique is that it’s really simple, and uses FM’s own stuff. Others may choose to use a graphic indicator (an arrowhead maybe?), and other non-FM tweaks, as I have done in the past, but after many hours spent fiddling with “home made” interface elements which sometimes end up looking wrong (or just different) on different screens etc., I’ve resolved to use native FM features in the re-write wherever possible.
Often, I need to be able to toggle an indicator field, i.e. switch it on or off. Obviously there are many ways to achieve this, and using “Yes/No” or “On/Off” value lists on a radio button field was one of my favourites in the past.
But it’s more efficient just to have the user click on the field and reverse whatever setting is currently held. And to achieve this, I started off with a script which said “If the current value is 1, set the field to 0, and vice versa”. But the next step in efficiency was to use a bit of pre-school maths, combined with the Abs function in Filemaker. The Abs function returns the absolute value of a number, i.e. disregarding the sign, so that Abs(-1) returns 1. So in a situation where a field can contain either 1 or 0, subtracting 1 from that value, and then taking the absolute value will always have the effect of toggling it, regardless of the current value held. If it’s currently set to 1, you get 0. If it’s currently set to zero, you get -1, of which the absolute value is 1.
So the toggling script that I ended up with is a really efficient one-liner, plus a commit. Pass the script the field name and the current value as parameters, and “Bob’s your uncle”. (Note the script uses the “GSP” custom function to extract the parameters.)
EDIT – See comments – Jeremy’s suggestion is even better, not even requiring the pre-school arithmetic, and using only one script parameter (or none, although I think I prefer the version which uses a parameter, thereby avoiding the “awkwardness”):
Set Field By Name [GSP(1); not GetField ( GSP(1) )]