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: Intermediate, Version: FM 8 or later

Reorder Based on Summary Field

One of the useful things you can do with a FileMaker summary report is reorder it based on the contents of a summary field. Since this is a bit abstract, especially if you’ve never done it before, let’s look at a concrete example.

Demo file: 2010-12-18-salespeople-by-state

Your organization has sales reps in various US states. In fact, many of the states have multiple reps. Here’s a simple table showing some of the sales reps and the state they’re associated with:

You’d like to generate a report showing the number of reps in each state, so your first step is Continue reading “Reorder Based on Summary Field”

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

The Wondrous Bullet Character

Do you know how to create a bullet (•) character via the keyboard? It’s easy on the Mac: just press Option 8. It’s a bit more complicated in the Windows world, but once your fingers get the hang of it, you’ll be able to do it without much thought at all.

  1. Hold down the Alt key
  2. Using the number pad, type 0149
  3. Release the Alt key

Step 2 must be performed on the number pad; it won’t work if you use the numbers on the main portion of the keyboard. And if this seems unduly complicated, the upside is that you can create any character this way as long as you know its corresponding ASCII value.

Continue reading “The Wondrous Bullet Character”

Level: Intermediate, Version: FM 10 or later

Exporting Data to iCal, Outlook, Google Calendar, etc.

Demo file: 2010-12-13-export to ical

The other day I needed to export some appointments from FileMaker to iCal. I’d never done this before, but I did a bit of reading on Wikipedia and elsewhere, and it turns out to be fairly straight forward. I don’t claim that what follows is in any way authoritative, just that it works… and not just with iCal, but with Outlook, Google Calendar and any other program that recognizes the iCalendar format.

Let’s start with a basic table of appointments, like this.

We’re going to create an “ics” file, which is a text file with three distinct elements. At the beginning of the file is the header, which looks like this:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//FileMaker Pro//NONSGML yourSolutionNameHere//EN

Next we have one or more body entries that look like this:

BEGIN:VEVENT
SUMMARY:Marketing Meeting
UID:00188BD54F5D-63427112000-1000028
DTSTAMP:20101206T180000Z
DTSTART:20101206T180000Z
DTEND:20101206T193000Z
END:VEVENT

And at the very end of the file comes the footer, which looks like this:

END:VCALENDAR

Let’s take a closer look at the body section. The BEGIN, SUMMARY and END entries seem self-explanatory, but what’s going on with those “DT” entries? Well, as you’ve probably guessed, they’re timestamps. First we have the date formatted as YYYYMMDD, followed by a T, followed by the time in 24hour format, hhmmss, followed by a Z.

One important observation about the time portion: it is represented as UTC time, not local time. So the first row in our appointments table, with a starting time of 10:00 Am, will be converted to the UTC equivalent of 180000 when we export it, because the database is located in the Pacific time zone, which is 8 hours offset, i.e., UTC-08. During Daylight Savings Time, the offset value would be -7, but we’re on Standard Time now, so the correct value is -8.

Clearly DTSTART is the beginning time for the appointment, and DTEND is the ending time, but what’s DTSTAMP? That is the “creation time” of the appointment, and apparently is required. Since I don’t care about that, I’m just making it the same as DTSTART.

And finally we come to the UID entry, which is simply a unique serial number. OK, I lied. Ideally it’s some form of “UUID” which means that heroic measures have been taken to attempt to guarantee its absolute uniqueness. But as long as it’s unique within your calendar, you’ll be fine.

The UID is very important, because if you import the same appointments more than once, the calendar will be smart enough to update existing entries where there is a matching UID, rather than allowing them to appear twice. That’s right… if an appointment changes in your database, you can export it, and reimport it into your calendar without having to worry about duplicates.

So, let’s add a UID field (auto-enter, text) and a UTC offset field (global, number).

Now that we understand the design of a minimal “ics” file, we can define a calculation field, ical_basis, in our database to assemble and format the data accordingly. Reminder: the first thing we do when we define a calculation field is, of course, make sure the result type is correct: in this case we want the result type to be text. Also, let’s set the storage type to “unstored”.

This calculation is the heart of the whole operation. Its main purpose is to generate one body segment per appointment record. Additionally, it will create the header at the beginning of the first record, and the footer at the end of the last record with the help of the “rn” and “fc” variables.

Note how the conversion to UTC is handled:

  1. transform a date and time into a timestamp
  2. add 8 hours (8 * 3600 seconds)
  3. break it into individual components (year, month, etc.)
  4. pad the components with leading zeros if necessary
  5. reassemble them as YYYYMMDDThhmmssZ

Let’s reduce our found set to three records, go into preview mode and see how that calc evaluates. Looks like we’re home free doesn’t it?

We simply export our found set of appointments to a text file with an “ics” extension and declare victory, right? Well, not quite. If we try that, here’s what we get:

…a text file with three lines, each a mile long. While inconvenient, this makes sense if you think about it. When you export to a text file, FileMaker uses ASCII 13 as the record delimiter, i.e, puts a hard return between the data from each record. To avoid confusion, any existing hard returns from within the source data are converted to a “vertical tab” character (ASCII 11).

But we’re not going to let a puny ASCII character defeat us, are we? (Yes, I know, these are Unicode, not ASCII, codes… but the values are the same either way, and it’s easier to refer to ASCII codes than Unicode “code points”.)

Broadly speaking, there are two different approaches we can take to solve this problem:

A. Generate the text file, and then post process the file using some sort of external technology.

B. Parse each row of ical_basis into a separate record, and then export those records.

Let’s look at option A first. There are various ways the post-processing can be accomplished, including AppleScript on the Mac, and VB Script or PowerShell on the PC, but it can also be done via a 3rd-party FileMaker plug-in, Troi File. (There may be other plug-ins that can accomplish this as well, but Troi File is the one I’m familiar with, having used it since 1998.)

What I like about the TroiFile method is, a) it works the same regardless of whether you’re on a Mac or PC, and b) how incredibly easy it is. One little command…

TrFile_Substitute("-IgnoreCase";$fileSpec;"";Char(11);Char(13))

…and no more pesky problem.

Method B involves creating a special table to facilitate the generation of the ics file. As with method A, there are any number of ways to accomplish this, but the one I want to look at here is Bruce Robertson’s Virtual List technique. It’s a highly versatile tool to add to your box of tricks, and it also happens to score a perfect 10 on the coolness scale.

Here’s a basic explanation of how Virtual List can be implemented to solve this particular problem, and don’t worry if it doesn’t make immediate sense. You don’t have to fully understand it to reap the benefits, and the act of implementing it in one of your solutions will help you understand it better.

  1. Create a new table in your solution. Name the table VL_Utility, and don’t create any records yet.
  2. Define a number field, serial_number, as an auto-enter serial number with an initial value of 1.
  3. Define a calculated text field, virtual_list, as
    GetValue ($$virtual_list;serial_number)
    …and set the storage type to unstored.
  4. Create “more records than you’ll ever need” in this table. If that sounds unhelpfully vague, why not start with 10,000? You can always add more later.
  5. Back in your Appointment table, locate the found set you wish to export.
  6. Using a looping script, walk the records and populate the $$virtual_list variable.
  7. Define a variable, $vc, as ValueCount($$virtual_list) .
  8. Locate records in VL_Utility where serial_number <= $vc, and make sure these records are unsorted.
  9. Export these records to a text file with an “ics” extension, e.g., appointments.ics

The result looks like this and is ready to import into your calendar program.


For Google Calendar, you can import entries by clicking the “Add” button at the left of the calendar. On the Mac, you can import an “ics” file to iCal by double clicking it, and for Outlook choose Import and Export from the File menu.

Here’s what we see after importing.

Really we’ve just scratched the surface of what can be accomplished using the iCalendar format. There are many optional properties that can be included, for example “description” (a.k.a. note). If we want to include notes in our export, we can modify our ical_basis calculation as follows:

And here’s how an appointment with a note looks in iCal:

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
and
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
C:Users[user]AppDataLocalFileMakerExtensions

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: Intermediate, Version: FM 8 or later

The Last Day of the Month, part 3

Grizzled FileMaker veterans are fond of saying things like, “You ought to know at least three different ways to accomplish any given task.” With that in mind, I hereby submit a third method for calculating the last day of a given month.

Let ( [
theDate = Get ( CurrentDate ) ;
monthNum = Month ( theDate ) ;
yearNum = Year ( theDate ) ;
febLastDay = 28 +
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
) ;
dayNum = Choose ( monthNum ; "" ; 31 ; febLastDay ; 31 ;
30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31
)
] ;

Date ( monthNum ; dayNum ; yearNum )

) // end let

If you read yesterday’s post, you may have noted a resemblance between this calculation and its predecessor, at least as far as the “Let” portion goes. The main difference is the addition of a dayNum variable populated via the Choose() function.

In case you’re not comfortable with Choose(), its format is

Choose (
test ; result if test = 0 { ; result if test = 1 ; result if test = 2... }
)

…where “test” is any non-negative whole number, and the results in braces are optional. At first this function may seem confusing but it turns out to be a very compact replacement for the Case() function, under a strictly defined set of circumstances.

Say, for example, in a table called “test”, you have a field called “score”, which can contain any integer between 0 and 9, and you want convert that value to its corresponding name (“zero,” “one,” “two”, etc.). You could certainly accomplish this with Case() and the statement might look like this:

Case (
test::score = 0 ; "zero" ;
test::score = 1 ; "one" ;
test::score = 2 ; "two" ;
test::score = 3 ; "three" ;
test::score = 4 ; "four" ;
test::score = 5 ; "five" ;
test::score = 6 ; "six" ;
test::score = 7 ; "seven" ;
test::score = 8 ; "eight" ;
test::score = 9 ; "nine"
)

The exact same result can be obtained far more economically thus:

Choose ( test::score ;
"zero" ; "one" ; "two" ; "three" ; "four" ;
"five" ; "six" ; "seven" ; "eight" ; "nine"
)

Essentially, Choose uses test::score as a pointer to the correct “result”, via what’s known as a zero-based index, so a test::score value of 0 corresponds to the first result, a test::score of 1 corresponds to the second result, etc.

In the case of our Last Day of the Month problem, there is no month number of 0, only 1 through 12, so our first result is "" to accommodate the non-existent zero result.

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

The Last Day of the Month, part 2

Yesterday we explored a method to calculate the last day of the month, and by way of introduction I said,

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years.

This time around let’s look at that method, and those alleged “gymnastics”. They turn out to not be terribly convoluted; in fact, the most time-consuming part of this could be simply defining the rules for what constitutes a leap year.

But first let’s pretend there’s no such thing as a leap year or a leap day. In that case we could write our calculation as follows:

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 ; yearNum ) ;
      <etc.>

Okay, that was a pleasant interlude, but now back to reality. Superficially, it appears that all years evenly divisible by 4 are leap years, but it’s a bit more complicated than that. A year is a leap year when:

  1. it is evenly divisible by 400
    or
  2. it is evenly divisible by 4 but not evenly divisible by 100

It’s important to know all the rules, because we don’t want our database breaking on February 29th in the year 2400, do we?

So how do we determine “even divisibility”? We do so by using the Mod() function, which returns the remainder when you divide one number by another. If the result of the Mod() operation is zero, then we know the first number is evenly divisible by the second.

For example, Mod ( 2000 ; 400 ) = 0, which passes test #1 above, and tells us that the year 2000 was a leap year. The year 1900 cannot pass either of the above tests, which tells us it was not a leap year. Bearing in mind that FileMaker uses calculation “short-circuiting” (stops calculating as soon as one of its logical tests evaluate as true), here’s a calc that returns a 1 for leap years and otherwise returns a zero:

   Let ( yearNum = Year ( Get ( CurrentDate ) ) ;
      Case (
         Mod ( yearNum ; 400 ) = 0 ; 1 ;
         Mod ( yearNum ; 100 ) = 0 ; 0 ;
         Mod ( yearNum ; 4 ) = 0 ; 1 ;
         0
      )
   )   //   end let

Okay, now that we’ve gotten to the heart of the matter, we can put it all together and write our calculation.

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate ) ;
      possibleLeapDay =
         Case (
            Mod ( yearNum ; 400 ) = 0 ; 1 ;
            Mod ( yearNum ; 100 ) = 0 ; 0 ;
            Mod ( yearNum ; 4 ) = 0 ; 1 ;
            0
         )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 + possibleLeapDay ; yearNum ) ;
      monthName = "March" ; Date ( 3 ; 31 ; yearNum ) ;
      monthName = "April" ; Date ( 4 ; 30 ; yearNum ) ;
      monthName = "May" ; Date ( 5 ; 31 ; yearNum ) ;
      monthName = "June" ; Date ( 6 ; 30 ; yearNum ) ;
      monthName = "July" ; Date ( 7 ; 31 ; yearNum ) ;
      monthName = "August" ; Date ( 8 ; 31 ; yearNum ) ;
      monthName = "September" ; Date ( 9 ; 30 ; yearNum ) ;
      monthName = "October" ; Date ( 10 ; 31 ; yearNum ) ;
      monthName = "November" ; Date ( 11 ; 30 ; yearNum ) ;
      monthName = "December" ; Date ( 12 ; 31 ; yearNum )
   )  
   )   //   end let

One of the nice things about yesterday’s elegant little calculation was that we didn’t have to think about leap year at all. We let the FileMaker calculation engine worry about that for us. But there’s something very different I like about today’s calculation… it’s supremely readable. It’s not elegant, and it’s not succinct, but it gets the job done. And that’s one of my favorite things about FileMaker: there is rarely only one way to solve a problem.

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

The Last Day of the Month, part 1

This seems an appropriate day to post this. At the risk of stating the obvious, the first day of a given month is a very easy date to calculate, because the day number will always be 1. So, assuming we want to dynamically calculate the date for the first day of the current month (whatever that month may happen to be), we can simply plug the values in as follows:

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) ; 1 ; Year ( x ) )
      ) // end let

Wouldn’t it be nice if it were that easy to calculate the date for the last day of the month? The problem of course, is that the last day of the month can be 28, 29, 30 or 31.

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years. Here’s an approach I like because it’s both succinct and bullet proof.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 1 ; Year ( x ) ) - 1
      ) // end let

In a nutshell, this tells FileMaker to calculate the date corresponding to the 1st of next month, and then to subtract 1 day from that. It even works in December, because FileMaker is smart enough to convert Date(13;1;2010) to January 1, 2011, and of course if we subtract one day from that we get December 31, 2010.

And here’s a tip I picked up from Geoff Gerhard at Creative Solutions: the calc can be further simplified, by removing the “-1” from the end, and changing the day number to zero, which FileMaker is smart enough to interpret as “the day before the first of the month”.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 0 ; Year ( x ) )
      ) // end let

Incidentally, I’ve used Get(CurrentDate) in these examples, and that’s fine for a single-user system. For a multi-user system, it’s a good idea to instead use…

      GetAsDate ( Get ( CurrentHostTimestamp ) )

…which ensures your users will all be on the same page, or date at any rate, regardless of what date their computer thinks it is. Time can be calculated in a similar manner:

      GetAsTime ( Get ( CurrentHostTimestamp ) )

And the nice thing about this is that if you open the file single-user, the above calcs still work since your computer is considered to be the host. That’s what I call all gain and no pain.

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.

2017-07-16_124329