Detecting Related Records

Geoff Coffey

3 September 2007 Archive Post

• sfr

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:

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.

Happy scripting 😄