Issues with cross module search with enum fields

[kgrise]

I'm able to add fields from other modules in the Accounts advanced search as long as they are text fields. I've followed the instructions I've found in the Sugar forums and blogs, etc. I haven't been able to get the SQL query to work properly for enum fields. The possible values are showing up on the front-end, but in the query Sugar is adding an extra single quote to the beginning of the = clause and an N'% at the end so it ends up looking like this:

WHERE contracts_cstm.preferred_status_c = ''PREFN'%'

When I actually need:

WHERE contracts_cstm.preferred_status_c = 'PREF'

I found one forum post with a similar question, but no one has given a viable answer. Any help is much appreciated.

[kgrise]

I figured out the issue. I worked off of an answer in this post:

http://www.sugarcrm.com/forums/f6/se...modules-57125/

I was using the incorrect query_type in my SearchFields.php. I had 'default' when it should have been 'format' and once I modified the query slightly (as seen in the above example) it's working correctly.

[jmertic]

I figured out the issue. I worked off of an answer in this post:

http://www.sugarcrm.com/forums/f6/se...modules-57125/

I was using the incorrect query_type in my SearchFields.php. I had 'default' when it should have been 'format' and once I modified the query slightly (as seen in the above example) it's working correctly.

Thanks for the update! Could you provide the SearchFields.php entry you added to make the fix?

[kgrise]

Here's what I added to my SearchFields.php:

 'preferred_status' => array(
        'query_type' => 'format',
        'operator' => 'subquery',
        'subquery' => 'SELECT accounts.id FROM accounts LEFT JOIN contracts ON (accounts.id = contracts.account_id) INNER JOIN contracts_cstm ON (contracts.id = contracts_cstm.id_c) WHERE contracts.deleted = 0 AND contracts_cstm.preferred_status_c IN ({0})',
        'db_field' => array(
            'accounts.id',
        ),
    ),