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!

Advertisements

Filtered Value Lists, with “All” Option

Demo file: filtered_VL_with_all.zip – – downloadable from the “Box” over on the right.

Filtered value lists are great, and very useful when it comes to improving the user interface.  In this food-based example, we have 2 tables, “Courses” and “Items”.  Food Items belong to a Course (e.g. Soup is a Starter, Steak is a Main course, Trifle is a Desert, etc.).

rel_vl1rel_vl2So when the user selects a Course, the Items menu (pop-up) displays all Items belonging to that Course.

This is achieved by a global field, COURSEID, with a relationship from this field to the course_id field in the Items table.  We then have a value list defined as follows, i.e. including only related values in Items from the context of the Globals table.  Standard stuff, well documented elsewhere, and it works fine, as long as the user has selected a Course.

rel_vl4

But we often have to deal with exceptions.  What if, for example, we have Items which don’t belong to a course (such as coffee or canapes)?  Or we want to allow the user to skip the Course selection and go straight to the Items list, where s/he expects to see a list of ALL items available?

Well, there are probably many ways to achieve this, but I’ve found it quite difficult to make it really user-friendly.  For instance, one book I read recently suggested having separate pop-up menu for “All Items”, using a value list based on a cartesian join, which would obviously work, but strikes me as potentially confusing to the users, sending them to 2 different places to make the same selection.

In my solution, the principle is the same, i.e. if the user doesn’t select a Course, we want to offer ALL Items, but let’s keep it all in the same pop-up.  To do so, base the relationship from Globals to Items NOT on the COURSEID global, but on a second global field, COURSESELECTOR.  This is an auto-enter calc, defined as:

If ( IsEmpty (COURSEID); ValueListItems ( Get ( FileName ) ; “AllCourses” ) ; COURSEID )

(making sure that “Do not replace existing value of field” is UNchecked).

What the auto-enter is saying is “if no Course has been selected, put all Course ids in the relationship field, as a return-separated list. But if a Course has been selected, just put that id in the field”.  So we’re using the global field either as a multi-key, or as a simple key, and when it’s a multi-key, the result will be that all Items appear in the “Items” value list.

rel_vl3There’s one important proviso though, concerning Items that don’t belong to a Course.  As it stands, these won’t appear in the “Items” value list (because that list includes only those Items which have a “parent” Course record.  So we must create a dummy Course record, with a blank name (or even named “Misc”, “Other items”, etc.), and all Items which don’t have a natural foreign key value must be given this one.  This will force the Items to appear.

As ever, there may be better way to skin this cat – if so, please let us know in a Comment.

 


Virtual Lists – “Let’s start at the very beginning…”

“Virtual List” in Filemaker is not really a technique, but a concept.  And it’s one that, although tricky to understand (at least for me!), is actually a very simple one, albeit with scope for complexity that can be rather scary. So I’m creating a series of steps, starting with this, the most simple of implementations, and working upwards in terms of complexity.

The requirement for this first example is simple: we have a table of People, and we want to produce a listing of each person’s first name.  Obviously this first example does not even need a Virtual List – we could simply have a layout showing the values directly from the table – but diving straight in with a more complex example runs the risk of clouding the simplicity of the concept.  (When I was trying to get my head around this concept, the complexity of available examples was a problem.)

The components required for any use of Virtual List are:

  • A global variable, e.g. $$PEOPLELIST, which will contain all the values for the list, in a return-delimited list.  The return-delimiting of the list means that each value in the variable will have a “value number”, and any value can be referenced using the GetValue function, e.g.

GetValue ($$PEOPLELIST;823)

  • A Virtual List table, pre-populated with the largest number of (blank) records you will ever need – i.e. the largest number of values that will be held in $$PEOPLELIST.  So, for example, if you’re going to use Virtual List for a report based on People, and you have currently 8,000 People records, you should probably create 10,000 blank record in your VL table.  Each record must have a sequential id, starting from 1.  There must be no gaps, and records must not be deleted, i.e. there will always be 10,000 records, numbered 1 through 10000.  (There is another field in the Value List record, “name”, about which more later.)  Obviously, we use a script to populate the table in the first place, like this:

vl_1

  • A script to create and populate the variable.  This will result, for example, in a return-delimited value in $$PEOPLELIST for each record in the People table.  Note that, if the field you’re concerned with has blank values in any records, a blank space needs to be written to the variable.  (We’ve seen that each value in the variable has a “value number”, and this will, in time, equate to the serial number of the record in the table. Hence the need for there to be no gaps.)

vl_2

So far, so very simple.  All we’ve done is write a variable with a bunch of people’s names – no big deal.  But what’s been going on in the Virtual List table while we’ve been doing this?

Well, the vital component to the whole Virtual List concept is a field in the Virtual List table, which I’m calling “name”.  This is an unstored calc field, defined as:

GetValue ( $$PEOPLELIST ; id )

So, into this field we automatically get the contents of the relevant value in the global variable.  The relevant value is obviously the nth value, where “n” is the id of the record, which in turn is the number of the value in the variable.  So if the 823rd person in the original file has a first name of George, because we’ve set the 823rd value in the variable to that name, we automatically have “George” in the name field in the Virtual List table.  Note that the records in the Virtual List table don’t need to be written or updated – the unstored calc is automatically refreshed when the global variable on which it depends is updated.

We can now use our Virtual List table as the source for, perhaps a report, listing people’s names.

At this stage, this may seem a) obvious, and b) pointless – but stick with it!  In future posts, we’ll be looking at how this can be really useful.  For now, consider the possibilities if, instead of just one global variable, we have several.  We may, for example, want to build a reporting table including related values (from other tables) for each person.  That’s when it starts to get really useful.


The Case function in a calculation

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:

Case (
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.

So, V2:

Case (
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:

Case  (
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.


Keeping Text Fields Tidy With Custom Functions

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; “”))


Tips for Improving Performance Over WAN

I recently posted a cry for help on the Filemaker TechNet, and received a load of helpful responses about how to improve a solution’s performance over a WAN (wide area network, i.e. via the internet, rather than local).

The full thread is here (login required), but these are the main points offered by the ever-helpful TechNet people, especially Taylor Sharpe, Joshua Ormond, David Jondreau and Mike Mitchell:

Unstored calculation fields

This seems to be the biggest issue in some circumstances.

  • The bad news about unstored calcs.  If a calc has to reference other records (e.g. aggregate fields, such as sum, min, ave, etc.), then all the records that contribute to the calc have to be downloaded and cached.  Aggregating data or referencing a lot of related data inevitably impairs performance, and this becomes especially noticeable in a WAN setup, and even more so when the unstored calcs are used in a Find or a Sort.  And EVEN more so when unstored calcs are used to aggregate data from other unstored calcs in related records – e.g. Sum ( { insert unstored calc here } ).
  • The good news.  It would be unfair on the humble unstored calc to say simply that “unstored calcs are bad” – obviously they’re a brilliant, and essential, feature of FM.  And if an unstored calculation references a global variable, for example, the evaluation occurs instantly.  And unstored calcs only evaluate when they are accessed (displayed, referenced by another calc, sorted on, etc.).

Where unstored calcs are causing performance issues, Joshua Ormond came up with these alternatives:

  • When you need agrregates of related data (count, sum, etc.), use a static field, which can be indexed.  This field is updated when records are added, deleted, or amended.  Joshua calls this a “transaction model”.
  • Execute SQL can also help. (This really does seem to becoming the FM Developer’s new best friend…)

“Big data”

Where possible, split data into manageable chunks.  Because FM retrieves the whole record, rather than just the bits it needs, it makes sense to limit the size of those records.  I’ve found this to be true in my solution, where I once had huge “People” table, whereas I now have it split between “People basic” and “People auxiliary”, the latter containing various calcs etc.  So if I just need the basic People data, that’s all I get.  (The “basic” table is still too big, so I aim to split it further in due course, using a properly normalised attributes table.)

Too many relationships

Where a relationship exists purely to retrieve an occasional piece of data from the related table, consider using “Execute SQL” (FM12 +) instead.  Certainly in my solution, the relationship graph, whilst tightly organised along anchor-buoy lines, will be hugely simplified when I move to FM12 and can use this feature.

Layout efficiency

  • Use blank layouts on file open and file close.  It seems that the first thing FM does when it opens a file is “Show all records”, so specifying a blank layout based on an empty table will certainly help.  Likewise, ensure that this same blank layout is the last layout shown when the file is hosted and closed, as this becomes the default startup layout.
  • When switching to another layout to perform a find, go to Find Mode FIRST, then switch to the other layout, thus preventing the loading of an initial set of records to display.
  • Switching layouts is slow.  Again “Execute SQL” can be used to retrieve data which otherwise may require a layout switch.

Seeing what’s going on

To see how much network traffic FM is handling, analyse the packet data going via Port 5003.  Tools such as Base Elements enable detailed benchmark testing of script performance etc.

ALSO see this article from FileMaker itself – “Optimizing Network Performance for Shared Databases“.  Some useful stuff.


Calculations and Auto-Enter Calcs

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.

Example

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.