Index properties

On the Index properties tab, which is present when you have selected a new or existing index in a database, you determine the general properties of this index.
Click here for information on how to edit properties in general. And click here to read about how to manage objects in the tree view in the Application browser.

Important: you must build up this index by reindexing it - the index table will be (re)filled with data - after you have set all the properties of a blanc new index, or when you have modified the properties of an index definition in any way. Reindexing a new index is also necessary if the field does not yet contain any data, to create the relevant SQL table.

On the current tab you'll find the following settings:

Internal name

Enter a unique name for the index (note that names which only differ in casing are not unique). This name will only be used within the database setup. If you create access points for a Collections application, you can use other names. The name may contain an underscore but no full stops, asterisks, minus signs or punctuation marks.
When you define a new index, it will automatically be given a name: substitute this name with a more appropriate one.
Index names which are only different in casing, e.g. Ab_index and ab_index, are not allowed because SQL usually sees them as one and the same and this can lead to duplicate table names. Designer 7.13 and up automatically check if existing index names are unique this way when a database definition is loaded in the Application browser. Any errors will be reported in the main Designer Window. Also when creating a new index will this check be performed and if the name already exists you must enter a different name.
The Application tester reports on duplicate names too.

Key type

For an index you must specify its key data type. It sets the way the data in the index table is structured and treated when you search it. It also determines the default sorting order of a search result from this index. For example, if you would like the result of a search on the object number field to be sorted alphanumerically, instead of alphabetically, make sure the key type of its index is set to Alpha-numeric, not to Text.
The index key type is relevant for the sorting of records in the Hierarchy browser as well: here the key type will determine the type of sorting, while the Sort values option for the relevant internal link will determine if sorting must be applied at all, and if so, whether it must be ascending or descending. This is particularly interesting if the main term field of the internal link contains numerical or alphanumerical values, like the object_number field.
The available index types are:

Text (term) - A Text/term index is used if you want to search on the whole field value (or a first part of it), e.g. in the case of keywords. A term index contains keywords in the form of text. The entire content of the field becomes a key value in the index. Any spaces and punctuation marks in the field's contents will be included. If the field is a repeatable field, each repetition will be a separate index key.
Integer - An integer index is used for data consisting of whole numbers, e.g. quantities, record numbers, and such. This type of index may contain integers between -2147483648 and +2147483647.
Free text - You should use a Free text index (also called a word index) if you want to search all the words in a text field separately, e.g. in the case of book titles, when you want to be able to search on each word from the title individually.
A free text index contains not the words themselves but reference numbers for the words in the word list table. The word list itself is a text (term) index without fields, containing separate words, without spaces or punctuation marks.
Date - A date index contains dates in numeric form, which means that this index does not store the actual date but the number of days since 1/1/1900. This index type accepts dates in the following notations:
 
European:
dd/mm/yy    (e.g. 31/12/94)
dd/mm/yyyy  (e.g. 31/12/1994)
 
Julian:
yyyy.ddd    (e.g. 1994.365)
 
ISO:
yy-mm-dd    (e.g. 94-12-31)
yyyy-mm-dd  (e.g. 1994-12-31)
 
Only these two variations of an ISO date can be expressed in number of days since 1/1/1900. Other legal ISO dates, like '1984' or '1970-04' can only be indexed using the ISO date index type.
Note that users cannot search truncated on years if dates have the Date type of index, so dm = 2020* only works if dm has been indexed as an ISO date.
Logical - A logical index is an index on a field which is one position long. If the field contains any character, its contents are considered 'true'. If it does not contain a character, the field contents are considered 'false'.
ISO date - This type (which can only be applied to index tables in SQL databases) indexes all legal ISO dates from an ISO date field, like 2009-01-31, 2009-01 or 2009, in a numerical format in which the hyphen between the year and month is substituted by a dot, and the second hyphen is removed, e.g. 2009.0131, 2009.01 or 2009. This behind-the-curtains conversion ensures that ISO dates in this index are always sorted correctly. Also make sure that the Locale option in the Database properties has been set, otherwise the index cannot function properly.
Numeric (floating point) - Use this index type for numerical fields only.
Alpha-numeric - If a field contains a combination of text and numbers and you want the index to sort the text part of a value alphabetically and the numbers numerically (because you want a search result sorted that way), then this index type may be the solution. For example, if two values "a10" and "a9" would be sorted alphabetically only, then a10 would come before a9 because 1 comes before 9 in the alphabet, but if sorting is alphanumeric then a9 comes before a10 because 9 is numerically smaller than 10. To achieve this, each alphanumeric value in the index is reformatted so that every number in the string becomes a ten-digit number starting with zeroes. For example, when in a record the string A238HJK08aa2 has been registered, the index value (of which the user can remain unaware) becomes A0000000238HJK0000000008aa0000000002. So, if the field you wish to create an alphanumerical index for, contains values with many letter-number alterations, the index values can become pretty lengthy and therefore you should set the Key size option of this index sufficiently high: however, the key size for an index in a SQL database is limited to 100 characters, so if you employ a SQL database and you estimate that any reformatted alphanumeric strings might exceed 100 bytes, then it's best not to create an alphanumerical index for this field and just accept alphabetical sorting.
A noteworthy consequence of an alpha-numerical index is that when you search it on a truncated value you'll only get the result you might expect when you truncate the search key after a letter or after a complete number: it is not possible to search truncated on a partial number, because behind the scenes, any numbers in your search key are considered complete already and are reformatted to ten-digit numbers before truncation is applied. This means that truncated searching on "a9" will find "a9" and "a9cba" but not "a90" or "a90bca". Truncation after a letter still works as you would expect. So truncated searching on "abc" will find "abc9" and "abc903" and "abcd943io".
Double meta phone - In a Double MetaPhone index, the entire field content is indexed as one key value, encoded according to its English pronunciation, so that of an incorrectly spelled search key the correct variant can still be found as long as the pronunciation is the same. It is not possible to search truncated in this index. See the description of the Phonetic index type property for more information.
Stripped - This index type was originally introduced to be able to index ISBNs without any concatenators or separator characters in it. So, only the "stripped" version of field content is indexed in an index of this type. For example, if a field contains the ISBN: 90 272 2167 7, it will be indexed as: 9027221677. This makes sure that ISBNs or similar data in this index are always sorted correctly.
Period - Index type for fields of data type Period. See the full implementation topic for more information.

Note that not defining an index type is an application error; nonetheless, an undefined index type will be interpreted as a Text type index.

Date completion

This option appears if the Key type for this index has been set to ISO date. Use this option to complete partial dates when they are indexed - a partial date would be just a year, or the year and a month. This is particularly relevant to date range searching in SQL databases (see the Date range search method type), for which indexed dates need to be complete dates, but you can use it for other purposes as well.
In the case of a date range, an incomplete start date (only a year or year and month) must be equalled to the first day of the year or month, while an incomplete end date must probably be equalled to the last day of the month or the year. Set the current option for the index of the start date field to First day of period, or set it to Last day of period for the index of the end date field. For example:

Start date in record

End date in record

Start date in index

End date in index

1960

1960

1960-01-01

1960-12-31

1950

1952

1950-01-01

1952-12-31

2000-03

2000-03

2000-03-01

2000-03-31

Do this for both the start date index and the end date index of each date range.
Also, the date indexes for a date range should only index the first occurrence of the relevant date fields. So mark the First occurrence only option in the properties of the relevant indexes.
Reindex the adjusted indexes to apply the changes.

Tags to index

There will usually be a one-to-one relationship between an index and a field, in which case one specified tag is all you need here. Enter the tag of the data dictionary field from which this index derives its values in the white entry field (not in the list below it). The tag may be a maximum of two characters long.

You may also index a field linked on forward reference. In that case, the key data field in the primary database cannot be indexed, because the data for the key field is not saved in this database, only retrieved temporarily. Instead, you can define an index (of the integer type) for the forward reference field. (The only place where you refer to this indexed forward reference tag instead of the linked field itself, is when you create a method/access point for the relevant linked field. In screens you always associate an entry field with the linked field name or tag, never the forward reference tag.)

Indexing merged-in fields might be problematic, since the value of a merged-in field is only present in a record when that record is being edited: it is retrieved from a linked record and displayed with a linked field in the current, main record, but not stored in this record. Indexes are updated each time a record is saved, which means that an index on a merged-in field may be built up correctly as long as it is being updated after a main record has been edited and saved, but not whenever the linked record is edited and saved (because then the main record with the linked field and merged-in field won't be opened and saved by Collections): in this case, the index on the merged-in field will keep the old value until the main record is opened and saved again in some way. If the index on the merged-in field is ever reindexed from within Designer, it will even result in a completely empty index, since the main records have never stored any merged-in values. So it's best not to create an index on a merged-in field. However, there are ways to create an access point for a merged-in field: see the Search key property of methods for more information.

Further, there is a possibility to search on a combination of fields, e.g. by using a combination of title and abstract. To implement this, you must specify a unique tag in the white entry field, that does NOT occur as a field tag in the database yet (but then you must add it still) or that you created specifically already to specify the new index tag*. You then Add the tags of the database fields to be indexed together, to the list below the first white entry field.
Just as with the other indexes, you have to specify the main (the first) index tag as the Search field for the appropriate method (access point) that you specify in the application setup.
For example, suppose you want to allow users of your public access catalogue to search a number of fields with one search command, fields such as title, series, annotation and abstract. To make such a "free text" access point, you would define an index tag vt for instance, of the Free text type with the Tags to index: ti, re, an and sa. (The tags in this example may be named differently in your application.)
If you need a term index on a long text field (like Title), for instance because you'd like to have a linked Title field somewhere or because you wish to create an access point on a merged-in Title field (see the Search key property of methods for the other half of this story), then proceed in the same way: create a new tag and a term index for it, and in this index include the tag of the Title field as the second tag to index. (This won't effect any existing free text index on the Title field.)

Note that you should only combine fields of the same type (e.g. Free text fields) in one index. And they must all be monolingual or all multilingual. And a combination of forward reference tags is only allowed if the linked-to fields are located in the same database.

* If you wish to index a combination of fields, it's mandatory to create a separate tag in the data dictionary for the new index tag that you provide for the current property, with the same tag name as that index tag. This reduces the risk of using the index tag for some other purpose too.

Reindex (button)

To make it easier to index or reindex an index after you created a new index or after changing an existing index definition, a Reindex button is present next to the Tags to index list.

Domain name tag

Here you enter the tag in which the domain names belonging to each term or name to be indexed can be found in this database (often this is do). This applies only to indexes that will contain terms belonging to different domains, so indexes on certain fields in validation databases only (like term in the thesaurus). The appropriate domain is then added to each field value in the index and makes validating and searching on domains possible.
(If searching or validating on domains will not be necessary, you needn't fill in any Domain name tag.)

First occurrence only

Here, you indicate whether only the first occurrence must be indexed (mark this checkbox), or all occurrences (leave this checkbox unmarked).

Unique keys only

When entering a record, on leaving a field which is indexed (or when saving the record) this option checks whether the entered value already exists in another record (by checking this index) in the current database table. If it does, you will see the message: This term already exists, only unique terms permitted. Mark this checkbox to set unique indexing. Leave this checkbox unmarked, if keys in this index needn't be unique. After changing this option, either from non-unique to unique or vice versa, you must reindex this index because uniqueness is (must be) a property of the index table in the SQL database too.
If the indexed field is multilingual, an entered value must be unique in the current data language; a term can appear more than once in this type of index only if the term has been entered in different data languages. For example, in a multilingual field, the term "software" can be registered in every data language available in the application, and still be indexed with the Unique keys only option set.

(If you just want to check for uniqueness in one or more specific datasets within the current database, you cannot use the Unique keys option. Instead, use the VALUECOUNT function in a before-storage and/or after-field adapl for the relevant database and/or fields.)

In SQL databases only the first 100 characters (bytes actually) of the value entered in a field can be indexed. With the Unique keys only option set, this means that values longer than that, which share an identical first 100 characters, will be considered identical (generating an error 91: double key) even though the 101th or further characters may differ. The solution is to either enter values which differ in the first 100 characters or to switch Unique keys only off for the relevant index.

If a unique index is not necessary but you do want to check whether the index value already occurs in the record you are working on (in the case of a repeated field), you must specify the screen field property Repeatable for this field as Repeated Unique, in the Screen editor.

Page size (in bytes)

This option is not relevant to Axiell Collections.

Key size(in bytes)

This value only applies to a Text/term index. For SQL databases it's minimally 1 and maximally 100*.
As long as you respect these limits, you can set this value the same as the Maximum length specified for the field of this index, but for terms within a domain you must specify a larger Key size than the field length because in the index not only the key is stored, but in front of it also the domain to which the key belongs (separated by two colons).
On the other hand you can also specify the Key Size to be shorter than the Maximum length of the field, if you would want to index only a first part of the text in this field.

Designer will allocate the correct length for the integer, date, free text and logical index types automatically.

* Indexes in SQL databases cannot have a key length greater than 100 characters. If you specify a greater key size anyway, and you try to save a 100+ character term in a record, an error 29 will occur. The record will be saved anyway, but the relevant term index won't be updated. An example of a long term index is TX (a term index on the Title field in the DOCUMENT database).

Phonetic index type

This index type is not supported by Axiell Collections.

Metadata type

This index type is not supported by Axiell Collections.