Quick search not working with compound field

SuiteCRM Forum - English Language Developer Collaboration
1

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

2

Are you sure that you are using the correct field names?
Custom field names always end with _c (underscore followed by the letter “c”.
Maybe this is your problem.
Or else you should dig more into the manual (check SugarCRM CE 6.5 manual) and, possibly, the code, to better understand what can be achieved and how

3

Thank you for response.

However, I am confused as to how me having incorrect field names (which they aren’t) would result in SuiteCRM generating invalid SQL (as far as I know, it is invalid to have a field alias in the WHERE claus prefixed by the table name).

Now, I am quite prepared to accept that somewhere in the documentation there is some caveat that you cannot use compound fields in this context, but I have not been able to find that or any similar limitation.

Regards,

Carl