How to change the type of a field in Sitecore Commerce 8.2.1

I was in a pickle recently when I created a new field in Sitecore Commerce 8.2.1, but accidentally set the wrong type (string instead of bool). I thought getting rid of the field, and starting from scratch with the right type would do the trick. But I was wrong.

Re-creating the issue

  1. Create a property in Commerce Server Schema Manager of type string.
  2. Assign the property to a product definition.
  3. Delete the property.
  4. Create a new property with smart name but type bool.
  5. Assign the property to the product again.
  6. Try updating the property value in Commerce Server.

When you try to update the property value in Commerce Server, it will fail with a cryptic error!

The reason

To find the root cause, I took a peek at the Catalog table in the database- and sure enough, the property column was still of type string and wasn't updated to bit even though I deleted the string property and created a new one with type bool.

The fix

You need to either delete the column and recreate it in the database to fix it after the fact. Alternatively, you could delete the column before recreating the property in Commerce Server Schema Manager.