Level: Any, Version: FM 8 or later

Portal Sorting, part 3

Today we’re going to look at a couple more approaches to dynamic sorting, from opposite ends of the complexity spectrum. The simple one, portal sorting, circa 2002, is something I built in the FM 5.5 era. It uses a “smoke and mirrors” approach to achieve its objective, and apart from converting to .fp7 format, and consolidating into a single file, I’ve left it as is.

Behind the scenes this file has eight relationships from the parent table to the child; all based on the same keys, but with different sort orders. Continue reading “Portal Sorting, part 3”

Level: Any, Version: FM 8 or later

Logarithms I Have Known And Loved

They say you never forget your first time, especially if it’s your only time; maybe that’s why my first (and, so far, only) logarithm stands out so vividly in my mind. It was a quiet Thursday in October when the call came. A colleague was building a cat breeding database and wanted advice on how to solve a problem.

If a given cat is assigned “position 1” in his or her family tree, the cat’s ancestors can be assigned tree positions like so:


Additionally, each generation can be numbered as follows:

Continue reading “Logarithms I Have Known And Loved”

Level: Any, Version: FM 11 or later

GetFieldName: New in FM 10, Improved in FM 11

Demo file: getfieldname – requires fm11 or later

When FileMaker introduced the Set Field By Name script step in version 10, they wisely included a complementary function, GetFieldName, to help prevent database breakage due to field renaming.

Brittle code:

Robust code:

This is the standard use for GetFieldName, and it’s a very good use… in fact, in FM 10, that was just about all you could do with it.

I had high hopes when I first heard about this function, because the ability of a field to know its own name presents some intriguing possibilities. Unfortunately in 10, GetFieldName(Self) did not resolve properly in unstored calculations, as per the highlighted field below.

The good news is that this shortcoming was fixed in FileMaker 11.

This means that for the first time, we have the ability to modify the output of a calculated field merely by renaming the field itself. And while I offer no apologies, I do ask the reader’s indulgence for what follows. We’re exploring a “proof of concept” involving the GetFieldName function, which is not necessarily the optimal solution to this particular reporting challenge. Still, I think it’s worth exploring.

Here’s an example of how we might take advantage of this new capability. Below is a table of tour bookings. Pax is tour-speak for “number of passengers”, and currently we’re looking at some March departures for three consecutive years.

Note the three rightmost columns: pax_2009, pax_2010 and pax_2011, which exist to help produce a report comparing three years side by side. These are calculated fields, and in the Dark Ages (i.e., FileMaker 10 and earlier), we would have defined these fields along these lines:

   pax_2009:  if ( year ( date_depart ) = 2009 ; pax ; "" )
   pax_2010:  if ( year ( date_depart ) = 2010 ; pax ; "" )
   pax_2011:  if ( year ( date_depart ) = 2011 ; pax ; "" )

But now in this enlightened (post-10) era, we can define all three fields identically as:

   Let ( [
      a = Year ( date_depart ) ;
      b = GetFieldName ( Self ) ;
      c = Right ( b ; 4 )
   ] ;
      If ( a = c ; pax ; "" )
   )   //   end let

And while we’re at it, let’s make sure the storage type for these fields is “unstored”. In a nutshell, each field will compare the rightmost four characters of its name against the year of the date in date_depart, and if they’re the same, the field will show the pax value; otherwise it will show nothing.

We’ve also defined three summary fields to total these three fields, which allows us to produce a comparison report, showing totals for last year, this year, and next year side by side.

What happens next year, when we want to increment each of our pax_YYYY fields by 1? We simply rename the fields (pax_2011 to pax_2012, pax_2010 to pax_2011, and pax_2009 to pax_2010). Give that a moment to sink in: we can now update our business logic by renaming fields.

What about the column labels in the report? Can we use GetFieldName to make them update automatically? The answer is a resounding yes. And we can use “merge variables” (another new-in-FM-11 feature) to help. Here’s our report in layout mode:

The year column labels are merge variables named $$year1, $$year2 and $$year3, and we could have populated them via script, but how boring would that be? Instead we use conditional formatting — not to format the labels, but to cause the merge variables to refresh so they always indicate the correct years.

The conditional formatting formula is basically the same for all three labels, so let’s just look at how we’ve applied it to $$year3:

To reiterate: no conditional formatting has been applied. We just wanted a way to “tickle” the merge variables on the report. And there’s even a bit more trickery perhaps worth drawing attention to: the Let statement is being used to update (or create) the $$year3 variable, and that’s all the Let statement is doing, which is why the actual calculation part of the statement is empty.

As far as I’m concerned, this use of conditional formatting  is taking “cleverness” a bit too far (given that a script runs to generate this report, why not just set the variables there?), and I intend to sternly reproach myself at the earliest convenient opportunity.

But using GetFieldName to bind $$year3 to the rightmost four characters of the pax_2011 field name, in such a way that renaming the field doesn’t break things? Nothing clever about that… that’s just common sense.

Level: Any, Version: FM 9 or later

Alternative Locations for Plug-Ins

The release of FileMaker Pro 9 in July 2007 introduced the ability for plug-ins to be stored in an “alternative” location. The traditional locations,

Macintosh HD/Applications/FileMaker Pro x/Extensions
C:Program FilesFileMakerFileMaker Pro xExtensions

…(with “x” representing the FileMaker Pro version number) are still valid, but the new locations are guaranteed to be writable, whereas OS-level security settings may prevent users from being able to install plug-ins in the traditional locations.

The alternative locations are:

Mac OS X
Macintosh HD/Users/[user]/Library/Application Support

Windows 7
C:Users[user]Local SettingsApplication DataFileMakerExtensions

Windows Vista

Windows XP
C:Documents and Settings[user]Local SettingsApplication DataFileMakerExtensions

Note: on the Windows platform, the Local Settings folder may be invisible. You can fix this by going to the “Folder Options” control panel and checking the “show hidden files and folders” option. Of course this will make all other hidden files and folders visible as well, so take that into consideration.

General, Level: Any, Version: FM 8 or later

Color Coding in the Relationships Graph

FileMaker developers have philosophical differences of opinion on many issues great and small, and one of them is a) whether it’s worthwhile to add color to table occurrences (TOs) on the Relationships Graph (RG), and b) if so, what guiding principle(s) one should use.

Needless to say, I have some opinions on the subject, and here they are.

If you’re working on a simple project, it may not be worth the trouble. But let’s assume you’re working on a complex project, or that you’re working as part of a team (and if so, the project will almost by definition be complex).

In either of those latter cases, I believe that color coding is well worth the effort. I have seen various schemes employed, but to cut to the chase, the one that makes the most sense to me is to color code TOs according to their underlying base table.

In the example above, which is a small fragment of a complex project, I can quickly zoom in on the TO I want without actually doing much reading, akin to the way those of us who drive automobiles in the U.S. automatically know to stop when we see a red octagonal sign.

And I can sort the TOs as I wish (in this case by function), rather than grouping TOs from the same table together, as I might feel inclined to do if I weren’t using color coding.

Bottom line: color coding facilitates team development, helps me understand my solutions better, and enhances my productivity.

Level: Any, Windows

A Sweet Little ERD Tool for Windows

I have been on a multi-year quest to find a tool that would allow me to easily generate ERDs (entity-relationship diagrams) on the Windows platform. Having tried and been disappointed by a large number of free and paid products, the other day I stumbled on one that is easy to use and is 100% free, called Dia (http://dia-installer.de).

According to the online help…

Dia is an application for creating technical diagrams. Its interface and features are loosely patterned after the Windows program Visio. Features of Dia include multiple-page printing, export to many formats (EPS, SVG, CGM and PNG), and the ability to use custom shapes created by the user as simple XML descriptions. Dia is useful for drawing UML diagrams, network maps, and flowcharts.

One of my favorite things about Dia is how intuitive the interface is. I was able to produce the following in a few minutes without consulting the online help at all.

Note that I’ve chosen “Database” from a drop down menu of diagram types, and as a result, three diagram tools have appeared. I can use the leftmost one to insert tables into the work area.

What’s interesting here is that the four tables represented above aren’t just boxes on a screen. They are “table objects”, and double clicking on them brings up editable properties.

…so, in a nutshell, all you need to do is:

  1. throw some tables onto the work area
  2. specify key fields and attributes
  3. draw lines to connect the tables via the appropriate key fields

…and you’ve built a professional looking ERD.

Hint: set scaling like so under Page Setup if you want your ERD to always fill a single page; otherwise it will likely require multiple pages to print.