Search partly broken

[xadora]

Hello!

We have problems with the search field both basic+advanced in all modules.

a) When "Only my items" is checked not items are displayed at all

b) When we search by custom field (here a relation to a product) there is also nothing displayed

Do i need to use the repair function anyhow?

We are using Sugar CE 5.2f, recent extension was the enhanced search module. Problems occured after the extension of that module, which doesn't mean that they need to caused by that.

[andopes]

Hi Ole

If you have access to sugarcrm files do the following:
Edit the file include/ListView/ListViewData.php and add this line

 echo "<LI>$main_query</LI>"; 
  

Just after the line

 $main_query = $ret_array['select'] . $params['custom_select'] . $ret_array['from'] . $params['custom_from'] . $ret_array['where'] . $params['custom_where'] . $ret_array['order_by'] . $params['custom_order_by']; 
  

Make another search and let us know the displayed query.

Best regards

[xadora]

with "my items only" checked:

# SELECT leads.id ,leads_cstm.date_until_c,leads_cstm.do_not_contact _c,leads_cstm.wrong_person_c,leads_cstm.retired_c, leads_cstm.pdx_product_id_c,leads_cstm.information _sent_c,leads_cstm.pdx_product_id1_c,leads_cstm.pd x_product_id2_c,leads_cstm.pdx_product_id3_c, leads.date_entered , jt0.name productrelated_c , CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')) as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.status_description , leads.account_name , leads.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, leads.assigned_user_id FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN pdx_product jt0 ON leads_cstm.pdx_product_id3_c = jt0.id AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= leads.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((leads.assigned_user_id = '9aaae306-e63e-4c76-e8b7-4771a2e474d2' ) AND ( leads.status like '%-blank-%')) AND leads.deleted=0 ORDER BY leads.date_entered ASC

with custom product selected:

SELECT leads.id ,leads_cstm.date_until_c,leads_cstm.do_not_contact _c,leads_cstm.wrong_person_c,leads_cstm.retired_c, leads_cstm.pdx_product_id_c,leads_cstm.information _sent_c,leads_cstm.pdx_product_id1_c,leads_cstm.pd x_product_id2_c,leads_cstm.pdx_product_id3_c, leads.date_entered , jt0.name productrelated_c , CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,'')) as name, leads.first_name , leads.last_name , leads.salutation , leads.status , leads.status_description , leads.account_name , leads.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, leads.assigned_user_id FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN pdx_product jt0 ON leads_cstm.pdx_product_id3_c = jt0.id AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= leads.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((leads.status like '%-blank-%' ) AND ( pdx_productproductrelated_c like '%Think PROFIT%' ) AND ( leads_cstm.pdx_product_id3_c like '%411b983b-a873-4922-4bfc-48303645a6d2%')) AND leads.deleted=0 ORDER BY leads.date_entered ASC

Both from the leads module

Any help appriciated!

[andopes]

Try to execute these queries on phpMyAdmin and check if it returns any error message.

Regards

[xadora]

#1

#1054 - Unknown column 'leads_cstm.do_not_contact' in 'field list'

This part of the query is marked red:

CONCAT( IFNULL( leads.first_name, '' ) , ' ', IFNULL( leads.last_name, '' )

#2 same as in 1

[xadora]

ok, i can now repoduce the error

once the enhanced search plugin is removed, the sugar search function works as it shoulds. After installing it again it's again broken.

Perhaps somebody can help. I really would like to use the enhanced search.

[kenshiro]

ok, i can now repoduce the error

once the enhanced search plugin is removed, the sugar search function works as it shoulds. After installing it again it's again broken.

Perhaps somebody can help. I really would like to use the enhanced search.

It seems that you've added a custom field called "do_not_contact" to the Leads module.
Then maybe it hasn't been removed correctly and the query cannot find it.

Is there a "do_not_contact" custom field in the Advanced Search panel ?

[xadora]

Yes it is. After i removed it and reinstalled enhanced search, the problem persists.

Do I need to use the repair function somehow?

[kenshiro]

Yes it is. After i removed it and reinstalled enhanced search, the problem persists.

Do I need to use the repair function somehow?

Can you check the DataBase if the table 'leads_cstm' and the field "do_not_contact _c" exist ?

[xadora]

Both table and field exist. The field contains of 0/1.