freetextindex Table (46)
This is the index table for the free text search function. Each word in FreeTextWords can have multiple occurrences in this table. Each record in this table points to one occurrence of the word, and points to both the table in which the word occurs (which might be contact or text), and also a pointer to the owner table (which is one of contact, person, project, appointment or sale).
Fields
| Name | Description | Type | Null |
|---|---|---|---|
| freetextindex_id | Primary key | PK | |
| freetextwords_id | Reference to word | FK freetextwords | |
| table_id | Source table number - could be main (contact, person, etc) or sub-tables like address, email | TableNumber | |
| record_id | Source record - the row that contains the word. Might be contact record, or an address or phone | RecordId | |
| ownertable_id | Logical source table (high-level), like contact or project | TableNumber | |
| ownerrecord_id | Logical source record. The contact, project, sale that the source belongs to. | RecordId | |
| infile | Word found in file (0=word found in database) | UShort | |
| contact_id | Set for contacts and person records to allow cross-table free-text searches. 0 for non-contact, non-person | FK contact |
Indexes
| Fields | Types | Description |
|---|---|---|
| freetextindex_id | PK | Unique |
| table_id, record_id | TableNumber, RecordId | Index |
| freetextwords_id, ownertable_id | FK, TableNumber | Clustered |
| contact_id | FK | Index |
| ownerrecord_id, ownertable_id, freetextwords_id | RecordId, TableNumber, FK | Index |
Relationships
| Table | Description |
|---|---|
| contact | Companies and Organizations. |
| freetextwords | This table contains the word list (automatically updated by the indexer) for the free-text search. Words are in UPPERCASE and truncated to 9 significant characters. Indexed words, dynamically updated by the freetext index |
Replication Flags
- Copy to satellite and travel prototypes.
Security Flags
- No access control via user’s Role.