The ExecuteSQL function in Filemaker can be really useful. That said, it has its drawbacks: it can be very slow (where many JOINs are involved), it’s very fiddly to build queries (with little help on offer as to where the problem lies if the query doesn’t work), and queries are prone to breaking when field or table names change.
I have no magic solutions to any of these issues, but I do have one suggestion to help with the ‘breaking’ thing, and a little bit with the ‘fiddliness’ problem.
Here’s a simple eSQL query:
This works fine, but if any of the field or table names (PPL, PPL::nameFirst, PPL::nameLast) change, the query will no longer work.
The solution is to put the various parameters into variables, as part of the ExecuteSQL calculation, like this:
At first glance, this looks unnecessarily complex, but stick with it!
The whole thing is wrapped up in one ‘Let’ statement. Part 1 of the ‘Let’ statement is where we define calculation variables with the fully qualified field names of the fields that we need to reference in the query, plus the argument to be used. (The tilde at the start of the variable names is just the prefix I use for calculation variables.) Note that the field name variables can’t be used in the query itself, because they have the table occurrence as part of them.
So, Part 2 of the variable declaration basically takes the fully qualified field names and extracts the field name itself (e.g. ‘nameFirst’, without ‘PPL::’). It does this by:
- Using the Substitute to convert ‘PPL::nameFirst’ to ‘PPL¶nameFirst’,
- And then separating the first and second values into the table name (‘PPL’) and fieldname (‘nameFirst’).
Note that these values have to be put in quotes, for use later in the query.
Part 3 assembles the query, also defined as a calculation variable, and using the variables defined in Part 2.
And finally, Part 4 defines one last variable, ~result, which is the result of the query.
This may seem like a lot of hard work, but I’m finding that once in the habit of using ExecusteSQL in this way, it’s actually easier and very logical. And, crucially, it makes your queries much more robust, in that if any of the field names change, the query is automatically updated because the fieldnames in the initial variable declaration (Part 1) come directly from the database. Unbreakable? Well, obviously not, but certainly less breakable.
(Note – this approach is not my invention, but has been adapted from solutions found on the Filemaker Technet.)
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!
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.
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.
I’ve rather avoided the Let function until now – until you use it, it seems to add a layer of complexity, and, as a novice, I’ve always liked keeping things simple.
But now I’m beginning to realise that simplicity is exactly what the Let statement offers. Not only does it enable you to make calculations more readable, but it greatly increases the efficiency of the calculation.
Here’s a good example.
Say I have a field called BKG::BKG_Cost, which is the cost of a Booking. The cost of the Booking is the total of the costs of the Resource Bookings within the Booking. The cost depends on whether the date of the first Session has passed – if the deadline has passed, we increase the cost by 15%.
So, one way of defining the field would be like this:
But if we use a Let statement to define the total booking cost, we end up with this:
The main gain here is that we reference the field PGM to SSN to RBK::RBK_Cost only once, and do the Sum only once, i.e. when defining the calculation variable “BookingTot”. In the first version, to apply the increase, we do that twice. Using the calculation variable also makes the whole thing more readable. And the more variables you use, the more readable it becomes. Here is is again, with a couple more variables (note the need, when defining more than one variable, to enclose the variable definitions in square brackets):
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.