No Opportunities Search After Upgrade

[integralsugar]

I recently upgraded our opensource Sugar instance from 5.0.0d to 5.1.0b and now the Opportunities page doesn't appear to work. The dashboard still shows the opportunities and users are able to add and edit opportunities if they are in the last viewed bar. We are unable to search or display a list of the opportunities. In addition, the unified search will not show any opportunities.

There are no errors in the logs and the upgrade said it completed successfully. I also restored sugar from backup and re-ran the upgrade on a different system with the same results. In addition, I ran all of the repair items and cleared the cache.

SELECT * FROM opportunities WHERE deleted=0; returns 500+ results.

Does anyone know of a way to fix this problem? Has anyone else experienced this problem?

Thanks for your help.

[andopes]

Hi integralsugar

Edit the file include/ListView/ListViewData.php and add this line

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

Open the Opportunity ListView again, you will see a query.
Copy this query and run into phpMyAdmin. You should get an error message.
Let us know both query and error message.

Regards

[roblaus]

On top of what André said: make sure that you have build 4905. 5.1b was issued twice.

[integralsugar]

Thank you Andre,

Query:
SELECT opportunities.id ,opportunities_cstm.Project_Type_c,opportunities_c stm.a_c,opportunities_cstm.b_c,opportunities_cstm. b_c_c, opportunities.name , accounts.name account_name, jtl0.account_id account_id, opportunities.sales_stage , opportunities.amount_usdollar , opportunities.date_closed , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, opportunities.assigned_user_id FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_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= opportunities.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where opportunities.deleted=0

Error Message (from mysql command prompt since phpMyAdmin isn't installed.):
ERROR 1054 (42S22): Unknown column 'opportunities_cstm.b_c_c' in 'field list'

I'm assuming that I'll get the same mesage from the mysql command prompt, but if this isn't the case, I'll install phpMyAdmin.

[integralsugar]

Thank you Robert. I do have Version 5.1.0b (Build 4905) installed.

[andopes]

Hi integralsugar

opportunities_cstm.b_c_c

This field is right weird.
Let us know the content of the file cache/modules/Opportunities/Opportunityvardefs.php

[integralsugar]

Please see attached Opportunitityvardefs.txt

[andopes]

There are the fields b_c and b_c_c.
Did you create both?
Are them different ones?
I'm not sure but perhaps the two _c are getting SugarCRM confused.
Try to remove the b_c_c field and create a new one without two _c.

Regards

[integralsugar]

Thank you Andre!

After doing some searching around, it appears that someone attempted to create and then delete that field. It looks like it was deleted from the opportunities_cstm table, but didn't get deleted from whatever table stores the custom field information.

All that needed to be done to remedy the situation was to delete that field int the module editor.