Move parent table fields to extended table

[kjmauriello]

I have taken over an instance where 100+ custom fields have been added to an OOB table.  I would prefer NOT maintaining all of that in the OOB table and want to extend that table and move those custom fields to only reside on the extended table.  How/Can this be accomplished with scripting.

This is how I see it, please let me know if there is a better method.

Option 1:

Execute a script that moves all u_..... fields from parent to child.  This seems to be the easiest since I can execute a Glide query and loop through the fields.  However, GlideDBUtil.promoteColumn promotes from child to parent and does not work on scoped tables (I attempted this sending the last parm as false).  GlideDBUtil.promoteColumn( "parent_table", "child_table", "column_to_move", false);

Option 2:

Create the field on the extended table (giving it a different name) and then run a script to copy the contents into the new field. To get back to the original field name you could then delete the parent table fields and repeat the process on the child table.  All choices, ACL's, ref qualifiers, etc need to be re-created.

If Option 2 is used it would be easier to execute a script to do the field creations on the extended table.  How can this be accomplished?  The table already exists so i would just need to script the add columns.

Any assistance with this would be greatly appreciated.

Kevin

[kklosterman]

For option 2 you can try inserting into the sys_dictionary table with table field named "name" (Table Name) being set to your child table.

I have not done this directly but you can take a look at BR on that table to make sure nothing else goes into creating a column on a table.

something like 

var gr = new GlideRecord('sys_dictionary');

gr.initialize();

gr.setValue('name', 'CHILDTABLENAME');

gr.setValue('internal_type', '??'); // Can copy the field type from parent

gr.setValue('column_label', '??'); // Can Copy the field label from parent

gr.setValue('element', 'Make Up New Name Here'); // Can Copy Field Name from Parent and make it slightly different

// Set Values for other fields to like Max Value / Read Only / Reference Field / Reference Qualifier / ect...

// If Type is String ... Copy Max Value from Parent....

// If Type is Reference ... Copy Reference Fields from Parent...

gr.insert();

[kjmauriello]

The sys_dictionary table only allows updates/insert/delete in Global scope.  I do not want to change the OOB definition of the sys_dictionary table to allow scoped apps to create/update or delete from that table.

I will see if I can make the changes in Global as you have suggested and set the fields application to the scoped app.

[Allen Andreas]

Perhaps I've misread this entire post, but I find it difficult to believe that none of these fields belong on the Parent table. That literally all of them could be moved to a child and there be no real impact...to process, data, etc.

Have you dug through everything and these are that bad that they must be moved?

Please mark reply as Helpful/Correct, if applicable. Thanks!

[kjmauriello]

Yes there are some that will remain in the parent table.  However, the custom fields are part of a custom questionnaire being implemented for Security Incident Response.  So I would say of the 109 custom fields 10 or so will remain and 5 or so will just be dropped all together.  Still a whole bunch of manual work.  I realize that all BR's, UI Policies, etc are going to need to be reviewed and modified.  In the near future there will be an implementation of SI OOB functionality, these additional fields will impact the OOB form/list, etc.  It will be easier to maintain the OOB and custom implementation if the unnecessary custom fields are moved to an extended table.

This was a very poor implementation effort originally and I am trying to clean it up to make it more manageable in the future.

I am trying to avoid manually creating the fields with a different name, scripting the values into those fields deleting the original fields in the parent and then re-typing them in again.  Anytime things are done manually mistakes are sure to be made.