Detecting Related Records

Leave a comment

9-3-2007 by Geoff Coffey

If you have a relational database (one with several connected tables) and you do any scripting at all, you’ll almost certainly ask yourself a question at some point: How can I tell if the record I’m on has related records? This article explains the scenarios you may run in to, and how best to deal with them.

Before I start pontificating, let’s set up a scenario. I have the simplest of all possible relational databases. The Relationships tab of my Manage Database window looks a lot like this:

A very simple relational graph

I’ve got a Products table, and a related Parts table. Some products have no parts, some have a few, and some have hundreds. We’ll use this hypothetical example for the rest of the article.

Safely Processing Related Records

When you’re scripting, you often bump in to a scenario where you need to know if you have related records or not. Maybe your script is about to jump to the Parts related to a Product and do something to them:

Go to Layout [Products]
Go to Related Record [From Table: "Parts"; Using Layout: "Parts List"]
Go to Record/Request/Page [First]
Loop
   // do something here
   Go to Record/Request/Page [Next; Exit After Last]
End Loop

This script looks pretty solid: It goes to the set of related Parts records, jumps to the first in the list, then loops through them, “doing something” to each one. But it has a pretty serious bug:

If there are no related parts records, it will loop through all the product records in your found set, doing goodness only knows what to them.

That sounds like a FileMaker bug at first, but it isn’t. Here’s what’s happening: Since this product has no related parts, the second line in your script (Go to Related Record) will fail. It will fail quietly, though. If you turn on FileMaker Pro Advanced 9′s Script Debugger, switch on “Pause on Error”, and run the script, it will pause on that line and inform you that error number 101 has occurred. Error 101, as luck would have it, means “Record is missing.” Since the step failed, it doesn’t do anything, and your script runs as though the Go to Related Record step weren’t there at all. Ick.

You can probably dream up a lot of ways to deal with this problem. Perhaps the most straight forward is to simply add a little error checking:

Go to Layout [Products]
Go to Related Record [From Table: "Parts"; Using Layout: "Parts List"]
If [Get(LastError) = 0]
   Go to Record/Request/Page [First]
   Loop
      // do something here
      Go to Record/Request/Page [Next; Exit After Last]
   End Loop
End If

Now, if the Go to Related Record step fails (for any reason), your script is smart enough to avoid the loop. I use this method in most cases because it is absolutely safe. This is especially important if the stuff in the loop is destructive (like if it changes data or — gasp — deletes records).

Detecting Related Records in other Situations

The sample above only applies to one case: Using the Go to Related Record script step in a script. But there are lots of other scenarios where you might want to find out if you have related records. For example, suppose you want to turn the “Add Part” button red when there are no related records, so your users are irresistibly attracted to it. Or perhaps you have an unstored calculation field that shows the message, “No parts have been added yet.” when there are no related records. There are lots of reasons you want to find out, so how do you do it?

A common approach is to count them. For instance, in a calculation field in the Products table, you might use a formula like this:

If ( Count(PARTS::ID) = 0 ; "You need to enter some parts." )

This formula looks at the ID field in all the related Parts records. If the field value is not empty, it counts the record, so the result of the expression Count(PARTS::ID) is the number of related parts records that have an ID. Since every record has an ID, in practice this technique counts the related records.

This formula will work swimmingly, but it has a drawback: FileMaker has to do the counting. This isn’t such a big deal. After all, FileMaker is pretty speedy. But why put it through its paces if you don’t have to?

Here is a version of the calculation that does exactly the same thing, with less work on FileMaker’s part:

If ( IsEmpty(PARTS::ID) ; "You need to enter some parts." )

It may look a little counterintuitive, but if you scratch your head for a while, I think it will start to make sense. It says, “If the related Part ID field is empty, show the message.” But, you say, the ID field is never empty. Exactly. So if you look through the relationship at the ID field and it is empty, then there must not be a related record. Make sense?

Using IsEmpty() on a related field that is never really empty is a fast, sure-fire way to detect the absence of related records. It is faster than count, and completely fail safe.

FAQ: Someone told me I should use IsValid() for this instead. Doesn’t that work too?

The IsValid() function is a little-known function in FileMaker’s calculation system whose purpose in life is twofold: First, it tells you when you have bogus (ie: invalid) data in number, date, time, and timestamp fields. For instance, you might import from an Excel spreadsheet, and somebody put “Next Tuesday” in the Date Due column. FileMaker will import this data happily (unless you have validation to prevent it), but “Next Tuesday” is not a valid date. It is, as they say, invalid. The IsValid() function can also detect a missing table, but that’s not the sort of thing you typically code for.

For whatever reason, although the FileMaker manual says nothing about it, IsValid() will also return false when you look at a related field and their are no related records. So some people use it for this purpose. But is wasn’t designed to be used that way, and the folks at FileMaker can’t promise it will always work that way. What’s more there’s a (small but real) chance you might actually have invalid data in a related field. If you use IsValid() it might return false because of actual invalid data rather than no-related-records. So it isn’t completely reliable. But IsEmpty will always work so long as you stick with a field that will never be empty. And the ID field in your table is a perfect candidate for this.

Happy scripting :)

4 Comments

  1. Barbara Cooney

    I’ve always used the IsValid() before a GTTR. Hmm. I believe I was taught that it meant “if the relationship is valid…”

    I’ll change to not isempty(), because I typically setup my if elses to be

    
    If positive
     do something
    else
     whatever
    endif.
    

    Gonna be hard to break the isvalid() habit. Thanks for the tip.

  2. Geoff Coffey

    @barbara: I’m with you there. I used to use IsValid() for this until I was…ahem…educated on the subject by the FileMaker luminary, Darren Terry several years ago. Luckily, it’s not a hard habit to break. And I agree, I end up using not IsEmpty() most often.

  3. William

    hmm, I use it most of the time. In every table I have a field called One – a number field – which has an auto entered value of 1 – I always assumed this was a safe bet to use. I have never had it fail on it … but these comments are worth considering…

  4. Gregg O

    Be careful when trusting the Get (Last Error) method to check on the validity of your Go to Related Record script step. It is possible to get a 101 error even while the Go to Related Record step has “successfully” completed.

    You get a “false” 101 error if all following are true:
    1. you select the “Match all records in current found set” option
    2. the record on which you happen to be sitting—when the gtrr script step runs—has no related records
    3. at least one other originating record has valid related record(s).

    In the above case, Filemaker will deliver you to the related found set AND give you a 101 “record is missing” error!

Tell Us What You Think

*
* (will not be published)