Find Similar Records

Imagine your viewing an invoice for a customer named John Osborne and you want to quickly view all the invoices for Mr. Osborne. You could manually select his last name or customer number, enter find mode and perform a find. But, wouldn't it be easier if you had a button that did all that for you? There are several approaches to writing a script like this. We will cover four of them: Copy & Paste, Paste from Last Record, Set Field and Relationship.

Copy & Paste

Using the Copy and Paste script steps is the most straightforward of all the approaches. It works just like you would do it manually. Just copy a value that uniquely identifies the customer, paste that value into find mode and perform the find. In the example file provided, the full name is used as the unique find criteria. However, you could use a Customer ID field to prevent problems where two people have the same name.

Here is the complete script:

The advantage of this method is the ease of implementation. The disadvantage is that Copy destroys the contents of the clipboard where the user could have something stored. In addition, the Copy and Paste script step requires that the field be on the current layout.

Paste from Last Record

Most people don't realize that the Paste from Last Record script step will work in find mode. Paste from Last Record will copy any field value from the last active record to the current request in find mode. The key is to remember what designates an active record. It's not the last record you were viewing. It is the last record where the cursor was in a field. That means you have to use the Go to Field script step to make sure the current record is also the active record.

Here is the complete script:

The advantage of this technique is that you can grab multiple field values without using multiple Copy and Paste script steps. This might be helpful if locating records similar to the current one involves multiple criteria. The biggest disadvantage to this method is that it requires the field to be on the current layout. If the field you need to identify the customer is not one you show on the layout, you'll need to switch layouts.

Set Field

It is not possible to set values from browse mode to find mode unless they are stored in global fields. Therefore, in order to use the Set Field command, you have to add at least one global field to temporarily hold the unique value from the current record. To prove that global fields are accessible while in find mode, manually enter find mode to see that the browse mode value remains.

Here is the complete script:

TIP: Using global fields from find mode presents all kinds of possibilities. Try using a global field to display custom messages in find mode. This will enable you to use one find layout for multiple find scripts.

The biggest advantage of the Set Field method is that it does not need the field to be present on the current layout. This allows you to hide the unique identifier field from the user. However, the Set Field method requires you to add a global field which adds overhead. This isn't too bad if you design your database solution with Global Temp field that you use as a repository for all temporary data. However, multiple find criteria would require multiple global fields. The Past from Last Record technique only needs to add script steps.

Relationship

One of the coolest aspects of relationships is that they can perform finds. This is done using the Go to Related Record script command with the Show Only Related Records option. It is not a complete replacement for find mode, but is perfect for this solution. All you have to do is add a single relationship using the unique identification field on both sides of the relationship definition. In the case of the example file provided, a calculation concatenating the First Name and Last Name fields serves to differentiate between like records. The relationship uses the Full Name calculation on both sides of a same file relationship (self-join).

NOTE: 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. Never check the option for deleting related records when creating a same file relationship since the related records are the same as the master file records. It can cause a problem called cascading deletes where deleting one record deletes all the records in your database.

Here is the complete script:

That's right! You only need a single script step. That's the biggest advantage besides not needing any special fields to be on the current layout. The biggest disadvantage is the creation of a relationship. It is always a good idea to limit the number of relationships you add to a file to cut down on the overhead.

Download an example FileMaker file     .