Cannot edit Opportunity name

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

Any ideas anyone?

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

Help!

Hi blloyd,

go to studio. select the module, select field

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,

  1. you have created the field manually but the field is not present in the database table.
  2. or one required field you delete which is need so it is showing null.

check it out.

You’re quite right Jaydeep - there is a “null” field listed. How do I remove it?

Thanks.

From studio You can’t remove.

just check what field was there previously that might have been deleted.

check. revert back that particular field.

dont try to create new field it will not reflect anything.

try it and let us know.

Hi Jaydeep,

Try as I might, I cannot find the missing field in either the query that is failing (Query.txt below) or in the Opportunities vardefs.php

Can you please have a check and see what I am missing?

Thanks again,

Ben

Sorry, here is vardefs.php saved as a text file.

ok i will check and get back to you. give some screenshots.

  1. studio -> field one.
  2. database table fields, screeshoots. mean no need of data, where dataypes are menioned.( desc tablename).

All the related field with opporunity

Thanks Jaydeep - here they are.

HI blloyd,

here is your error.

see,

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’.

Your problem will be solve.

1 Like

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.

Do you know how I can delete this unwanted field?

Thanks.

Hi blloyd,

As per my knowledge You can not delete that particular field. beceause that is not custom field .

if you are not able to recover that field,

Then Reset the opportunity module,


Studio->Opportunity->top right corner [RESET MODULE] Module.

Do this and check.

:cheers:

1 Like

Thanks Jaydeep - that appears to have solved the problem. I can now edit and save Opportunity names without any errors.

Bingo.

My pleasure.

:cheer:

2 Likes

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:"

‘textarea_c’ =>
array (
‘labelValue’ => ‘TEXTAREA’,
),

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.

‘textarea_c’ =>
array (
‘labelValue’ => ‘TEXTAREA’,
),

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!

Note: forum user: gazoutback is now gazza73

Gary

note: User: gazoutback is now forum member: gazza73