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.