Searching FileMaker – text search operators

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.

The A-Z of FileMaker: M is for Merge

There are a number of methods to display data on a FileMaker layout, the most common being using field objects. Often it is more convenient and flexible to use merge items within a text object.

What can be merged in text?

Text objects can contain three types of merge data:

  • fields
  • variables
  • symbols

All can be accessed via the Insert menu while working in Layout mode.

Insert menu merge

What do merge items look like?

When looking at a text object in Layout mode, merge fields and variables in the text appear within double chevrons <<like this>> and <<$likethis>>; merge symbols are enclosed in double curly braces {{like this}}.

merge items in layout mode

Merge fields

When inserting merge fields, you can specify any field that you could physically place on the layout (current table, related or global). Text objects containing merge fields can be placed as you would normal fields – on the layout, in a portal, in a popover, etc – and will display the same data as it would for a standard field object.

The exception for using merge fields is container fields. If these are merged, the text object will display the container data file name.

Concatenating data

Merge fields can be very useful for stringing together (concatenating) data. A common example is when displaying a person’s full name – you will want a single space between first and last names or perhaps a comma and space between last and first names. This can be done using a calculation field but merge fields for display may save creating a number of calculations.

concatenate with merge

Note also in the above that the merge fields have been formatted with styling (bold and uppercase) within the text object.

Merge variables

You can insert both local ($) and global ($$) variables in text objects. The value of a local variable will only be displayed during the script in which it exists.

To ensure correct display, you will need to use either the Refresh Window or Refresh Object (specifying the named text object) script step.

Tip: Displaying page count

As in the above screenshot, you may want to display the page count in the footer of a report – Page 3 of 10. While the page number is displayed with a symbol, the page count is determined dynamically for the report. When in Preview mode, you will go to the last page using the Go to Record/Request/Page [Last] script step, and set a variable to Get ( PageNumber ).

Idea: use Merge variables as Layout calculations

There have been various feature requests over the years for layout calculations. This feature would reduce the need for calculation fields in tables. Merge variables may satisfy that request in a number of cases. During a script, you can define a variable using any calculation. Then you can display the result of the calculation on a layout using a merge variable.

Merge symbols

Merge symbols use the Get functions available in the calculation engine (see the blog article The A-Z of FileMaker: G is for Get for more). If the function used is Get (AccountName), then the merge symbol is {{AccountName}}.

The merge symbol displays the result of the Get function in Browse, Find and Preview modes. It is evaluated when the text object is drawn on the layout and when it is refreshed.

Challenge

If the {{CurrentTime}} merge symbol is placed on a tab panel that is not the default front tab, when will the current time be evaluated?

a. when the layout first loads
b. when the tab panel is first selected
c. every time the tab panel is selected
d. when any tab panel is selected

(answer is at the end of the article but you would be wise to experiment for yourself to see whether your results agree)

The most commonly used merge symbols are available for immediate selection – date, time, user name, page number and record number. All others can be accessed through the Other Symbol… command and dialog.

Formatting merge data

Data displayed by merge fields, variables and symbols can be formatted for display by applying Data Formatting via the Inspector to the text object.

Only one format per data type (number, date, time) can be applied to a single text object. This means that it is not possible to have two numbers in a single text object display with different formatting.

CHALLENGe Answer

b. when the tab panel is first selected

After that event, it will not be re-evaluated except if the window or object is refreshed. If the window is refreshed and the tab panel is not active, then it behaves as it does when the layout is first loaded – it evaluates next time the tab panel is selected.

Why is this useful to know? It contributes to an understanding of how FileMaker draws layouts and layout objects, and when it evaluates calculations. Such understanding will help a developer to create efficient layouts and troubleshoot performance.