Custom Date fields don't save in MySQL - Solution (4.5.1)

[rbooz]

With SugarCRM version 4.5.0c, I had submitted a bug and posted a thread about custom fields not saving the same as regular fields (http://www.sugarcrm.com/forums/showthread.php?p=62092). Although the bug has never changed status, it does appear that some adjustments were made to the "DynameFields.php" code that actually takes care of saving custom fields. HOWEVER, there is still a problem with fields of type "date". In the code, if the date field is not set, it gets set to 'NULL", which MySQL balks at. No custom fields are saved to the DB (because there is a MySQL error) and nothing is shown in the interface to tell you what the problem is.

The solution, at this point, is to edit the "modules\DynameFields\DynamicField.php" file at/around line 225:

                     if($field['data_type'] == 'date' && (empty($this->bean->$name )|| $this->bean->$name == '1900-01-01')){
                         // Change by Ryan Booz.  Can't set to 'NULL' - MySQL won't take it!
                        // 
                        // Was: $this->bean->$name = 'NULL';
                        $this->bean->$name = '0000-00-00';
                    } 
  

When this change is made, the forms with custom date fields will now save again.

Hope this helps someone.
Ryan

[andydreisch]

rbooz, what's the bug number?

Andy

[rbooz]

Initial bug (from the 4.5.0c issue) was: 10376

New bug (just submitted) is: 11475

[andydreisch]

OK, terrific work ... thanks.

I'll ask the Dev team to address this is an upcoming patch.

Andy

[rbooz]

Totally unreleated to this issue, I still do not get emails from Sugar Team Members in threads that I'm subscribed to. If I hadn't come back to edit my initial post, I wouldn't have seen your question. However, I get posts from other members in each thread. Any ideas on this? I don't see any options in the user CP that relates to this, but it's frustrating when you want to make sure you're keeping on top of things, especially if I'm interacting with someone from Sugar.

Thanks

[rbooz]

OK, nix that. SORRY! It appears that this was an issue of poor timing and my email server choosing to be slow this morning. Oops! I just now received a batch of email from the last two hours and Andy's replies were included. No issue on the email thing.

[wjohnson]

I don't know if it is related but I had a problem with custom date fields not saving as well. We were having to enter 0000-00-00 into every custom date field for any of the custom data to save so that mysql wouldn't return an error. We were able to get around this by turning off mysql strict mode. I don't like that as a fix because of a financial app that we run internally on mysql.

[Napalm Dalmer]

Nice one rbooz! I thought I was going insane when I encountered this problem. So has this been included in the new update then? Thanks again.

Mike

[rbooz]

This is still a problem in 4.5.1b. Changes were made in DynamicFields.php - but the NULL date problem still exists. I approached the fix a little differently this time, since MySQL should accepted non-quoted NULL for anything that is NULL - which is the problem with the date columns. If you put quotes around the NULL, it rejects the insert/update.

My updated code is at http://www.sugarcrm.com/forums/showthread.php?t=22288


Hopefully it helps someone.

BTW, anyone from SugarCRM Team, the bug numbers referenced in this thread do not appear to have ever been touched/assigned.

Ryan