Searching by Contact doesn't work

[FishGuts]

Hi,

I have a custom module called BuyerJobs. There is a one-to-many relationship between Contacts and BuyerJobs.

On my BuyerJobs screen, the Basic Search block has a 'Contact' search field, because I want to be able to search BuyerJobs by their related Contacts. But this doesn't work. When I enter a Contact's name (say, Bob Brown), none of Bob Brown's BuyerJobs appears in the results list.

I've been scratching my head about this for ages. Do you have any solutions?

I'm using SugarCE 5.2c.

Thank you

AG

[andopes]

Hi AG

Let us know how you added the Contact field into Basic Search and attach into your next post a screen shot of the Search screen.

Cheers

[FishGuts]

Hi André,

Thank you for your swift response. I included the Contact field in the Basic Search by customising the search layout in Studio (after deploying my new BuyerJobs module). As you can see from the attachment, I've labelled the Contact field as 'Buyer'.

By the way, if you're wondering where the 'Advanced Search' tab disappeared to, well, I got rid of it! hehe

Thank you,

AG

[andopes]

It seems the ListView is generating a wrong sql query.
Follow the instrucitions into my last post on this thread.

Cheers

[FishGuts]

Thanks again André.

I managed to partially solve the problem by adding the following line to the BuyerJobs module's SearchFields.php file:

 'contacts_rej_buyerjobs_name' => array('query_type'=>'default','db_field'=>array('contacts.first_name', 'contacts.last_name')), 
  

Now, if I enter part of a Contact's first name or last name, the Contact's BuyerJobs are successfully shown in the results list. This still does not work if I enter the Contact's full name. I suspect this is because a Contact's full name is not an actual database field - it is merely a concatenation done by Sugar code. Is this correct?

AG

[ashishmathur]

This thread helped me too.

But I have updated field definition slightly to improve search capability:

Instead of as mentioned in previous post

 'contacts_rej_buyerjobs_name' => array('query_type'=>'default','db_field'=>array('contacts.first_name', 'contacts.last_name')), 
  

I have used

 'db_field'=>array('TRIM(CONCAT(contacts.first_name," ",last_name))') 
  

[roblaus]

I am sorry to dig up such an old thread but this problem still exists and drives me crazy. Whenever there is custom module which involves a relationship to contacts the search for contacts doesn't work. The reason is rather simple and can be fixed easily by changing the core files but it is extremely difficult to do this in an upgrade safe manner.

Sugar searches in this case not as expected for the ID of the contact but as mentioned above for a combination of last_name and first_name. Unfortunately the salutation is subtracted although there is none which results in a query which looks like this "select .... like 'n Doe'" rather than "John Doe".

The solution above doesn't work because it doesn't take into account that the representation of the name (also in the search field) depends on the user settings - so we may have 'John Doe', 'Doe John' or just 'Doe'.

I'd appreciate if somebody had an easy solution which I could use...

[roblaus]

Here is a fix which will work if you set the Name Display Format in your preferences to first last (with or without salutation) or to last only. It will not work with last, first.

https://github.com/sugarcrm/sugarcrm_dev/pull/87/files

or differently put: change the file include/SearchForm/SearchForm2.php by commenting out this line
if(!empty($salutation) && strpos($field_value, $salutation) == 0)

and adding this line instead
if(!empty($salutation) && strpos($field_value, $salutation) === true && strpos($field_value, $salutation) == 0)

around line no 987

Thanks to Alex from Letrium for helping out here and providing the fix...