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!
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) )]
The Requirement: A quick, user-friendly toggling of people’s “attributes”.
Demo file: ToggleAttributesDemo.zip – downloadable from the “Box” over on the right.
I’m now using a very efficient method for toggling fields. A typical example is with “attributes” in the application I’m currently working on.
First, a bit of background. The users wanted to be able to assign “attributes”, or labels, to a person’s record. This attribute could be anything – “Homeless”, “Likes drum and bass”, etc. – and will be used for reporting, producing mailing lists, etc. The attributes have to be user-definable, and a person can have as many attributes as necessary (although typically not more than about 10).
I did think of storing each person’s attribute allocations in a text field, with a value list enabling the user to select as many of the available tags as they needed via a check-box display. But, thinking ahead, this would have limited the usefulness of attribute allocations when it came to reporting. Much better (if a bit more work) to have a separate table of “attribute allocations”, i.e. a join table between the People table and the Attributes table. This would not only increase my reporting options later, but also enable the storage of details of the tag allocation (reason for allocating it, date, etc.).
So, 2 new tables:
- Attributes (“ATR”), with primary key, attribute name, plus any other details needed.
- Holder Attribute Allocations (“HAJ”) – primary key, plus 2 foreign keys (to People and Attribute tables), and any other details that we may need. (Note the use of “Holder”, rather than “Person”, as this will be used for other entities which can hold Attributes.)
In the People table, a couple of new fields:
- AttributesHeld, i.e. a calc field, listing all the Attributes held for this person. The calculation is defined as List ( PPL_HAJ_ATR::id).
- ATR_ID – i.e. a global field, which will be used to hold the ID of the selected attribute, and will be used to check whether the person already has the attribute or not.
Re. relationships, as well as the obvious relationships linking the PPL, HAJ and ATR tables, I added a relationship from PPL to HAJ. I called it “PPL_HAJ~newRecCheck”, defined as:
So, it uses the global “ATR_ID” field and the PPL ID field to check for the existence of the record.
And now the method for allocating attributes , which is the main point of this post.
On the People Details layout, we have a list of available attributes, in a portal. If this person has this attribute, conditional formatting highlights the portal row:
FilterList ( PPL::attributesHeld; “Equals” ; PPL_ATR::id ; 0 ) ≠ “”
So, using the excellent “FilterList” custom function (see Brian Dunning’s site), we can check whether the ID of the current portal row’s attribute exists in the person’s currently held tags. If so, we highlight it.
A button on the portal row runs the “Toggle Tag” script:
This is what it does:
- Set the global field to the value of the attribute under consideration.
- Use the “creator” relationship to check for the existence of this attribute allocation (i.e. whether this person already has this attribute ).
- If so, delete the attribute allocation.
- If not, create it.
The record creation is the nicest part of all of this. By using “Set Field”, we not only create the record, but also populate the field that we’ve set (obviously), but also the other field(s) used in the relationship – in this case, that’s the PPL_ID field, the foreign key back to the People table.
The icing on the cake is that we don’t even need to refresh the window to toggle the attribute in the Person Details screen – it refreshes itself, presumably by virtue of the Commit. The whole thing happens as calmly as if I had used the check-box/text field option, but with all the potential extra functionality that the related table option provides.