Changing the data type of a meta field in SiteFinity


We have a site finity site which was nocked together by a designer who has since left- unfortunatly one of the meta field we had on the news articles “ViewCount” was set to ShortText instead of Integer, meaning we couldn’t do anything “numbery” with it– like sort it correctly (being text it sorted like 999- 998- 90- 899- 898- 80 for example!), which was kind of crippling the “Most Popular” box on the homepage.

I read up that this is a simple matter of updating the entry for this meta field in the web config- easy enough (in the web.config under telerik > cmsEngine > metaFields);

<!--<add key="News.ViewCount" valueType="ShortText" visible="True" searchable="True" sortable="True" defaultValue=""/>-->
<add key="News.ViewCount" valueType="Integer" visible="True" searchable="True" sortable="True" defaultValue="0"/>

But unfortunly, this only applies to any data inserted after that change has taken place, and i had 6000 or so records already in the database as text. Having dug around in the database I found the solution- meta data seems to be held in a table called sf_GCMetaData so having gradually built up a query which isolated my ViewCount fields i set about writing an update statement;

UPDATE
                sf_GCMetaData
SET
                ValueType = 3,
                IntegerValue = CAST(ShortText AS Integer)
WHERE
                (Application = '/News') AND (KeyValue = 'ViewCount') AND (ValueType = 0)
GO
 
UPDATE
                sf_GCMetaData
SET
                ShortText = null
WHERE
                (Application = '/News') AND (KeyValue = 'ViewCount') AND (ValueType = 3)

A few notes- ValueType 0 is Shorttext, 3 is Integer- i figured this out by looking at other known meta fields. So i move the data out of Shorttext and put it into IntegerValue field (using a cast to actually convert the data) then in a subsequent update i wipe out the shorttext data, incase this some how messes it up!

So conversion complete and nothing broke– try this at your own risk though and make sure you backup your databases first!!

  1. No comments yet.
(will not be published)