Mutually exclusive Booleans

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:

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!

Advertisements

One Comment on “Mutually exclusive Booleans”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s