In our first post of this series, we looked at the FileMaker search engine – how to access it, and how it operates. In this post, we will start looking at specific searches as they apply to different data types for which you might search.
The article got so big that we had to split it up! We will consider searches in text fields to start.
Data types
When creating fields in a table, you specify a name and a data type. FileMaker provides several data types for fields:
- text
- number
- date
- time
- timestamp
- container
In addition, calculation fields are defined with a data type for the result. If a calculation field is searched, it behaves in much the same way as a standard field for the specified data type.
Searching in text fields
Text fields are the most common field type in databases. In FileMaker solutions, text is the default data type allowing a ridiculous amount of data to be stored in one field in one record. Text fields can store any characters –alpha, numeric, symbols.
The default search in a text field is “words beginning with”. So a search in an address field for st will find all records containing any words beginning with “st” – St Agnes Road, Storey Lane, Franklin Street, etc. Note that the word can be anywhere in the field – it is not “data begins with”.
What is a word?
A word is a string of characters delimited by a word separator. What is considered to be a word separator? A FileMaker Knowledgebase article on the subject notes the following as recognised word separators:
space ! @ # $ % ^ & * ( ) _ + =
{ } [ ] | \ : ; " ' < > ? / * ~ -
The article notes exceptions when characters like the hyphen, colon, forward slash, a single quote and a period (.) are not treated as separators.
In most cases, text field searches are not case sensitive. An exception is where the default language of the field is set to Unicode (Field Options – Storage). A search for dar will return the same results as a search for DAR or a search for Dar. So be lazy in your searches and don’t bother with the uppercase.
Search operators
FileMaker provides a number of operators (symbols) to perform searches other than “words beginning with”.
The first to consider is the asterisk (*). This represents zero or more characters – any characters. Thus, a wide-ranging search is performed by wrapping a string in asterisks like *dar*. This will search for records where the field contains the string dar anywhere in a word – start, middle or end.
Search far and wide
Perhaps the most wide-ranging search you could perform is *d*a*d*. This would find any record where the field contains dad in that order but anywhere in a word in the field. So it would find dad, David, dreadful, underhanded, and advantaged. But not addition or biddable.
Find ‘something’
The asterisk is very useful when searching for records that have something (anything) in a field. For example, if you wanted to find all contacts with an email address, just type * into the email field and perform the find.
The second operator is the at symbol (@). This represents exactly one character. So a search for s@mon will find Simon and Symon, but not Salmon or Simone. Note that when the @ symbol is used, the search is no longer for “words beginning with”.
The third operator is equals (=). This searches for an exact word. So a search for =ann will find Ann but not Anne or Anna or Annabelle or Annette.
Find nothing
The equals sign is very useful when searching for records with empty fields. For example, enter = in the company field to find all contacts for whom no company is recorded.
Two words, Any order
To search for two exact words in any order, precede both words with =. So a search for =hotel =grand will find both the Grand Hotel and Hotel Grand (but not Grandfather Hotel).
The fourth operator is two equals symbols (==). This searches for exact content in the entire field. So a search for ==Melbourne would find records with only the word Melbourne in the field and not Melbourne City or Melbourne CBD.
The fifth operator is the double quotes (“”). This searches for a phrase beginning at the start of a word. So a search for “little street” will find all records with that exact string somewhere in the address field but will not find one with Little Bourke Street. A search for Little Street (without the quotes) would find that record since it is searching for any record containing those two words in any order.
Combine with * to extend
If you add in the asterisk and search for *”e street”, it would find all streets with names ending in e. In other words, the search starts from anywhere in the field, not just the start of a word.
The sixth operator is the exclamation mark (!). This searches for duplicates of the entire field. So a search for ! would find two records with Swanston Street but would not consider Cnr of Bourke and Swanston Street to be a duplicate.
Search for an operator as a character
So what if you want to search for one of the recognised operators in a field? Maybe you are looking for records containing * or @ or !. The backslash character (\) is used to ‘escape’ the following character in a search.
For example, if you search for info@ulearnit.com.au, there would be no records returned even if there was one with that email address. The solution is to escape the @ symbol like this: info\@ulearnit.com.au.
If you need to search for a recognised operator, you can also use quotes. For example, search for “@” or “!”.
next Up
In the next post, we will look at number and date searches.
One Response
Hi David,
Having problems with a search which finds more than one record of different types… ie Bitcoin and Bitcoin Cash… I get both result returned.
I want to get Bitcoin only.
I set a text string to a variable $var and then search on it.
It is when I search for $var and use = or ==.
If I use just $var then I get results.
If I use = or == $var I get “cannot find records”.
I don’t understand what is going on here.