Change Reference field to another table without loosing data

[Sam Ogden]

Hi All,

On our Incident table some time ago we created 5 new fields.  These were to hold details around which build number of our applications the incident was reported in, which build we introduced the issue in, the build we fixed the issue and 2 for if we back fixed the issue into older builds.  When these were setup we had incorrectly been storing the build numbers as separate CIs in the business services table.

We are trying to tidy up our CMDB and want to remove these inaccurate CIs.  We have instead made a lookup table to store the build numbers in.

The 5 fields on the incident table are all currently reference fields to cmdb_ci_service and now need these to be a reference to the lookup table which is now storing these values, but I don't want to loose any existing data.   I wanted to know the best way to achieve this?

My thought is to re-create the 5 fields as 5 new fields.  Then write a script to lookup the existing value in the old field to the same value in the new lookup table and add it to the new field.  Once all have been amended I can then delete the 5 old fields and then clear the incorrect CIs from the business service table.  The down side of this is anyone with reports on these 5 fields will need to amend these.

Any other suggestions on how to achieve this?  Is it possible to do this without having to create 5 new fields?

Thanks

Sam

[Dubz · Accepted solution]

Hi Sam,

You should be able to use a similar technique:

-add 5 string fields

-run a script to update those string fields with whatever value from the cmdb you can use to lookup the correct item from your new lookup table

-Redirect the reference field to the new lookup table

-run a script to lookup the correct item using the value in the string field

-delete the string field

Bit long winded but should allow you to avoid recreating reports etc

Cheers

Dave

[Sam Ogden]

Hi David,

Thanks for the above suggestion.  Very simple and should work a treat - I'll give it ago latter today.  Not sure why I was trying to over complicate the issue.

Thanks once again

Sam

[pmenon30580]

One more thing you can try is

1. Get an excel extract of the fields with the incident number.

2. Update the field reference tables

3. Import back the excel file.

@David Dubuis :Can you let me know what you think about this approach too.