CiviCRM Full-text Search does not include obvious fields like: Job Title for contacts and many other useful fields
CiviCRM Full-text Search does not include obvious fields like: Job Title for contacts and many other useful fields. In fact, it only covers a limited subset of fields. See CRM/Contact/Form/Search/Custom/FullText.php
public function buildTempTable() { $table = CRM_Utils_SQL_TempTable::build()->setCategory('custom')->setMemory(); $this->_tableName = $table->getName();
$this->_tableFields = [
'id' => 'int unsigned NOT NULL AUTO_INCREMENT',
'table_name' => 'varchar(16)',
'contact_id' => 'int unsigned',
'sort_name' => 'varchar(128)',
'display_name' => 'varchar(128)',
'assignee_contact_id' => 'int unsigned',
'assignee_sort_name' => 'varchar(128)',
'target_contact_id' => 'int unsigned',
'target_sort_name' => 'varchar(128)',
'activity_id' => 'int unsigned',
'activity_type_id' => 'int unsigned',
'record_type' => 'varchar(16)',
'client_id' => 'int unsigned',
'case_id' => 'int unsigned',
'case_start_date' => 'datetime',
'case_end_date' => 'datetime',
'case_is_deleted' => 'tinyint',
'subject' => 'varchar(255)',
'details' => 'varchar(255)',
'contribution_id' => 'int unsigned',
'financial_type' => 'varchar(255)',
'contribution_page' => 'varchar(255)',
'contribution_receive_date' => 'datetime',
'contribution_total_amount' => 'decimal(20,2)',
'contribution_trxn_Id' => 'varchar(255)',
'contribution_source' => 'varchar(255)',
'contribution_status' => 'varchar(255)',
'contribution_check_number' => 'varchar(255)',
'participant_id' => 'int unsigned',
'event_title' => 'varchar(255)',
'participant_fee_level' => 'varchar(255)',
'participant_fee_amount' => 'int unsigned',
'participant_source' => 'varchar(255)',
'participant_register_date' => 'datetime',
'participant_status' => 'varchar(255)',
'participant_role' => 'varchar(255)',
'membership_id' => 'int unsigned',
'membership_fee' => 'int unsigned',
'membership_type' => 'varchar(255)',
'membership_start_date' => 'datetime',
'membership_end_date' => 'datetime',
'membership_source' => 'varchar(255)',
'membership_status' => 'varchar(255)',
// We may have multiple files to list on one record.
// The temporary-table approach can't store full details for all of them
// comma-separate id listing
'file_ids' => 'varchar(255)',
];
$sql = "
";
For the Full-text Search feature to meet end-user expectations, all text fields need to be covered including custom fields which have been identified as "Is Searchable" = True.
For me, this again raises the question as to why MySQL Views are not being used in CiviCRM instead of creating a temporary table and copying the data. There's probably a good reason for the current approach, but it does seem unnecessarily resource-intensive.
Given the effort required to make this functional, it may be just simpler to either remove this feature entirely or provide better documentation on the page to explain the search scope and manage expectations.
Agileware Ref: CIVICRM-1401