Search problem

[siteware]

Hi,

I've defined a custom checkbox field(is_company) for the contacts module.
When I use this field in the search panel, it shows a dropdown with 'NO', 'YES' and empty options.

When I select 'NO' and do a search, the search is executed correctly, but the search value 'NO' is not remembered but reset to the 'empty' value on return, so when I go to the next result page, the query is changed and also the result list!

[andopes]

Hi siteware

Which SugarCRM version are you using?
Did you customize your SugarCRM?

Regards

[siteware]

5.2.0.

Customization: yes, added some custom fields in the contacts module and changed the search layout.

[siteware]

After further investigation it seems that the problem only occurs with custom fields. Build-in fields, like the 'do not call' field does not have this problem.

[siteware]

It turns out, it also happens with build-in fields, strangely enough, the behaviour is not very consistent.

I got one step further however, I found out I had to add the custom fields to modules/Contacts/metadata/SearchFields.php to be remembered after search.

But going to the next page of search results still resets the search value for checkbox fields (not-always).

[jcb23]

Windows XP and Vista
Sugar 5.1.0b
Apache 2.2.4
PHP: 5.2.3-1ubuntu6.3
MySQL Server: 5.0.45-1ubuntu3.3

I'm having a similar issue. When I click No the search pulls both those records where the checkbox isn't checked as well as those where the checkbox is checked. It is with a custom field and when I insert

 <LI>$main_query"; 
  

into include/ListView/ListViewData.php is shows that the query isn't looking at that field at all in the where clause:

 SELECT contacts.id ,contacts_cstm.misc_specialties_c,contacts_cstm.gender_c,contacts_cstm.salary_c,contacts_cstm.received_cv_c,contacts_cstm.home_state_c,contacts_cstm.citizenship_c,contacts_cstm.status_c,contacts_cstm.leisure_interests_c,contacts_cstm.hometown_state_c,contacts_cstm.board_certified_c,contacts_cstm.positions_preferred_c,contacts_cstm.hometown_state2_c,contacts_cstm.hometown_country_c,contacts_cstm.facility_employee_c,contacts_cstm.notes_c,contacts_cstm.date_available_c,contacts_cstm.states_licensed_c,contacts_cstm.special_skills_c,contacts_cstm.languages_c,contacts_cstm.regions_preferred_c,contacts_cstm.hometown_country2_c,contacts_cstm.facility_position_c,contacts_cstm.amt_c,contacts_cstm.states_preferred_c,contacts_cstm.degree_c,contacts_cstm.hometown_city_c,contacts_cstm.hometown_city2_c,contacts_cstm.identification_number_c,contacts_cstm.candidate_c,contacts_cstm.target_list_c,contacts_cstm.middle_initial_c,contacts_cstm.interested_in_c,contacts_cstm.cities_preferred_c,contacts_cstm.allied_health_c,contacts_cstm.industry_c,contacts_cstm.cont_address_id_c,contacts_cstm.mass_email_date_c,contacts_cstm.int_level_c,contacts_cstm.title_c,contacts_cstm.spouse_name_c,contacts_cstm.spouse_can_id_c,contacts_cstm.currency_id,contacts_cstm.mass_email_ae_c,contacts_cstm.ae_c, CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, contacts.assigned_user_id 
FROM contacts 
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c 
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0 
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 
LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 
where contacts.deleted=0 ORDER BY name ASC 
  

Here is the query that runs when I select yes:

 SELECT contacts.id ,contacts_cstm.misc_specialties_c,contacts_cstm.gender_c,contacts_cstm.salary_c,contacts_cstm.received_cv_c,contacts_cstm.home_state_c,contacts_cstm.citizenship_c,contacts_cstm.status_c,contacts_cstm.leisure_interests_c,contacts_cstm.hometown_state_c,contacts_cstm.board_certified_c,contacts_cstm.positions_preferred_c,contacts_cstm.hometown_state2_c,contacts_cstm.hometown_country_c,contacts_cstm.facility_employee_c,contacts_cstm.notes_c,contacts_cstm.date_available_c,contacts_cstm.states_licensed_c,contacts_cstm.special_skills_c,contacts_cstm.languages_c,contacts_cstm.regions_preferred_c,contacts_cstm.hometown_country2_c,contacts_cstm.facility_position_c,contacts_cstm.amt_c,contacts_cstm.states_preferred_c,contacts_cstm.degree_c,contacts_cstm.hometown_city_c,contacts_cstm.hometown_city2_c,contacts_cstm.identification_number_c,contacts_cstm.candidate_c,contacts_cstm.target_list_c,contacts_cstm.middle_initial_c,contacts_cstm.interested_in_c,contacts_cstm.cities_preferred_c,contacts_cstm.allied_health_c,contacts_cstm.industry_c,contacts_cstm.cont_address_id_c,contacts_cstm.mass_email_date_c,contacts_cstm.int_level_c,contacts_cstm.title_c,contacts_cstm.spouse_name_c,contacts_cstm.spouse_can_id_c,contacts_cstm.currency_id,contacts_cstm.mass_email_ae_c,contacts_cstm.ae_c, CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, contacts.assigned_user_id 
FROM contacts 
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c 
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0 
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 
LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 
where ((contacts_cstm.amt_c like '%1%')) AND contacts.deleted=0 ORDER BY name ASC 
  

You can see the difference here as the second query is what it should be and the first is missing

 where contacts_cstm.amt_c like '%0%'