I have a custom module that links to the Case module. In the Quick Create screen, I have a field that when I type in, it does an autocomplete on the Case name. For example, if I have a Case called “Test Case”, in that field in the Quick Create screen, if I type “T”, I get a drop-down with “Test Case” in it.
However, I want the field to look up and display the case number and case name (e.g “1 Test Case”). To this end, I created a compound field in the Case module:
'full_name' => array ( 'name' => 'full_name', 'type' => 'relate', 'source' => 'non-db', 'vname' => 'LBL_FULL_NAME_TITLE', 'save' => false, 'id_name' => 'casescases_ida', 'link' => 'case_cases', 'table' => 'cases', 'module' => 'Cases', 'rname' => 'full_name', 'db_concat_fields' => array(0 => 'case_number', 1 => 'name'), ),
The problem is that the query that is generated by SuiteCRM to do the lookup doesn’t work:
SELECT cases.* , ..., LTRIM(RTRIM(CONCAT(IFNULL(cases.case_number,''),' ',IFNULL(cases.name,'')))) as full_name FROM cases, ... where ((cases.full_name like 'T%')) AND cases.deleted=0 ORDER BY full_name LIMIT 0,30
The problem is the condition “cases.full_name like ‘T%’”, since cases.full_name does not exist.
Tracing through the code, the problem is in modules/Home/QuickSearch.php, method constructWhere():
array_push( $conditionArray, $table_prefix . $db->getValidDBName($condition['name']) . sprintf(" like '%s'", $like) );
The field is always prefixed with table name, which is wrong in this case.
I can’t see an obvious work-around, apart from creating a custom field Case that is set in a before_save logic hook.
Does anyone have any other ideas?