RATING: Intermediate
PLATFORM: Macintosh & Windows
VERSION: FileMaker 6
TECHNIQUE FILES: Foundset.fp5
Buried in the "New Features" chapter of the "Getting Started Guide" that comes with FileMaker Pro 6, you'll see mention of perhaps the least sexy addition to the FileMaker feature set: "Constrain and extend a found set". It's the last real feature the guide talks about, and it hasn't gotten much press. It doesn't have a strong presence in the product either. If you weren't looking for it, you could probably use FileMaker Pro 6 for a long time and never even notice it. But this bottom-of-the-list feature is the most relevant addition 6 brings to the typical FileMaker developer, and one of the best things to happen to FileMaker in a long time.
If the name isn't clear, this feature lets you "constrain" an existing found set, or make the find criteria more selective; and it allows you to "extend" an existing found set, or make the criteria less selective. The impact is really threefold. First, it facilitates a new approach to finding records where you can keep trying until you get just what you want. Second, it provides a sometimes simpler alternative to complex multi-request finds. Third, and least obvious, it makes some kinds of queries possible that were never possible before (more on this later).
So how does it work? It might seem a little backwards at first, but once you realize how it is intended to work, the implementation is elegant. To perform a find, nothing has changed. Simply go to find mode, fill out a field or two, and click "Find". Just like before, this produces a "Found Set", making just the matching records visible. Now the fun part begins. Suppose you got more records than you bargained for. For instance, you know the customer you're looking for is in Indiana, so you search for that. Unfortunately, you have 300 customers in Indiana. Since you're pretty sure her first name starts with an m, you go back to find mode and enter "m" in the "First Name" field. This time, instead of clicking the "Find" button, choose "Constrain Found Set" from the "Requests" menu. The result will be just the customers who live in Indiana and have a first name starting with "m". If you still have too many records, you can head back to find mode, enter something else and constrain some more.
The Extend Found Set menu command works in a similar way. Suppose you wanted to produce a mailing to some of your customers about the new version of a product. You might start with all the customers who haven't purchased something in a while. This may produce a found set of 150 records. You also want to include those customers who have purchased the previous version. You can go to find mode, enter the criteria you want, and choose "Extend Found Set". In the end, you'll have the first 150 customers, plus those that match the second find.
This method of progressively massaging your found set until you get just what you want is a new and exciting way to explore your FileMaker data, and it's also often an easier way than the old-fashioned alternative. Savvy users know that the kinds of queries given above can be done in FileMaker 5 as well, by adding multiple criteria to a single request or by adding new requests. In many cases, a single find is easy enough, and the "Modify Last Find" menu command lets you easily "add on" to the find until you get it right. But multi-criteria, multi-request finds can get pretty hairy. Being able to break them up into multiple finds is more elegant and obvious in many cases.
In the beginning of this article, I mentioned that the new Constrain and Extend feature makes new kinds of queries possible. This is great news for anyone whose ever run in to the glaring limitation in the Request model of FileMaker's find command. It is helpful to use a "boolean expression" form when thinking about complex finds. For example, a find for customers in Indiana could be written like this:
state = 'IN'
If we wanted each customer in Indiana whose name begins with "m", we would have:
state = 'IN' AND name = 'm'
This would be equivalent to entering "IN" in the "State"Â field and "m" in the "Name" field in FileMaker's find mode.
FileMaker also lets you add additional requests, which translates to an "Or" operation in our boolean expression:
last purchase < '1/1/2002' OR previous version = true
So multiple fields in a single request are ANDed, and multiple requests are ORed. The "Omit" checkbox in a FileMaker request produces the last boolean operator, "Not":
state = 'IN' AND NOT name = 'm'
would be equivalent to a find with two requests, the second of which has the "Omit" box checked.
The limitation to FileMaker's find request model is that And operators always take precedence over Or operators. So you can do this:
(state = 'IN' AND name = 'Bill') or (state = 'CA' AND name = 'Steve')
but you can't do this:
(state = 'IN' OR state = 'CA') AND (name = 'Bill' OR name = 'Steve')
The first finds all the Bill's in Indiana and all the Steve's in California. The second finds all the Bills or Steve's in Indiana or California. You can get the effect of the second query in FileMaker by producing four requests and duplicating some of your criteria, but the problem quickly grows unmanageable. In an extreme case, I had a client who wanted a type of query that would have required thousands of find requests to produce because of this limitation.
But with FileMaker Pro 6, we have a couple of new concepts that nicely work around this limitation. Multiple criteria still correspond to a boolean AND, and multiple requests still produce an OR, but now we have Constrain Found Set to apply AND in a different way, and Extend Found Set for OR. To do the search shown above, we simply do two separate finds. The first finds all the customers in Indiana or California. The second finds all the Bill's or Steve's. Instead of clicking "Find" the second time, we choose "Constrain Found Set" and we get exactly the results we wanted.
Mixing "Omit" requests with Constrain and Extend may bend your mind a bit at first, but it works predictably and properly as well, producing NOT expressions and applying them to the current found set appropriately. For instance:
(name = 'Steve' OR name = 'Bill') AND NOT state = 'IN'
Is equivalent to two finds, one for the Bill's and Steve's, and one for everyone not in Indiana. The end result is all the Bill's and Steve's who don't live in Indiana. And:
(name = 'Steve' AND state = 'IN') OR NOT state = 'IN'
is equivalent to two finds, the first for all the Steve's in Indiana and the second for everyone not in Indiana. The result is everybody in any state other than Indiana, plus all the Steve's in Indiana.
Essentially, the FileMaker developer now has control over the precedence of boolean operators in a query. Try doing some of these in FileMaker Pro 5 and you'll see why Constrain and Extend are so powerful!
Best of all, this power is completely scriptable. When you need to script a complex query, the Perform Find script step has been enhanced, allowing you to "Replace", "Constrain" or "Extend" the existing found set. Simply perform the first find, then a second, third or fourth, choosing the right options each time. The user simply sees the correct set of records instantly isolated.
Constrain and Extend may look boring on the surface, but they provide new power, new ease of use and a new way of thinking in FileMaker's find mode, and to the FileMaker professional, that's nothing to yawn at.
Happy FileMaking!
Geoff Coffey is the Chief Technology Officer for Waves in Motion http://www.wmotion.com/ but his most important job is as dad to his daughters Isabel and Sophia. Email him at gcoffey@wmotion.com