FileMaker 8

Since last month, FileMaker, Inc. released version 8 of their Pro and Developer software (although the Developer version is now called Advanced). We’ll continue to use FileMaker 7 as our teaching tool for the foreseeable future, but at some time you can expect us to move to FileMaker 8. Just not yet.

The Let() Function

FileMaker 7 introduced the Let() function, which is probably the most useful of those introduced with that version of FileMaker. This function lets you define variables that can be referenced later in the function. Here is the syntax template for the Let() function:

Let ( {[} var1=expression1 
      {; var2=expression2 ... ]}; calculation)

You can define as many variables as you want with using the Let() function, but if you define more than one, you need to enclose all of the variables within square brackets. After the variable definitions comes the rest of the calculation, which can reference the variables you’ve defined.

An example would probably help here. First, I’ll show you an example of a function that does not use the Let() function. The following is a custom function called IsPalindrome. A palindrome is a string of characters that appears the same forward and backwards, such as “bob” or “12344321”. The function gets passed a string and returns True if it is a palindrome and False if it isn’t.

Case(
  Length( String ) = 1 or Length( String ) = 0;
  True;
  
  Left( String; 1 ) <> Right( String; 1 );
  False;
  
  IsPalindrome( Middle( String; 2; 
                Length( String ) - 2) )
)

Note that we call the Length() function here three times, each time with the same parameter. That’s a hint that we may want to use the Let() function. Here’s IsPalindrome() using the Let() function:

Let(
  Len = Length( String );

  Case(
    Len = 1 or Len = 0;
    True;
    
    Left( String; 1 ) <> Right( String; 1 );
    False;
    
    IsPalindrome( Middle( String; 2; 
                  Len - 2 ) )
  )
)

Here we define the Len variable to be equal to Length(String). Then, within the rest of the calculation, whenever we used to use that function, we instead use the Len variable.

In this particular case, using the Let() function doesn’t do much for us, although the actual expression may be a bit easier to read. There are times, however, when the Let() function is indispensable, decreasing the size of the full calculation while increasing its readability. Any time you see the same code in your calculation more than once (as we did with the Length() function call), consider whether a variable defined with the Let() function would make things easier.

IsEmpty()

A very simple but common function to use is IsEmpty(). This function accepts a field name and returns True if the field is empty and False otherwise. Here’s the syntax:

IsEmpty( field )

Pretty simple, eh? This is often used when you need to combine strings, but you aren’t sure that every string you’re working with will have data. For instance, if you have fields for FirstName, MiddleName, and LastName in a contacts database, and need to calculate the FullName in a calculation, you might begin with this:

FirstName & " " & MiddleName 
          & " " & LastName

However, if the MiddleName is empty, you’ll end up with two spaces between the first and last names. Using IsEmpty() with the Case() function, you can correct for this:

FirstName & " " & MiddleName &
Case(
  IsEmpty( MiddleName );
  "";
  " "
) & LastName

This corrects for the case when the MiddleName field is empty, but what if the FirstName or LastName fields are also empty? Again, you can check for this using the Case() function and have the calculation operate appropriately:

FirstName &
Case(
  IsEmpty( FirstName ) or 
    ( IsEmpty( MiddleName ) and 
      IsEmpty( LastName ) );
  "";
  " "
) & MiddleName &
Case(
  IsEmpty( MiddleName ) or 
  IsEmpty( LastName );
  "";
  " "
)

Now, if the first name is missing or both the middle name and the last name are missing, we will skip the first space, since we don’t want the full name to begin with a space, and if either the middle name or last name is missing, we skip the second space to avoid having two spaces if the middle name is missing and having the full name end with a space when the last name is missing. In other words, we only need the second space when both the middle name and the last name are present.

Calculation Evolution

The above example is a good exercise in the evolution of a calculation. We began with a fairly simple concatenation of strings, but noticed that if the middle name was empty, the calculation wouldn’t work correctly, so we accounted for that possibility. But once we thought of that special case, we were able to think of more special cases and to then account for them. Strictly speaking, this was a debugging process. There’s a bug in the first two calculations, and the third one should work correctly.

The IsValid() Function

Similar to IsEmpty() is the IsValid() function. Like IsEmpty(), IsValid() is passed a field and returns either True or False. True is returned if the field contains valid data. This means that a number field contains numeric data, or a date field contains calendar data, but the most common case I’ve used this function with is to see if there is a valid related record in a table related to the current table.

We haven’t covered scripting yet (writing small programs within FileMaker to automate tasks), but let’s say you have a database with a Contacts table and a related EmailAddresses table (see the first article in the series for a discussion of relationships in databases). A single contact can have many e-mail addresses, but one of them can be set to be the primary e-mail address, and we would like to write a script that will create a new e-mail message and address it to that primary address.

Well, it only makes sense to run that script if there is a valid e-mail address related to the current contact’s record. If no such record exists, then we wouldn’t want to attempt to send an e-mail to a non-existent address. A calculation that would check for this would be:

IsValid( EmailAddresses::EmailAddress ) and 
  ( not IsEmpty( EmailAddresses::EmailAddress ) )

We could place this calculation within an If script step (note, not an If() function; they’re two different things), allowing us to check that everything will work correctly when we create the e-mail.

• • •

That’s it for this month. Go ahead and experiment with the functions we’ve covered. Try them out in your own databases and make sure your calculations produce what you expect. And come back next month when we’ll cover those functions most often used with parsing text. Until then, happy FileMaking!

Copyright © 2005 Charles Ross, chuck@chivalrysoftware.com. Charles Ross is a Certified FileMaker 7 Developer and the Chief Technology Officer of Chivalry Software, LLC, a company specializing in custom database, web and automation software and publisher of Function Helper, a FileMaker calculation debugging tool. He was a contributing writer and the technical editor for The Book of FileMaker 6 and has contributed to ISO FileMaker Magazine and Macworld in addition to his series on AppleScript for ATPM.