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.

[Roger11 · Accepted solution]

OK. Here's how I solved my problem using 4 Update Sets (and 2 fix scripts).

Update Set 1.

• create new field with choices.
• create fix script to map the old true/false field into the choices field.
• run script/check results are what I expect.

Update Set 2.

• delete the old true/false field

Update Set 3.

• create new choice field with the same name as the old true/false field
• create fix script to simply copy across the temporary choice field data.
• run script/check results.

Update Set 4.

• Delete temporary choice field

This works nicely for development of the update sets and scripts in the development environment and then subsequent deployment through our TEST, UAT and PROD environments and supports segregation of duties.