Is it possible to change the Data Type of a field (e.g. from integer to shorttext)??

[sknopp]

I want to change the data type of a field from integer to shorttext (so that instead of value 1234 the value 1234a is possible). As far as I can see this can't be done by "Overrides for fields", since the dropdown-menu for data-type is disabled. Are there any possibilities besides creating a new field?

Thanks!!

[MichaelChatel]

Hi Stephan,

No, there is no way to do this, besides creating a new Integrity field of the datatype you desire, and possibly copying the old field values, to the new field.

The reason for that being, that this is really changing the database field types, on the underlying database (ex.  SQL Server, Oracle, etc).  Each database and field datatype would have certain caveats or limitations, of what data they could support.

If you are interested in such an enhancement for Integrity, you can ask Support to create a case for you, and ask for your organization to be attached to enhancement RFC # 786584, "Some of the data type of existing fields should be changeable".   I don't anticipate this particular enhancement coming to Integrity anytime soon, or possibly, ever, but getting attached to the RFC allows us to track customer interest for such enhancements.

Have a great day,

Mike

[sknopp]

Ok, good to know. Thank you!

[smccoy]

We encounter this problem quite frequently as well. Our approach is the following:

1) Rename the curent field to some descriptive, but useless, name. We use zz_fieldname so they sort at the bottom.

2) Recreate the field as the new type

3) Update the visible fields, associated rules, and presentation templates in the appropriate item-types to use the new field.

If there is no concern about the legacy data, we test that and push it to production with the Migration Wizard.

4) If the old data needs to be preserved, we create a manually scheduled trigger and script that will copy the data from the zz_field to the new field and perform any needed data-type conversions.

If anyone has found a cleaner, better, or more efficient method, please share because we'd like a better approach for managing this.

-Sean

[JensN.]

Hi Sean,

what about the history of the replaced field? When you replace an old field with a new one, and even when you copy the values from the old to the new field, there are two problems:

1. the history of the new field doesnt match the time, when the original values were entered.
2. if you remove the field from the item, the history of the old fields isn't available (or visible) for standard users anymore. It is, of course, still available in the database, but to look at these data isn't that easy for a standard user.

For this reason we always leave old and replaced fields in the item and shifting them into a part of the presentation template which is only visible for admins. Therefore the history of these old fields is still available for the user, but as a drawback it is one more field which has an (not so big, but existent) influence of performance, triggers and so on.

cheers, Jens

[smccoy]

Hi Jens,

To date, historical information has not ever been a consideration. When the need to change a data-type for a field has surfaced, it has been early in the roll-out process when there isn't much history. And in all cases that we've encountered so far, the historical data was not of any consequence. If we would ever encounter a situation where the history would be significant, the logic for that processing would be included in the post-migration processing script.

In addition, to date the new data-type has always been more capable; for example changing from an integer to a short-text, or a short-text to a long-text. If we were to encounter a "downgrade of a field", we would handle the sizing and truncation logic in the post-migration script.

Kind regards,

-Sean

[KaelLizak]

Hello Stephan,

Did any of the information provided help you resolve your issue?

• If not, could you let us know why not, so we can try to help you more specifically?
• If so, could you mark the relevant post as Correct Answer to let everyone know you found a resolution, and what got you there?  In addition to letting us know that this issue has been resolved, if someone else runs into this issue (which seems likely), it will also give them direction on how to resolve it.

Thanks,

Kael

[sknopp]

Yes thanks a lot, that was very helpful!!