So I upgraded my SugarCRM 6.5.17 installation to SuiteCRM 7.1.3 (Build 1220) but there is a problem when trying to rename an Opportunity. Clicking Save generates a database error. Here is the output from sugarcrm.log
Fri Sep 5 07:34:01 2014 [3730][1][FATAL] Error updating table: opportunities: Query Failed: UPDATE opportunities
SET name='2014-09 KIT September content',date_modified='2014-09-05 07:34:01',modified_user_id='1',created_by='1',description='2 x monthly posts for the Kimbley IT blog',assigned_user_id='1',opportunity_type=NULL,campaign_id='',lead_source='Web Site',amount=150,amount_usdollar=150,currency_id='-99',date_closed='2014-09-30',next_step=NULL,sales_stage='Closed Won',probability=100,=NULL
WHERE opportunities.id = 'e3fe4b5f-d819-9814-7ff5-53fda3f104b2' AND deleted=0: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=NULL
WHERE opportunities.id = 'e3fe4b5f-d819-9814-7ff5-53fda3f104b2' AND' at line 2
This problem seems to affect almost any aspect of editing an Opportunity. Just got the following database error when trying to assign a contact to an Opportunity:
MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=NULL
WHERE opportunities.id = '792d4f48-6506-ec30-d59a-5408a2e68323' AND' at line 2
check any empty row is there or not which showing null.
if it is there,
then you might delete some field(most probably after probability field ). so only it is showing like that. go to vardefs.php file of opportunity file recreate the file.
the thing is that, there are two possibilty is there,
you have created the field manually but the field is not present in the database table.
or one required field you delete which is need so it is showing null.
1. in the database table , last column is "Opportunity". But in your,
2. Vardefs.php file no field is define called Opportunity. (I think you previously you added now you deleted. write that)
'opportunity' =>
array(
'name' => 'opportunity',
'vname' => 'LBL_OPPORTUNITY',
'type' => 'int',
'dbType' => 'char',
'audited'=>true,
'validation' => array('type' => 'range', 'min' => 0, 'max' => 100),
),
like this as per your required you have to add after âProbabilityâ.
Thanks for your help Jaydeep. As you suggested, the extra code added to the vardefs file has created a new Opportunities field in the module. However the NULL field still exists and is still causing problems when editing Opportunities.
Just want to say thankyou for the solution/approach for this. In our case, we discovered that the NULL line seen in Opportunities, was because of /var/wwwthe existence of /sugarcrm/custom/Extension/modules/Opportunities/Ext/Vardefs/sugarfield_textarea_c.php
This seems to have arrived as part of the migration from sugarCRM v6.5.15 to suiteCRM 7.1.4, as part of the sugarfiled-jjwg_maps* modules.
Performing an ADMIN --> Repair would cuase the content of sugarfield_textarea_c.php to create the following array (with insufficent content) to be placed inside of /var/www/sugarcrm/cache/modules/Opportunities/Opportunityvardefs.php which I show here:"
The NULL content error showing up in sugarcrm.log when editing an opportunity, as due to the presence of the above âtextareaâ column containing no settings other that a label value of âtextareaâ.
We simply hid what we beleive to be an unnecessary file: to a â.ORIGâ extension
root@ourhostname:/var/www/sugarcrm/custom/Extension/modules/Opportunities/Ext/Vardefs# mv sugarfield_textarea_c.php sugarfield_textarea_c.php.ORIG
and then performed an ADMIN --> repair --> Repair and rebuild.
Thanks again!
PS: Still trying to see what/how the arrival of the sugarfield_textarea_c.php file came into existence, and hat possible new issue I may be intrducing by this workaround. But this was the result of a major migration from sugarCRM v5.2.0f under Ubuntu 8.04LTS to sugarCRM v6.5.15 / Ubuntu 14.04LTS followed by a migration to suiteCRM v7.1.4 over the last few days, for a legacy application, And it has all been a great outcome! Cheers. Gaz
I think my reply to this topic got lost just now, so Iâll have another go.
Thankyou for the suggestions in this topic - they worked well for me, I too could of edit opportunities, and the problem was a filed with NULL content found with Studio in Opportunities. In my case, it was due to the existence of the following file:
/var/www/sugarcrm/custom/Extension/modules/Opportunities/Ext/Vardefs/sugarfield_textarea_c.ph
This file seems to have come about as part of a major upgrade through many legacy versions of sugarCRM 5.2.0f thru 6.5.15 followed by a migration to suiteCRM v7.1.4 whilst also upgrading the environment from Ubuntu 8.04LTS thru 14.04LTS in the last few days.
The above file generated the following code into /var/www/sugarcrm/cache/modules/Opportunities/Opportunityvardefs.php when doing a ADMIN --> repair and rebuild.
So we discovered a NULL column with STUDIO in the Opportunities table.
Hiding the suspect file as sugarfield_textarea_c.ph.ORIG
and performing a ADMIN --> repair and Rebuild
solved the problem. Rows in Opportunities can again, be edited.
We chose not to reset the module, because we had one additional customer relationship linking opportunities to custome module (jobfl_houses) and did not want to disturb that aspect.
Thanks again, jaydeep for publishing your solutions and suggestions. Brilliant!