Find and Replace in FileMaker
Just for the fun of it, I read up on the Find and Replace feature in FileMaker - a feature I rarely use. Why not? Because I am a developer. I use the FileMaker's Find function, the Replace All function, or a script designed to do exactly what I want done. Admirable, but not always the best way to proceed. And isn't developing (and life, for that matter) all about doing things the best way possible?
Therefore, I have resolved to use the Find and Replace feature when appropriate, and here is part of the reasons why:
- It's possible to find and replace data across multiple fields (including related fields) in a record or in a find request
- You can find and replace across a found set of records or find requests
- You can find and replace across text objects in a layout while in layout mode
Find and Replace is easy to use: In Browse, Find, or Layout mode, choose Edit menu> Find/Replace> Find/Replace, and you'll see a somewhat familiar window:
Pretty similar to what you find in word processing or spreadsheet programs. Type what you want to Find in "Find what:" box, and what you want to replace in the "Replace with:" box. Pretty simple, very powerful.
In FileMaker, though, you can search across all records, current record, all fields or the current field, search forward, backward, or in all directions, and can match case or whole words only.
But, of course, there are exceptions
- You can search for data in any type of field except container fields
- It's OK to search for data in fields that are viewable but not modifiable – but you can't replace data in those fields.
- It's not possible to replace data in fields that are formatted as pop-up menus, radio buttons, or checkboxes. Those exceptions can easily trip you up. Those fields will be counted and reported as skipped at the end of a "Replace All" operation, or FileMaker just beeps when you search.
What about invisible characters?
You can find and/or replace invisible characters. The trick is to manually select and copy those characters, such as tabs or carriage returns, from a field into the Find what or Replace with box.
What about Match whole words?
Here's what FileMaker has to say:
A Match whole words only Search for only those occurrences that are whole words or are bounded or are bounded by word breaks. A word break in FileMaker Pro is defined as the blank space or a punctuation character on either side of a word when it is double-clicked in a field.
Please show some examples:
The following examples are from this FileMaker Knowledge Base article:
- Searching for "good" finds "good" and "good" within "good-bye"
- Searching for "travel" finds "travel" and "travel:" within "travel.com" but ".com", but does not find "travel.com"
- Searching for "stone house[space]" finds "stone house" in the sentence "The old stone house[space][space]has a large garden," but does not find "stone house" in the sentence "The old stone house[space]has a large garden."
- Searching for "customers" finds "customers'" but does not find "customer's" nor will "customer's" find "customers" since the apostrophe is considered a character.
Using the option "Search across" in Browse, Find or Layout mode:
- In Browse mode you can search across all records in the current layout or just in the current record
- In Find mode, you can search across all find requests in the current layout or just in the current find request
- In Layout mode, FileMaker Pro searches just in the current layout.
Use the option "Search within" in Browse, Find or Layout mode to search within all fields in the current layout or just in the current field.
Click one of the buttons in the open window to perform the type of find/replace operation you want.
- "Find Next" finds the next occurrence
- "Replace & Find" replaces the "Find what" data with the "Replace with" data, and keeps doing it each time you click
- If there is no selected data that matches the "Find what" data, it searches for and select the first occurrence of the Find what data
- "Replace" replaces the "Find what" data with the "Replace with" data
- "Replace All" replaces all occurrences of the "Find what" data with the "Replace with" data. At the end of the "Replace All" operation, you see a summary of the number of occurrences found and replaced.
That's pretty much it.
If you have never used Find and Replace in FileMaker, give it a shot. You'll see that, just like any function, there are occasions when it is the best tool for the job.