Finding Data - File Maker

Of course, no database would be that useful if you couldn’t query it. You know, “Show me all records that . . .” or “Only show me records where . . .” FileMaker has some pretty simple yet powerful features for finding records.

Exploring Find Mode

  1. Go into Find mode in Company.fp5, then press CONTROL-F/ COMMAND-F or choose Find Mode from the View menu. All the fields will go blank. You’re now looking at your first find request.
  2. Type ACME into the Company Name field and press ENTER or click the Find button on the status area. (If it isn’t on, turn it on now.) Under the book on the status area you’ll see that you’ve found one record out of nine. Great! Try a few more basic finds. When in Find mode, create multiple requests, and try to use the Omit checkbox on the status area, which means “Don’t find records with this find request’s criteria.”

Using Find Symbols
Now enter Find mode again. Notice the little drop-down menu on the status area next to the word Symbols that looks like this:

Using Find Symbols

You can use these symbols to help you find certain records or bits of data. But what if you only know the first two characters of what you’re looking for? Or you want to find all records with “Kabi” as the first name while excluding the “Kabita” first name records? The following symbols can help.

< Less Than
Find all records that, in this field, have a value less than what you entered. For instance, you might enter “<03/03/2003” in a date field to find all records with dates earlier than 03/03/2003. Same goes for times and numbers. You can also enter “<B” in a text field to find all records that have no words in that field that start, alphabetically, after A.

<= Less Than or Equal To
This works just like less than, but includes the value you enter in the found set too. For example,“<=03 / 03 /2003” would find dates earlier than and including 03/03/2003. Dates 03/04/2003 and later would be excluded.

> Greater Than
The opposite of less than, this finds all records that are greater (date-wise or alphabetically, and so on) than what you type — for example, “>1pm” in a time field or “>1000” in a number field.

>= Greater Than or Equal To
This works just like greater than, but includes the value you enter in the found set too. For example, “>=1pm” would find times earlier than and including 1 p.m. Times even one second later than 1 p.m. would be excluded.

= Exact Match
Use this to find the exact whole-word match within a field. For instance, searching for “Jack” in a name field would find “Jack Kerouac” and “Jack Frost,” but would not find “Jackson Hewitt” or “Action Jackson.”

You can double this up, too: Searching for “=house =beautiful” would find “house beautiful” and “beautiful house” in a field.

. . . Range
This is used to find things within a range that you specify. Searching for “1 . . . 100” finds all numbers within that range, including one and 100. The same goes for date ranges, time ranges, and even text ranges (“a . . . g” finds all records where a word in that field starts with something within that alpha range). You can use just two periods (..), too.

! Duplicates
This means show all records where the data in the specified field is identical to a data entry found in the same field in another record in the database. This is useful for finding duplicate customers that you accidentally entered twice, determining how many Browns you have in a contact database, or how many people ordered Massage Tool #1 in an invoice line items database. (Find for dupes in the City field in Company.fp5 and see what turns up.)

// Today’s Date
This means show all records where the date in this field is the same as today’s date. You might use this to find how many orders you’ve received today (which you can then print to a report).

? Invalid Date
This is used to find an invalid date (or time) in a date or time field, or a calculation with a date or time result. (You may have noticed already that invalid calculated dates or times show up as a “?” in browse mode.)

@ One Character
This is used to find any records with at least one character in the field. It can be used like @ except that the * can represent multiple characters. For example, “Jo@” would find Joe, Jon, Job, and John. This only works on text or number (or like-defined calculation) fields.

* Zero or More Characters
This is used when you don’t know one of the characters in your search or when you want to find many records with mostly similar values. For instance, searching for “Jo*” would find Joe, Jon, and Job. It wouldn’t find John, though, because * cannot be used to find the “oh” in “John,” because the “oh” is more than one character. (See the next symbol for how to search for more than one character.)

““ Literal Text
This is used to find an exact string of characters. Finding for “"Andy’s Candies"” would find any record with that exact phrase, in that word and punctuation order in the field. By way of comparison, just finding for “Andy’s Candies,” not surrounded by quotes, would find “Andy’s Candies” but also “Candies Andy’s.” You can also use literal text to find punctuation and such. For example, “.” would find the period character in a field.

== Field Content Match
Using two equal signs in a row “==” means “Show any record containing exactly what I type, in the exact order that I typed it. For instance, finding for “==Andy’s Candies” would find “Andy’s Candies” but not “Andy’s Candies Limited.”

Tips for Using Find Symbols
Here are some miscellaneous tips and facts regarding finding records:

  • You can combine many of the above symbols when searching. For example, “*ecr@s” would find secrets, decrees, or even descriptions.
  • Less is more. If at first you don’t find what you want by typing “Halifax, Dame Bertha,” try searching for just “hal*” and see what comes up.
  • Use “Insert from Index” to enter find request data into a field accurately, based on the data that’s in the field throughout the database.
  • Finds go fastest on indexed fields.

Multiple Find Requests
You can have an almost infinite number of find requests in any given find. To enter a second request for a find, press CONTROL-N/ COMMAND-N or pick Add New Request from the Requests menu in Find mode, then enter the criteria for the second request.

For instance, to find all records in Company.fp5 with “West Lafayette” and “Indianapolis” in the City field, the first find request would have “West Lafayette” in the City field, and the second request would have “Indianapolis” in the City field.

Using Omit
The Omit checkbox in Find mode means “Find records that don’t match this find request’s criteria. For instance, to find records in Company.fp5 with “West Lafayette” as the city but omit any records in the state of Illinois, the first find request would have “West Lafayette” in the City field, and the second request would have “Illinois” in the State field with the Omit box checked.

Other Requests Menu Commands
There are more commands available under the Requests menu in Find mode that you’ll find useful. They are:

Duplicate Request
Makes another find request based on the current find request. This speeds find request entry when you have many similar requests.

Show All Records
Means “I’m done with this found set now, forget it, and bring me back to browsing all records in the database.”

Perform Find
This is the same as pressing ENTER or the Find button on the status area. This performs the find based on the find request(s) you enter.

Constrain Found Set
Use this when you want to do what many call a “find within a find.” For example, say you’ve just done a find on the State field for “IN” and have a small found set (try this in Company.fp5). Now, you would like to do another find for just the records within the current found set that have “Indianapolis” as the city. To do so, enter Find mode again, enter Indianapolis in the City field, and select Constrain Found Set. Now you should see only the Indianapolis, IN records.

Extend Found Set
This is kind of an “and also find,” meaning “OK, I’ve found all the ‘IN’ state records; now I want to add all the ‘IL’ records to the found set too.”

For example, say you’ve just done a find on the State field for “IN” and have a small found set (try this in Company.fp5). Now, you would like to do another find outside of the current found set to also find records that have “IL” as the state. To do so, enter Find mode again, type IL in the State field, and select Extend Found Set. Now you should see a slightly larger found set containing all the IN and IL records.

Revert Request
Resets a find request to the way it was the last time you committed it (clicked outside of a field on the layout somewhere). Thus, if you typed some parts of your request then clicked off the request and changed the request, you could press Revert Request to restore the request to what it was before the second edit, provided you haven’t committed the second request yet.

Modifying Last Find
When you select Records > Modify last Find in browse mode you’re brought into Find mode with the last used set of find requests/ criteria restored. This is useful when you need to refind something you just found.

From the Field: Finds Without Find Mode
More advanced users can allow users to find records without letting them into Find mode by using a global field, a relationship, and a script. To see this in action, click the Find button in Company.fp5. This is useful when you don’t want a user to ever be able to get into Find mode or you want to strictly control when, where, and on what fields a user can find on.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics