Defining field indexing options

Contents > Creating a database > Setting options for fields > Defining field indexing options

Defining field indexing options

In FileMaker Pro, you can create indexes, which are lists of the words or values in a field. FileMaker Pro uses indexes for searching and for joining related tables. Indexes increase the speed of searches but also increase file size.

FileMaker Pro uses different indexes for different tasks:

Value indexes can be created for text, number, date, time, and timestamp fields, as well as calculation fields that return results of these same types. Value indexes are used for joining related records and for searches in number, date, time, and timestamp fields, and calculation fields that return results of these same types. A value index is created by taking each line of text (delimited by the carriage return character) and taking up to the first 100 primary character weights that all the characters in that line generate, according to the Unicode Collation Algorithm. For more information, see Choosing a language for indexing or sorting.

Note Some behavior is dependent upon the data source or drivers when using Japanese collation that does not distinguish Katakana or Hiragana, either in full- or half-width. This is also the case with roman characters with accents.

Word indexes can only be created for text fields, where they are used for searches. A word index is created by storing each unique word in a field. Fields containing large amounts of text can generate large indexes, as each unique word in the text field appears in the word index. This can significantly increase file size.

To set indexing options for a field:

1.	With the database open, choose File menu > Manage > Database.

2.	In the Manage Database dialog box, click the Fields tab.

3.	If your database contains more than one table, select the appropriate table from the Table list.

4.	Click the field name.

5.	Click Options (or double-click the field name).

6.	In the Options for Field dialog box, click the Storage tab.

If you selected a calculation field, you see the Specify Calculation dialog box. Click Storage Options.

7.	Select indexing options for the field.

Select

None

Prevent FileMaker Pro from indexing the field.

Minimal

Create a value index of a text field's contents or a calculation field returning text results.

All

Create both word and value indexes for text fields or calculation fields returning text results. For number, date, time, and timestamp fields, as well as calculation fields returning results of these types, All creates an index of a field's values.

Automatically create indexes as needed

Create the necessary index only if a user performs a search using the field or if the field is used as a match field in a relationship.

A language from the Default language list

Specify the language that is used for indexing and sorting values in a text field. Each language is tailored to support language-specific indexing and sorting requirements.
For more information, see Choosing a language for indexing or sorting.

8.	For calculation fields, select Do not store calculation results if you want FileMaker Pro to calculate the result only when needed, then click OK.

9.	Click OK to close the Options for Field dialog box, or click another tab to set additional field options.

Notes

You can define storage and indexing options for text, number, date, time, and timestamp fields. You can also index calculation fields if the results are text, number, date, time, or timestamp.

FileMaker Pro stores most calculation field values immediately after the field is defined, when the Manage Database dialog box is closed. By default, calculations that include a related field, summary field, global field, or a reference to another unstored calculation are unstored; all other calculations are stored.

Stored results require more disk space. Unstored results require more time to calculate.

For normal use, use None or Minimal and enable the option to Automatically create indexes as needed.

Selecting All for text fields can significantly increase file size, as every word in the text field will be indexed. Certain operations, such as importing records, may also take more time, as each word in the field will be added to the field's index as the import occurs.

The Automatically create indexes as needed option indexes the field the first time a user performs a find request (searches) on the field. The first search is slow because the index is being created. However, subsequent searches on that field are faster because they use the index. (This option also creates an index when the field is used in a relationship.)

To create relationships using text fields as match fields without creating word indexes for these fields, use Minimal and disable the option to Automatically create indexes as needed.

To reduce file size and prevent users from creating indexes, use None (or Minimal) and disable the option to Automatically create indexes as needed.

The combination of selecting None and disabling the option to Automatically create indexes as needed will also prevent the field from being used to create relationships.

For databases that will be placed on CD-ROM or other read-only media, any field that could be used in a Find should be set to Indexing All (if disk space on the CD-ROM allows).

Related topics

Using a field index

Choosing a language for indexing or sorting

Contents > Creating a database > Setting options for fields > Defining field indexing options