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!
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) )]