Blank Custom Date fields break insert/updates 4.5.1b

[rbooz]

It seems with every upgrade/patch, the custom fields break for us. When I do a little tracking, it's always found it's way back to "DynamicFields.php". This is what determines the insert/update string for adding any custom field data. In almost every instance, it has to do with the way dates are handled. We have custom date fields that do not always get filled in and are maked as "allow NULLS" in the database. However, MySQL can't accept quoted "NULL" as a value for fields, it has to be a simple NULL (no quotes) to be accepted. The changes to "DynamicFields.php" still don't take them into consideration correctly. Below is the changes I made to mitigate the situation until it can be fixed in a new patch. For more background, see http://www.sugarcrm.com/forums/showthread.php?t=18775

Look for the "save()" function in DynamicFields.php (modules\DynamicFields). Starting around line 211 here are my changes. I added a small if...else block and changed a few other fields. I kept all original code, just commented it out.

                 if(isset($this->bean->$name)){
                    $quote = "'";
                      if($field['data_type'] == 'int' || $field['data_type']== 'float'){
                               $quote = '';
                               if(empty($this->bean->$name) && !is_numeric($this->bean->$name) ){
                                       if(strcmp($field['required_option'], 'required') == 0){
                                               $this->bean->$name  = 0;
                                       }else{
                                               $this->bean->$name = 'NULL';
                                       }
                               }
                       }
                    if($field['data_type'] == 'date' && (empty($this->bean->$name )|| $this->bean->$name == '1900-01-01')){
                         $this->bean->$name = 'NULL';
                    }
                    
                    // Added by Ryan Booz
                    if($this->bean->$name == 'NULL') {
                        $updateValue = " $name=".PearDatabase::quote(from_html($this->bean->$name));
                        $insertValue = " ,".PearDatabase::quote(from_html($this->bean->$name));
                    } else {
                        $updateValue = " $name=$quote".PearDatabase::quote(from_html($this->bean->$name))."$quote";
                        $insertValue = " ,$quote".PearDatabase::quote(from_html($this->bean->$name))."$quote";
                    }
                    // End of add
 
                    if($isUpdate){
                        if($first){
                            //$query .= " $name=$quote".PearDatabase::quote(from_html($this->bean->$name))."$quote";
                            $query .= $updateValue;
                        } else {
                            //$query .= " ,$name=$quote".PearDatabase::quote(from_html($this->bean->$name))."$quote";
                            $query .= " ,".$updateValue;
                        }
                    }
                    $first = false;
                    $queryInsert .= " ,$name";
                    //$values .= " ,$quote". PearDatabase::quote(from_html($this->bean->$name )). "$quote";
                    $values .= $insertValue;
 
                } 
  

[mkenigson]

I've gone ahead and implemented this fix. I'd love to hear from the Sugar team if/when this fix goes into the code. I'd like to not have to worry about manually integrating these changes every time I do an upgrade.

Thanks,

Matt K.