AnsweredAssumed Answered

Custom search field subquery problem

Question asked by Ramon Marcondes on Nov 29, 2015
Latest reply on Nov 29, 2015 by Ramon Marcondes

Hello.

I need to create a year custom search field on a Listview, but I have only a full date field in the module,

so I do a substring via mysql on the date, to compare the year with the search input.

I followed this tutorial: HOWTO: Add a search field that searches another module « Sugar Developer Blog – SugarCRM

And that's what I done:

 

Code - custom/modules/Project/searchdefs.php:

'ano_padv' => array('name'=>'ano_padv', 'label'=>'Ano', 'type'=>'enum', 'default'=>true, 'options'=> $padv_anos),

 

Code -  custom/modules/Project/SearchFields.php:

  'ano_padv'=>
  array(
  'query_type' => 'default',
  'operator' => 'subquery',
  'subquery'=> 'SELECT id_c FROM project_cstm where substring(data_padv_c, 1, 4) LIKE',
  'db_field' =>
            array (0 => 'id',),
  ),

 

I tried But it doesn't return any result on the page.

I put the site in debug logging, and checked what query it produced in sugarcrm.log. I'm putting here only part of the 'where' section.

 

In 'default' query it generates:

WHERE project.id in ('2013')

 

With my custom subquery, it generates:

where (
 (project.id IN (select  id_c  from
 (SELECT id_c FROM project_cstm where substring(data_padv_c, 1, 4) LIKE '\'2013\'%')
 ano_padv_derived))

 

I don't want, " LIKE '\'2013\'%' ", I want " LIKE '2013%' ", so this query is succeful in mysql workbench.

I tried to concat simple quotes in the substring field: WHERE concat ("'", substring(data_padv_c, 1, 4),"'"), but then I refresh the page, and it's blank, and no query is shown in sugarcrm.log, although the query works in mysql workbench.

 

What should I do to solve this?

What's the correct way to add custom search fields on SugarCrm?

 

I'm using SugarCRM 6.5.20 Community Edition on a Nginx server.

Thanks.

Outcomes