Fix Script Change Column Type

[Roger11]

I have a true/false column that needs to be a choice column with 3 options (auto/manual/unmanaged). How to do this and keep the existing column name via a fix script (as our change management process across multiple environments needs to be as automated as possible). 

I built the following script that takes care of the mapping - but it needs the new column to already exist and a lot of manual tidy up and then another script to migrate the data back when we recreate the old field with the same name but of type choice instead of true/false so that existing functionality isn't broken.

gs.log('Starting Mapping choice column');
var gr = new GlideRecord('u_table');
gr.addNotNullQuery('u_managed'); // existing true/false field
gr.query();
while (gr.next()) {
var managed = gr.getValue('u_managed');
if (managed == 1) {
gr.u_managed_choice = 3; // automated
} else {
gr.u_managed_choice = 1; // unmanaged
}
gr.update();
}
gs.log('Finished Mapping Choice column');

Ideally, I'd need to add logic that

1. Checks that the managed field is "true/false" (ensures script can't accidentally run twice and will fail if attempted).
2. Creates new u_managed_choice field with 3 choice options (1=unmanaged, 2=managed, 3=automated)
3. Delete the old u_managed field
4. Create new u_managed field of type choice just like 2 above.
5. Delete the temporary u_managed_choice field created in step 2

I've searched the community for code snippets to try build a solution, but there's not a complete solution out there that I've been able to find and I think it would be quite valuable to publish something here.

[Himanshu Dubey]

Hi 

Please refer link it might help you

https://community.servicenow.com/community?id=community_question&sys_id=58da70cbdb919f004fc2f4621f96...

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks

Himanshu Dubey

[Roger11]

Hi Himanshu, 

There's some good info in digging thru into that but on the main thread, Chuck only lists the same steps that I mention above, the moves needed to make to change an existing field type to something else programmatically. 

I've been trying to find the code snippets to help me do it. I've found a couple but they don't actually work (for me). If I can get something working fully, I'd like to document it here rather than linking all over the place as there are multiple steps involved in achieving this.

Thanks

Rog.

[Himanshu Dubey]

I think that is not possible by script as well because of system has restrictions on changing the behavior of data type 

[Roger11]

We're not changing the data type directly, I've done the steps manually, anything you can do by manual steps can be done by script. I just don't know enough at present how to script it.