5. select and sort data records

In gFM-Business there are several ways to search for data records and create certain selections. Data records usually have to be found in two cases:

• Targeted search for a specific data record
• Selection of a certain quantity of data records according to previously defined criteria; for example, for the creation of a mailing.

To search for a specific data record, you can, for example, search for the customer number, invoice number or the name of a company. For this search, either the search mode described below or the quick search in a list view can be used (see section 5.6.1).

5.1 The search mode

Click on the button Search in the toolbar to activate search mode (see section 5.4). Search mode is indicated by the fact that all fields have a dashed frame and a magnifying glass symbol. Search mode can be called up from all screen layouts, except in the Settings program section, as settings only consist of one data record.

After calling up the search mode, any search terms can be entered and combined with each other. When performing a search query, gFM-Business searches all data records in a table and compares the criteria entered with the data in the database. Only data records that meet the search criteria are then displayed in the selection. The data records found, the so-called Result setis a subset of all existing data records in the database called up. Data records that do not match the search query are not displayed and are excluded. With the button Change query buttons in the toolbar to restrict or expand the result set (see section 5.2).

5.1.1 Searching for text

Search for text or text passages in text fields. Unless you are searching for word combinations and exact matches, the field found can contain not only the specified text, but also other values in any order. For example, if you enter "Hotel" in the "Company" field, gFM-Business will find records for "Hotel", "Wellness Hotel" and "Hotel & Restaurant".

Words that begin with certain characters:
Chris Schmidt
...finds Chris Schmidt, Chris Schmidtmeyer and Schmidt Christiane.

A phrase or character string that is to be searched for exactly:
"Hotel Jonas & Fischer"
...finds only "Hotel Jonas & Fischer"

Words with an unknown variable character:
B@rg
...finds Berg, Burg and Borg.

Invalid characters
? (question mark)
...finds invalid characters

Several unknown characters:
Mei*er
...finds Meier, Meisner and Meitinger.

Digits in a text field
# (for each digit)
...finds 3, but not 30

Parts of a phrase (a string of words or characters):
*"son & Philips"
...finds "Johnson & Philips", but also "Paulson & Philips".

Exact match:
== (two equal signs)
==Johann
...finds Johann, but not Johann Schmidt.

Exact match with the given whole words:
= (an equals sign)
=Chris =Schmidt
...finds Chris Schmidt or Schmidt Chris, but not Chris or Christoph Schmidtmer.

5.1.2 Searching for numbers and dates

A number in a number field
0,50
...finds 0.50 and 50%.

A date in a date field
3.3.2005
...takes place 3.3.2005, March 3, 2005 and 03-03.2005.

Today's date in a date field
// (two dashes)
...finds today's date

Invalid dates
? (question mark)
...finds invalid entries

Date values on a specific day of the week in a date field
Tuesday
...finds all entries that fall on a Tuesday

All valid values for a date or time component
* (star)
...finds all valid values

5.1.3 Searching for value ranges

Smaller than the specified value
Example: <40, <31.12.2005

Less than or equal to the specified value
Example: <=40, <=31.12.2005

Greater than the specified value
Example: >40, >01.01.2006

Greater than or equal to the specified value
Example: >=40, >=31.12.2005

Within a specified range. A range differs depending on the data type. Numbers (smallest to largest), date (earliest to latest), time (earliest to latest), text (first to last word).
... (three points)

Examples:

In June 2005
6.2005

From July 1997 to October 1998
7.1997…10.1998

From 1.7.1997 to 31.10.1998
01.07.1997…31.10.1998

Between 8:00 am and 8:00 pm
8:00…20:00

5.1.4 Searching for empty or non-empty fields

Search for non-empty fields (i.e. fields that contain data):
* (star)

Search for empty fields
= (equal sign)

5.1.5 Searching for duplicate values

You can find all data records for which one or more fields contain duplicate values. Example: You want to check all non-unique data records and decide which of these data records you want to keep and which you want to delete. To find all duplicate data records, enter the following in search mode Exclamation mark (!) into the field to be compared.

5.1.6 Using multiple search criteria

You can narrow or broaden your search by entering a range of search criteria.

Logical AND search

Enter your desired search terms in search mode and confirm the entry with . All data records that match all the criteria entered will then be found.

Logical OR search

To expand your search query with search criteria that MAY also match the result set, click on the "New query" button in the toolbar in search mode.

A new empty search query will then appear in which you can enter further search terms. You can create as many search queries as you like and navigate in them in the same way as in data records in browse mode. A query can be deleted again using the "Delete" button in the toolbar.

5.1.7 Restricting a result set

You can narrow down the search result step by step and search for more specific details in your database. For example, you can first search for all contacts of the customer type "Customer" and then limit the search to all customers in a specific zip code area.

• In search mode, search for all contacts with the customer type "Customer" and confirm the search query with .
• Switch back to search mode and enter a zip code area in the <> field.
• Select "Queries > Restrict result set" in the menu bar.

5.1.8 Expanding a result set

You can expand a result set and extend your search query so that it finds additional valid data records without having to start all over again. For example, you can first search for customers from the zip code area 26125 and then extend the search to customers from the zip code area 26131.

• In search mode, search for all customers from the zip code area 26125 and confirm the search query with .
• Switch back to search mode and enter 26131 in the <> field.
• Select "Queries > Expand result set" in the menu bar.

5.1.9 Changing the last search query

If you have carried out a search query and would like to extend it subsequently, click on the "Change" button in the toolbar. gFM-Business then switches to search mode with the search criteria you entered in the last search query.

5.1.10 Hiding and showing data in a result set

All data records that are not contained in a result set have been excluded and are therefore not visible. You can exclude further data records from the result set without performing another search query.

Important! Excluded data records are temporarily removed from the result set group. However, they are not deleted from the database.

To exclude one or more data records from the result set, click on the "Exclude" button in the toolbar.

A dialog box then appears for specifying the number of data records to be excluded, based on the current data record. After confirming the dialog box, the specified number of data records is excluded from the current result set.

5.1.11 Quick search based on field content

You can perform quick searches based on field data without having to switch to search mode. You can perform quick searches based on an entire field or a text selection within a field.

• In browse mode, select the text in a field that you want to use for the search.
• Right-click on the selected text and select Search for matching data records in the context menu.

5.2 Help popover in search mode

As the basis of gFM-Business, FileMaker offers an above-average number of flexible options for searching and selecting data records. The operating concept of gFM-Business makes it possible to perform search queries on all fields of the database. FileMaker offers an abundance of placeholders for search queries in order to be able to optimally execute every possible search query.

gFM-Business 3.6 contains in all masks in the Search mode now has a help popover in which all available placeholders for the search are documented. In this way, you can easily execute even complex search queries with placeholders for e.g. filled or empty fields and always have an overview of all available commands.