search ignoring relationship

[mikesolomon]

I have added contact to the case search but it is not working

To create the field I copied code from Tasks

My problem is that the sql returned is :

SELECT cases.id , cases_cstm.*, cases.case_number , cases.name , accounts.name account_name , accounts.assigned_user_id account_name_owner , 'Accounts' account_name_mod, cases.account_id , cases.priority , cases.status , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, cases.assigned_user_id FROM cases LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c LEFT JOIN accounts accounts ON accounts.id= cases.account_id AND accounts.deleted=0 AND accounts.deleted=0 LEFT JOIN users jt1 ON jt1.id= cases.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((( contacts.first_name like 'a%' OR contacts.last_name like 'a%' ))) AND cases.deleted=0 ORDER BY cases.case_number DESC

What is missing is

LEFT JOIN contacts ON contact_id = contacts.id

I have built a one-many relationship between contacts & cases and it appears in the relationship table

Any help in getting this working would be much appreciated

I have put in a temporary fix by editing include\ListViewData.php and adding a line:
if ($_REQUEST['module'] = 'Cases') $ret_array['from'] .= ' LEFT JOIN contacts ON contact_id = contacts.id';

But although it works it is a lousy solution

[anamikakhadwal]

please try this
By adding this array in vardef


cases_contacts' => array(
'name' => 'contacts',
'type' => 'link',
'relationship' => 'case_contacts',
'bean_name' => 'Contact',
'source' => 'non-db',
'vname' => 'LBL_CONTACTS',
) ,


hope it will work (not sure )

[mikesolomon]

please try this
By adding this array in vardef


cases_contacts' => array(
'name' => 'contacts',
'type' => 'link',
'relationship' => 'case_contacts',
'bean_name' => 'Contact',
'source' => 'non-db',
'vname' => 'LBL_CONTACTS',
) ,


hope it will work (not sure )

No joy I'm afraid

I already have the following in my vardef

'contacts' => array(

'name' => 'contacts',
'type' => 'link',
'relationship' => 'contact_mcase',
'source'=>'non-db',
'side'=>'right',
'vname'=>'LBL_CONTACT',
),

I tried yours but didnt work

[anamikakhadwal]

Have you repaired "Repair Database" in admin

[mikesolomon]

Have you repaired "Repair Database" in admin

yup did that

I tend to do it whenever I make a change

[mikesolomon]

I have now had to remove my one to many relationship as it was interfering with the many-many relationship contacts-cases that already exists

by doing that and a repair suddenly it is all working

whether I could recreate it is a totally different question

[anamikakhadwal]

thats gr8!!!!!!!!!!

cheers