Hello.
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?
Thanks,
Carl