Change Field data type in MSCRM without dropping and recreating the field

All consultants like me be it in MSCRM or any other technology would surely agree with me – “Change is inevitable”. Every time I implement a project for a client, there is always a huge change in the requirements from the time when we started it and the time when we implemented it. And it has always been my aim to change to accept it gracefully (if not avoid it :)) and then incorporate it in the best possible way for that scenario (might not be the most optimal). Well let’s talk of one such change that I had to recently face in my project.

Before you continue further let me take my accountability off here. Well just joking but on a serious note, please read the lines in bold below. If you don not wish to continue further and if you are not confident of the changes suggested below it’s better you do not do it.

The procedures explained below are not recommended by Microsoft and working on the CRM organization database directly might make your CRM behave incorrectly. Please take a back-up of your database and customizations before you make changes as suggested below.

In my project, we initially created a field as a certain data-type and then all of a sudden we find that due to changing customer requirements, the data type of the field needs to be changed.

Let’s take a simple example below

The field “Test Field” is created as a whole number for the contact entity as show in the screenshot below

Now say all of a sudden, the field data type should be changed so that the user can enter decimal values. However this is not possible from CRM since you cannot change the data type after the field is created. You would need to drop the field and recreate it but in that case you would lose all the data that has been entered for the field.

Oh, thinking of making this change in all your PROD, DEV and QA environments using supported behavior and then planning for data migration for the existing records in production? Yeah, you are thinking in right direction. However if you think that this conventional way is not  fitting the time you have or you want to think otherwise, continue reading.

Lets see how we can tackle this the other way. Let us open the CRM Organization database and check for the field data type in ContactExtensionBase table (for CRM 2013 ContactBase table). When we open the contact extension base table we find that the datatype is integer.

Let us change the data type to decimal with the query below

alter table ContactExtensionBase

alter column new_TestField decimal(23,10)

When we check for the data type it is reflecting as decimal in the database

However when we open the same in CRM, the data type is still reflecting as Whole number.

This is because CRM picks up the data type from another table called AttributeType which has reference to the Attribute table. The code below would change the datatype to decimal in CRM

DECLARE @attributetypeid uniqueidentifier

DECLARE @attributeId uniqueidentifier

SET @attributetypeid = (Select at.AttributeTypeId from AttributeTypes at where at.Description = ‘decimal’)

SET @attributeId = (

SELECT ab.AttributeId FROM Attribute ab where ab.LogicalName = ‘new_TestField’  and ab.EntityId =

( Select top 1 EntityId from Entity where LogicalName = ‘contact’)

)

Update Attribute

SET AttributeTypeId = @attributetypeid where AttributeId = @attributeId

 

Now when we open the field in CRM, it is showing the data type as decimal. You may change the Maximum and the minimum value fields as you need and you are done.

 

Please note that any custom code referring to this field and typecasting it as integer would fail and you need to change the code. Also this would work only if the existing values for the field can be converted to the target data type. For example- for a text field with data which cannot be converted to decimal values, the above script would fail.

 

Another problem: You had been using local optionsets and suddenly you find that you should use global optionsets. But again thinking of the existing data in Production. Well follow this and use it if you like it.

https://debajitcrm.wordpress.com/2014/04/12/change-global-optionset-for-a-entity-optionset-in-microsoft-dynamics-crm-without-dropping-and-recreating-the-field/

 

Hope it helps and saves you some time !