Category Archives: Level: Beginner

Streamlining Scripts, part 1

I’m always mildly astonished when I see a script like the following — and this is the entire script by the way. It’s not that there’s anything horribly wrong with with it, but the architecture annoys me.

The thing I don’t like about the above script is that it is indented more than is actually necessary, negatively impacting readability. Continue reading

Plural, Singular, Who Cares?

This morning I was doing some maintenance on an old solution when I ran into this ungrammatical dialog. “I’d like to give a piece of my mind to the lazy slob who wrote that”, I said to myself smugly, followed a few seconds later by, “Uh, whoops, the slob was me.” Normally, I’m pretty good about making sure my dialog text is correct with regard to plural or singular, but in this case I must’ve been feeling particularly lazy (or overly confident that users would never export only a single record).

Continue reading

Portal Sorting, part 1

Experienced FileMaker developers will likely already be familiar with the following portal sorting trick, but I’ve worked with enough developers recently who aren’t (two, to be precise), that I figure it’s worth sharing here.

Problem: how do you sort a portal on a field that doesn’t live in the table the portal is based on? Take, for example, a simple sales database with this structure:

We want to view sales history for a particular product in a portal, like so: Continue reading

Manage Layouts… really

With the release of FileMaker 10, the “Set Layout Order” command, which had quietly lived under the Layouts menu for countless generations (only accessible from layout mode), was moved from its comfortable home and given a new name: Manage Layouts

One advantage of this change, was that the command could be accessed from any mode, rather than just when in layout mode. But apart from that, no additional functionality was added above and beyond what had been available in FileMaker 9.

The good news is that Manage Layouts has become much more useful in FileMaker 11, and now, truly does what its name implies, as per the buttons along the bottom of the dialog.

My favorite of these by far, incidentally, is the ability to highlight one or more layouts and then open each of them in a new window. I find this to be a huge time saver.

Another improvement worth noting is that there is now a keyboard shortcut to invoke Manage Layouts: Ctrl+Shift+L on Windows and Cmd+Shift+L on the Mac.

Dude, that code is sooooo FM3

Recently I saw some code that brought nostalgic tears to my eyes. The goal was to parse the file name from a reference in a container field called, appropriately enough, photo_ref. Here’s an example of what the data viewer showed when pointed at that field:

image:/C:/Client/XYZ Corp/photos/andrew wigan.jpeg

And this is the code the developer had written:

Middle (
   Photos::photo_ref ;
   Position ( Photos::photo_ref ; "/" ; 1 ;
      PatternCount ( Photos::photo_ref ; "/" ) ) + 1 ;
   99999
)

In a nutshell: count the slashes, and then retrieve everything to the right of the final slash. Here in a FileMaker 11 solution was code that could have been written in 1995.

To his credit, the code correctly returned “andrew wigan.jpeg”, but I had to wonder whether the developer was aware that there were several things he could have done to make his life easier (and his code more readable).

First, he could have simplified the code by using Let() to eliminate the multiple references to “Photos::photo_ref”.

Let ( a = Photos::photo_ref ;
Middle (
   a ;
   Position ( a ; "/" ; 1 ; PatternCount ( a ; "/" ) ) + 1 ;
   99999
)
)   //   end let

He could also have moved a few more things up into the Let portion of the calc.

Let ( [
a = Photos::photo_ref ;
b = PatternCount ( a ; "/" ) ;
c = Position ( a ; "/" ; 1 ; b ) + 1
] ;
   Middle ( a ; c ; 99999 )
)   //   end let

I find that to be a heck of a lot more readable than the code we started with. However, there’s a different approach that could be used to solve this problem, which strikes me as being both easier to understand and more elegant.

Let ( [
a = Photos::photo_ref ;
b = Substitute ( a ; "/" ; ¶ ) ;
c = ValueCount ( b )
] ;
   GetValue ( b ; c )
)   //   end let

In other words, convert the reference to a list, by transforming the slashes into hard returns, and then grab the bottom value from that list. Once you get comfortable with this technique, you will find many situations where it comes in handy.

For example, if you use the GetFieldName() function, you know that it returns both the table occurrence name as well as the field name itself, separated by “::” like so:

Invoice::id_customer

What if you just want to extract just the field name? You can use a simplified version of the technique we just finished discussing:

Let ( [
a = GetFieldName ( Invoice::id_customer ) ;
b = Substitute ( a ; "::" ; ¶ )
] ;
   GetValue ( b ; 2 )
) // end let

…and the result is “id_customer”.

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

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.