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:
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!
A recent post on the TechNet forum, plus a succession of typically helpful answers, provided a very useful tutorial in how best to use the Case function to calculate a marked up cost, with the markup being based on the value of Cost.
Here’s the original formula. “Why won’t it work?” was the cry for help:
Cost < “5” ; Cost * “4” ;
Cost > “5.01” and Cost < “15” ; Cost * “3.5” ;
Cost > “14.99” and Cost < “25” ; Cost * “3” ;
Cost > “24.99” and Cost < “40” ; Cost * “2.75” ;
Cost > “39.99” and Cost < “60” ; Cost * “2.5” ;
Cost > “59.99” and Cost < “100” ; Cost * “2.25” ;
Cost > “99.99” and Cost < “200” ; Cost * “2” ;
Cost > “199”; Cost * “1.6”)
Well, with the field “Cost” defined as a number, and the calculation result defined as a number, the first step is to remove the quotes, which makes Filemaker see the numeric values as numbers, rather than as text.
Cost < 5 ; Cost *4 ;
Cost > 5.01 and Cost < 15 ; Cost * 3.5 ;
Cost > 14.99 and Cost < 25 ; Cost *3 ;
Cost > 24.99 and Cost < 40 ; Cost * 2.75 ;
Cost > 39.99 and Cost < 60 ; Cost * 2.5 ;
Cost > 59.99 and Cost < 100 ; Cost * 2.25 ;
Cost > 99.99 and Cost < 200 ; Cost * 2 ;
Cost > 199; Cost * 1.6)
This will now work, but it’s making much harder work of the calc than it needs to. Filemaker only works through the Case statement until it meets a true condition, then it bails out – there’s therefore no need for all of these > and < evaluations.
So, enter V3:
Cost < 5 ; Cost * 4 ;
Cost < 15 ; Cost * 3.5 ;
Cost < 25 ; Cost * 3 ;
Cost < 40 ; Cost * 2.75 ;
Cost < 60 ; Cost * 2.5 ;
Cost < 100 ; Cost * 2.25 ;
Cost < 200 ; Cost * 2 ;
Cost * 1.6)
(Note the final default value, 1.6, which applies to any value of Cost equal to or greater than 200 – no need for an evaluation there; if we get that far, that’s the value to use.)
The next efficiency is to use calculation variables, for the value of Cost, and the markup to be applied.
So, finally, V4:
Let ( [
$Cost = Cost ;
$Markup = Case (
$Cost < 5 ; 4 ;
$Cost < 15 ; 3.5 ;
$Cost < 25 ; 3 ;
$Cost < 40 ; 2.75 ;
$Cost < 60 ; 2.5 ;
$Cost < 100 ; 2.25 ;
$Cost < 200 ; 2 ;1.6)];
$Cost * $Markup)
But then came another interesting point, i.e. whether the Cost field itself is the right place for the calculation to happen. In the case of a “net price” such as this, which may need to be overridden, the better way is to have a separate Markup field, defined as an auto-enter calc, with the initial value of the markup amount calculated as above. The (new) Price field is then defined as a simple calc field, i.e. Price = Markup * Cost. On the Markup fields, the option “Do not replace existing value of field (if any)” should be ticked, to prevent historical and “override” data being replaced.
For a more comprehensive look at calc and auto-enter fields, see here. Thanks, as ever, to the Technet contributors who combine to make it such a great resource.
Having said in the previous post that I’m trying to use FileMaker’s own feature set wherever possible, there are some areas where it just ain’t good enough.
One such situation is in the formatting of text data entered by the user, especially name and address details. For instance, if you want all names to held in “proper” case (e.g. “Jane Smythe”, rather than “JANE smythe”, “jane Smythe” or any other permutation), the “Proper” calculation function is available – you just need to make your field an auto-enter calc, with the calculation defined as “Proper (Self)“.
But this is very limited, and can actually create mistakes in data that has been correctly entered. For example, if your user has carefully entered “John O’Shea”, she’ll be cheesed off when FileMaker changes it to “John O’shea”. Likewise “Mary Macdonald”, and “Bbc”.
For a more thorough treatment, I’ve had to look beyond FileMaker to a couple of Custom Functions, both found on Brian Dunning’s excellent site. The CFs are:
- Title, by Howard Schlossberg, which does a great job of putting capital letters where they should be, leaving them as caps where they should be, and making them lower case where appropriate. It’s also quite adaptable, so you can add exceptions to be treated differently, etc. It looks like Howard created it to format titles (of albums, books, etc.) properly, but it works just as well on names of people, organisations, etc.
- SuperTrim, by Debi Fuchs, which removes leading and trailing spaces, tabs and carriage returns from the entered text. Again, like FM’s native function (in this case, “Trim”), but better.
So, once the CFs are added to the application, it’s a straightforward job to define all appropriate text fields as auto-enter calcs with this calculation behind them:
SuperTrim (Title ( Self; “”))
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.
This is fairly basic, but there are several aspects of Calcs and Auto-Enter Calcs that are very important to grasp, and the decision about which to use is sometimes not as straightforward as it might appear.
The data in a simple Calculation field cannot be changed, whereas the contents of an Auto-Enter Calculation can be overridden. So it may be best to think of an Auto-Enter Calc as an ordinary number field which is initially populated via a calculation.
Updating an Auto-Enter Calc
Remember that, if you have NOT ticked “Do not replace existing value of field (if any)”, the Auto-Enter Calc will update if any of the fields within the calculation are changed. If you want to “lock” the contents of the Auto-Enter Calc, make sure that this option is ticked.
Note that, even if this option is NOT ticked, the Auto-Enter Calc will refresh ONLY if at least one of the fields on which the calculation depends are local. Related fields can be used in the calculation, and will make the initial calculation successfully, but the field will NOT update when related fields are changed. So Auto-ENTER Calc is the right name for it – it’s not an Auto-UPDATE Calc when it depends on related fields.
In the example, RBK_cost_autoenter_calc is defined as sessions::SSN_duration * resources::RSC_rate_ph. When data is first entered into the 2 related fields, the cost is calculated accordingly. But when either or both of those values changes, the calculated value in RBK_cost_autoenter_calc remains unchanged. By contrast, RBK_cost_calc, an “ordinary” calc field with the same calculation behind it, is updated when either of the values in the calculation changes. But, as an unstored calc, the value in the field cannot be overridden.
So it’s really about understanding the behaviour of the 2 types of field, and making use of the behaviour. In the room booking example above, it’s probably a desired behaviour that the calculated cost of a resource booking doesn’t change when the hourly rate changes (e.g. going up with inflation, which you wouldn’t want affecting old bookings), but you would almost certainly want the cost field to change when the session duration changes.
If you do need an Auto-Enter Calc field to be updated when related fields are amended, these are some possibilities:
- Use a script trigger to update the Auto-Enter Calc on modification of the related field(s).
- Duplicate the related field(s) in the local table.
A combination of the above is also possible – perhaps do a triggered recalc when the duration changes, which uses the new session duration and the locally stored hourly rate.