Custom Fields wont save data

[bigugly]

We are running version4.5.1a in a windows MS-Sql enviroment and have developed a number of custom fields on the opportunity screens both Detail and edit when we enter information or select informtation it does not pass any of the infromation to the SQL server. The error that I see is below. Not sure what is causing this we made these changes on our test system and they worked fine using 4.5.1 and now in the live system using 4.5.1a it is not working.

[3872] FATAL SugarCRM - SQL Server error: Conversion failed when converting datetime from character string.

Help Please!!!!!!!!

[bvictoria06]

Were you able to fix this? I'm having the same problem.

[Johnmclucas]

Hi,

My Environment is using these components:

Sugar Version 4.5.1b build 1246
Sugar Edition OpenSource
Category Accounts
Operating System Windows 2003
PHP Version 5.1.2)
Database MS SQL 2005
Web Server IIS 6

I have been trying some customizations as well by adding 2 text fields and 2 date fields to the Account Object. I can get everything saved, but I can not retrieve it back out so this is the opposite problem, but I figured we might be able to help each other.

As a disclaimer, I don't really know what I am doing. I looked at a post on the Wiki under development http://www.sugarcrm.com/wiki/index.p...users_dropdown. I did have some success in a test environment adding a text field, but I just can not get my custom data back out of the db with this code.


Here is the code I have in place. It may help you with your problems.

I used Studio to create my custom fields. I also tried the "Repair Custom Fields" and Clear Cache. The "Repair Custom Fields" did do stuff with my date fields, so give that a try. The "Clear Cache" did not do any thing that I could tell.

The DetailView.html was changed via Studio. That is the only form I could get Studio to Change. I had to edit the rest of them by hand. I leave DetailView.html out because I did not have to do anything to it. Perhaps I should look at that for my own problem.


From the Account.php

 function fill_in_additional_detail_fields()
    {
        // Fill in the assigned_user_name
            $this->assigned_user_name = get_assigned_user_name($this->assigned_user_id);
 
               //custom fields begins
        $this->sold_c = get_assigned_user_name($this->sold_c);
        $this->sold_date_c = get_assigned_user_name($this->sold_date_c);
        $this->maintenancecontract_c = get_assigned_user_name($this->maintenancecontract_c);
        $this->numofinsureds_c = get_assigned_user_name($this->numofinsureds_c);
        //custom fields end
 
        $query = "SELECT a1.name from accounts a1, accounts a2 where a1.id = a2.parent_id and a2.id = '$this->id' and a1.deleted=0";
        $result = $this->db->query($query,true," Error filling in additional detail fields: ");
 
        // Get the id and the name.
        $row = $this->db->fetchByAssoc($result); 
  

from the EditView.php

 if (empty($focus->assigned_user_id) && empty($focus->id))  $focus->assigned_user_id = $current_user->id;
if (empty($focus->assigned_name) && empty($focus->id))  $focus->assigned_user_name = $current_user->user_name;
$xtpl->assign("ASSIGNED_USER_OPTIONS", get_select_options_with_id(get_user_array(TRUE, "Active", $focus->assigned_user_id), $focus->assigned_user_id));
$xtpl->assign("ASSIGNED_USER_NAME", $focus->assigned_user_name);
$xtpl->assign("ASSIGNED_USER_ID", $focus->assigned_user_id );
$xtpl->assign("ACCOUNT_TYPE_OPTIONS", get_select_options_with_id($app_list_strings['account_type_dom'], $focus->account_type));
$xtpl->assign("INDUSTRY_OPTIONS", get_select_options_with_id($app_list_strings['industry_dom'], $focus->industry));
//Add Custom Fields
$xtpl->assign("SOLD_C", $focus->sold_c);
$xtpl->assign("SOLD_DATE_C", $focus->sold_date_c);
$xtpl->assign("MAINTENANCECONTRACT_C", $focus->maintenancecontract_c);
$xtpl->assign("NUMOFINSUREDS_C", $focus->numofinsureds_c);
//Custom Code End 
  

From the Editview.html

<td valign="top" class="dataLabel"><span sugar='slot13'>{MOD.numofinsureds_c}</span sugar='slot'></td>
	<td  valign="top"  class="dataField"><span sugar='slot13b'><input name='numofinsureds_c' type="text" tabindex='2' size='15' maxlength='6' value="{NUMOFINSUREDS_C}"></span sugar='slot'></td>
	<td valign="top" class="dataLabel"><span sugar='slot14'> </span sugar='slot'></td>
	<td  valign="top"  class="dataField"><span sugar='slot14b'> </span sugar='slot'></td>
	</tr>
         <tr>
	<td valign="top" class="dataLabel"><span sugar='slot15'>{MOD.sold_c}</span sugar='slot'></td>
	<td  valign="top"  class="dataField"><span sugar='slot15b'><input name='sold_c' type="text" tabindex='2' size='35' maxlength='50' value="{SOLD_C}"></span sugar='slot'></td>
	<td valign="top" class="dataLabel"><span sugar='slot16'>{MOD.Sold_Date_c}</span sugar='slot'></td>
	<td  valign="top"  class="dataField"><span sugar='slot16b'>
		<table  cellpadding="0" cellspacing="0">
			<tr><td nowrap>
				<input name='sold_date_c' id='Sold_Date_c' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');" tabindex='1' size='11' maxlength='10' type="text" value="{SOLD_DATE_C}">
				<img src="themes/{THEME}/images/jscalendar.gif" alt="{CALENDAR_DATEFORMAT}"  id="Sold_Date_c_trigger" align="absmiddle">&nbsp;
			</td></tr>
			<tr><td nowrap>
				<span class="dateFormat">{USER_DATEFORMAT}</span>
			</td></tr>
		</table>
				</span sugar='slot'></td>
	</tr><tr>
		<td valign="top" class="dataLabel"><span sugar='slot17'>{MOD.maintenancecontract_c}</span sugar='slot'></td>
		<td  valign="top"  class="dataField"><span sugar='slot17b'>
		<table border="0" cellpadding="0" cellspacing="0">
			<tr><td nowrap>
				<input name='maintenancecontract_c' id='maintenancecontract_c' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');  " tabindex='1' size='11' maxlength='10' type="text" value="{MAINTENANCECONTRACT_C}">
				<img src="themes/{THEME}/images/jscalendar.gif" alt="{CALENDAR_DATEFORMAT}"  id="maintenancecontract_c_trigger" align="absmiddle">&nbsp;
			</td></tr>
			<tr><td nowrap>
				<span class="dateFormat">{USER_DATEFORMAT}</span>
			</td></tr>
		</table>
		</span sugar='slot'></td>
.... <!-- other fields and stuff -->
</form>

<!-- add the triggers-->

<script type="text/javascript">
Calendar.setup ({
	inputField : "Sold_Date_c", ifFormat : "{CALENDAR_DATEFORMAT}", showsTime : false, button : "Sold_Date_c_trigger", singleClick : true, step : 1
});
Calendar.setup ({
	inputField : "maintenancecontract_c", ifFormat : "{CALENDAR_DATEFORMAT}", showsTime : false, button : "maintenancecontract_c_trigger", singleClick : true, step : 1
});
</script>

[Johnmclucas]

Hi again,

I changed the Account.php and now all the fields work.

                //custom fields begins
        $this->SOLD_C = get_assigned_user_name($this->sold_c);
        $this->Sold_Date_c = get_assigned_user_name($this->sold_date_c);
        $this->MAINTENANCECONTRACT_C = get_assigned_user_name($this->maintenancecontract_c);
        $this->NUMOFINSUREDS_C = get_assigned_user_name($this->numofinsureds_c);
        //custom fields end 
  

Regards,

John

[Johnmclucas]

hmmm Now I am getting the same error. It is when a NULL value goes into the datetime field that I am having the problem. As long as there is a datetime I am ok.

Also I noticed that the SQL statements are all inclusive meaning that every field is being updated. Is there away to change that?

Thanks,

John

[Proware]

I am having the same problem with date type user fields - it does not like having 'NULL' as the value inserted.

It should have NULL without the quotes.

I will log this as a bug.

[ckristianto]

Hi guys,

I hope this thread can help you, http://www.sugarcrm.com/forums/showthread.php?t=20554. It solves my problem.

Regards