Isolating a Record

One of the most common requests I get is how to isolate the current record to be the only one in the found set. The reasons vary, but one of the most common needs is to preview the current record. When you enter preview mode, the first record in the found set rather than the current record is the first to show.

The script for isolating a record is very simple if you know the steps. There are only three steps:

The problem occurs when you want to restore the previous found set, active record and sort order. This is a lot more work, but improves the users experience significantly. The user doesn't care about the limitations of FileMaker or how much work a feature requires. All they care about is if FileMaker works the way they expect it to. Losing the previous found set of records can be very disconcerting for users.

With that said, you need to add some script steps, several fields and two relationships. Let's start with the fields since they are needed in the scripts and the relationships. All but one of the fields will be global fields. The fields are Current Record, Found Set, Sort Order and Serial Number.

The Serial Number field will need the auto-enter option for serial number.

The Serial Number field will uniquely identify each record while the global fields will do pretty much what their names suggest. The Current Record field will store the serial number for the record when the isolation script is initiated. The Found Set global field will store the serial numbers for all the records in the found set. The Sort Order global field will store whether the database is sorted or not.

TIP: If you own FileMaker Pro 4.0, it is possible to use a calculation with the formula Status(CurrentRecordID) instead of the Serial Number field.

The relationships work with the global fields and the serial numbers and the upcoming scripts to restore the found set, sort order and current record after the user is done isolating the record.

Notice that both of these relationships are same file relationships. A same file relationship is one that relates to itself. When creating the relationship, FileMaker will ask you to locate the related file. Simply locate the file where you are creating the relationship and you will have a same file relationship.

There are quite a few scripts when you look at ScriptMaker in the example file provided. The scripts are broken up into pieces representative of their function. To save space on this slide, the scripts will be presented here as a two longer scripts.

The first script is the one that remembers the found set, current record and sort order:

The first two Set Field commands are fairly straightforward. The serial number from the current record is placed into the Current Record global field. Then, the current sort status is placed into the Sort Order global field. The Status(CurrentSortStatus) returns a zero (0) for unsorted, a one (1) for sorted and a two (2) for semi-sorted. The Copy All Records script step is not as clear. This script step copies all the fields on the current layout for all the records in the found set. The format is tab-separated text which means a tab between each field and a return between each record. However, notice that the Go to Layout script command is called just before the Copy All Records script step. The layout that is selected contains a single field, the Serial Number field. This causes the serial numbers to be pasted into the Found Set global field with returns between each value. The data in the Found Set global field might look like this:

The explanation of the next script will cover exactly how this return-separated list is used to restore the found set. Here is the second script:

The first Go to Related Record script step restores the original found set using the Show Only Related Records option. This works because the Found Set global field contains all the serial numbers from the records in the original found set. Since the Found Set global field is used on the right side of the Restore Found Set relationship, the Go to Related Record script step finds all the records that have the same serial numbers. The returns allow the values to be treated as separate matches in the relationship.

The next step is to restore the sort order. If the Sort Order contains a one (1), the currently stored sort order will be invoked. For instance, if the user had sorted by First Name just before isolating the current record, that sort order will still be stored in the Sort dialog. Try it out yourself. Sort by a field and then return to the Sort dialog. The field or fields you last sorted by will still be there.

Since sorting activates the first record in the found set, another Go to Related Record step is needed to locate the record that was selected before the isolation script was run. This Go to Related Record step does not have the Show Only Related Records step, so it simply activates the record that contains the same serial number as stored in the Current Record global field.

Download an example FileMaker file     .