Field values in historical records disappeared after editing that field's reference

[karolisjon]

Hello, we've just noticed an issue and I can't find an answer/solution. We had a requirement to edit an already existing reference field (Problem Manager) on the problem record. We changed the Reference table from sys_user to sys_user_grmember and added a few conditions. Everything was working as expected but then we noticed, that this action erased Problem Manager field's values in historical records. For now the update set was backed out and that returned the missing values. 

How can we change the reference table + add reference qual conditions without deleting historical data from the field? 

Thanks!

[Aman Kumar S]

Its never a good practice to change the reference of an existing field, since reference fields stores sys_ids which represents a record from certain table, those values are going to be of no use if you change the reference type mid-way.

Best way to handle this would be to create a new field, set the reference qual, create a fix script to map all the data from old field to new field so it populates corresponding data.

Hope you have not done this in PROD directly, if yes, raise a HI ticket to restore the old values and go for the solution proposed above.

Feel free to mark correct, If I answered your query.

Will be helpful for future visitors looking for similar questions 🙂

[karolisjon]

Thanks @Aman Kumar , I was not aware of that. Do you perhaps have an example of such fix script or at least any tips? Maybe there is a smart way of doing this, without assigning problem manager to the field manually in the script, as there are hundreds of such records. 

[Aman Kumar S]

Can you explain the use case, why would you want to change the sys_user reference to sys_user_grmember table?

[karolisjon]

The issue is no longer relevant, we solved the issue in the field dictionary by adding reference qual.