There are many reasons you might need to isolate a single record in a table. This post looks at methods to do this in FileMaker databases, along with pros and cons for each.

1. Find the current record

This method may be the most obvious – perform a find that will return only the current record. To do this, you need to search for something that is unique about the record. While there may be unique data in the record such as a product code or member number, it is always safe to search on the one thing that should always exist and must always be unique – the primary key.

The problem is that the primary key is usually not exposed to the user. So how would they perform a search on that field? The answer is a scripting method that does not require a field to be on the layout. Here is a sample script:

Set Variable [ $key; Value:event::ID ] 
Enter Find Mode [ ]
Set Field [ event::ID; $key ]
Perform Find [ ]

What we are doing here is first storing the primary key value in a variable. Then we get into Find mode and construct the request – setting the primary key field to search for the primary key value we put into the variable. Then perform the find to return one and only one record.

This same script can be written in just two steps:

Set Variable [ $key; Value:event::ID ]
Perform Find [ Restore ]

The Perform Find step has a stored find the same as the one constructed in the first example. Many developers see the first example as self-documenting code. In the second example, you would need to open the second script step options to see what was being restored.

Either way, you will always return just one record using this method. The downside is that this process must be scripted – there should be no user access to the primary key field.

2. Do the Omit dance

Over the years, this has been a popular way to isolate the current record. It uses the scripted sequence:

Show All Records
Omit Record
Show Omitted Only

The advantages of this method are the simplicity and that it can be performed manually with two shortcuts and a menu command. The downside is that there is an edge case where it can break in an active multi-user environment (returning more than one record). If you are scripting this, it may be useful to test that you have a found set of exactly one record before proceeding.

3. Go to Related Record via self-relationship

You could set up a special self-relationship from your main TO (table occurrence) to another TO of the same table. This relationship would use the match:

event::ID = event__same::ID

This is a match for all records with the same primary key (ID) value like the find we did in #1. You can use this relationship to isolate the current record using the script step:

Go to Related Record [ From table: “event__same”; Using layout: <Current Layout> ]

As for #1, this method will always correctly isolate the current record. The downsides are that it is requires a scripted process, and that you need to add a TO to your relationship graph.

Do No Harm

In all of the above, the aim of scripting will be to do no harm (or as little as possible) to the current working environment of the user. For example, if a user has a specific found set of records sorted in a particular order and is currently on the fourth record, that is where you would ideally return the user after they run a script.

In most cases, this is most effectively done by creating a new window and performing all following operations in there. At the end of the process, you can simply close the window and that returns the user to their starting environment.

Other Methods?

Do you use any other methods to isolate the current record? Post a comment below.