Account search does not work

[Sukale]

Dear all,

we have updated our Sugar CRM to Version 5.1.0b But still the search function in Accounts does not work.

I have modified the layout in the studio. The Basic search function does not show any records if I search for accounts assigned to one special employee. If I use a combination in Advanced search (e.g. Name + Assigned to + Industry) sometimes it shows some records, sometimes it does not although there is such an account.

Does anybody have the same problem and how did you fix it?

Thanks for any support in advance

Regards

Lars

[andopes]

Hi Lars

Edit the file include/ListView/ListViewData.php and add the code:

 echo "<LI>$main_query"; 
  

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']; 
  

Save the file and go to Accounts ListView again.
A sql query will be displayed in the screen. Copy it and paste into phpMyAdmin. Run it.
phpMyAdmin should warn a error message.
Let us know both query and error.

Cheers

[Sukale]

Hello André,

thank you for your fast reply.

Here is the query:

SELECT accounts.id , accounts.name , accounts.industry , accounts.rating , accounts.billing_address_city , accounts.billing_address_postalcode , accounts.annual_revenue , accounts.employees , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users'
assigned_user_name_mod, accounts.assigned_user_id , accounts.created_by FROM accounts LEFT JOIN users jt0 ON jt0.id= accounts.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where ((accounts.assigned_user_name like 'Username%')) AND accounts.deleted=0 ORDER BY accounts.name ASC

and here we are with the error message:

Unknown column 'accounts.assigned_user_name' in 'where clause'

Thank you for your help

Regards

Lars

[mikesolomon]

Hello André,

thank you for your fast reply.

Here is the query:

SELECT accounts.id , accounts.name , accounts.industry , accounts.rating , accounts.billing_address_city , accounts.billing_address_postalcode , accounts.annual_revenue , accounts.employees , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users'
assigned_user_name_mod, accounts.assigned_user_id , accounts.created_by FROM accounts LEFT JOIN users jt0 ON jt0.id= accounts.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where ((accounts.assigned_user_name like 'Username%')) AND accounts.deleted=0 ORDER BY accounts.name ASC

and here we are with the error message:

Unknown column 'accounts.assigned_user_name' in 'where clause'

Thank you for your help

Regards

Lars

I had this problem

I solved it by editing custom/modules/Accounts/metadata/searchdefs

replace the assigned_user_name bit with

 'assigned_user_id' => 
      array (
        'name' => 'assigned_user_id',
        'type' => 'enum',
        'label' => 'LBL_ASSIGNED_TO',
        'function' => 
        array (
          'name' => 'get_user_array',
          'params' => 
          array (
            0 => false,
          ),
        ),
        'default' => true,
        'sortable' => false,
      ),

[Sukale]

Hello Mike,

thank you for your support. Unfortunately we were not able to fix the problem. We still get the same error message.

Any further suggestions?

Regards

Lars