Updating the data type of a meta field that was code based

Vote:
 

Hello,

I have a metafield that is type decimal.  I want to change it to double.  I tried to remove that field from code and then restart the app but I still see the meta field is still there and there's no option in commerce manager to unlink it or remove it.  The reason I want to do this is to update it's type.  But just updating the data type in code causes the site to crash when loading.

Any help would be appreciated.

Commerce version: 13.21 (build: 0)

#246465
Jan 06, 2021 19:01
Sanjay Kumar - Feb 03, 2021 19:37
Just observe, when I changed the data type from decimal to money and re-start the site then it's reflected into the database. e.g. decimal(18,0) to money
Vote:
 

Do you have any data that you want to migrate? If yes it will be very tricky. But if no you once you removed the property you should be able to unlink it from the metaclass and then delete it using Commerce Manager.

#246490
Jan 06, 2021 23:27
Vote:
 

There is existing data, but we can run a job to repopulate that data.  It's the product data, so would I have to delete the product data, restart the site without the property in the code, and then unlink and delete the property, then add the property back in with the new data type, restart the site again, and then repopulate the data?

#246491
Jan 07, 2021 1:08
Vote:
 

Hi Brian,

I think there is no option in Episerver commerce UI to update datatype directly but you can change the datatype with help of the database approach:

  • Open the database and find the tables in the database which you want to delete using the column name.

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyColumnName%'
ORDER BY    TableName
            ,ColumnName;

 

  • Drop the column name for tables that will populate from the above query.

ALTER TABLE [Table 1]
DROP COLUMN MyColumnName

ALTER TABLE [Table 2]
DROP COLUMN MyColumnName

 

  • Deleted MetaFileds and their Relations

DECLARE @MetaFieldId INT

SELECT @MetaFieldId=MetaFieldId  FROM MetaField WHERE Name='MyColumnName'
DELETE FROM MetaClassMetaFieldRelation WHERE MetaFieldId=@MetaFieldId

DELETE FROM MetaField WHERE Name='MyColumnName

 

  • Change the datatype from DataType = MetaDataType.Double, for the meta field and re-run the solution.

 

Note: FYI Episerver does not recommend the database approach.

#246726
Jan 13, 2021 5:38
Vote:
 

@Brian W (Whereoware) Yes, basically that.

  • Remove the property from code, build and start the site FIRST
  • Start and go to Commerce Manager and unlink the metafield from the MetaClass
  • Readd the property to code with different type, build and start the site
  • Repopulate the data
#246841
Jan 14, 2021 15:57
Vote:
 

I Support Quan's answer and in any CMS it is not a good idea to directly update the table with queries.

But sometimes you can not lose data. If you delete and re-create column then you will lose data.

If I remember correctly double is a float data type in SQL. So according to SQL, you can convert decimal to float using the following query. (Remember first you need to change type in code. It will crash the website. Then you need to change in DB and restart it)

ALTER TABLE TableName
ALTER COLUMN ColumnName float;

#247776
Jan 27, 2021 17:42
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.