Mutually exclusive Booleans

The requirement:  A set of mutually exclusive fields, to be stored as Booleans (i.e. 1 or 0, on or off).

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, 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:

rgb_layout

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:

rgb1

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:

rgb2

rgb3
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!