Use of "inActive" to manage reference data

Question

Hello, 
 My app has a series of reference data stored in a separate table/RT. We've permitted the user to manage that data as the values (slowly) change over time. Part of that management is to disable selections for future use by making them inactive (isActive = False). 
 
 Dropdowns are used to access the reference data for creation and editing records. Only the active values are made available in the choiceValues/Labels to prevent use of outdated information.

The problem is in dealing with "historical" data. There are active records created the week previous that include a reference value that was made inactive=True this week. Now anytime these types of records get edited, the reference to the retired data causes an error because selected value is not on the current list of choiceValues. I could lift the isActive = true filter on edit vs. create new record, but some of the reference values are years old and not appropriate for selection vs a value that was retired just last week. 
 The question: What is the best practice for managing the availability of reference codes while enabling the editing of "historical" data? 
 Thank you for your consideration and thoughts, 
 Spencer Blanton

Mike Schmitt · Accepted Answer

Making a value NULL is not my preferred approach when it's dealing with important data that must be updated. 
 When the possibility exists of a deactivated item showing up in a dropdown (i.e. a former selection that was valid at the time, but is no longer valid), I use a local variable to detect whether the item's default value is outside the list of allowed values. Then I append that value to the valid values seen in the dropdown, but I append a "tag" to the Display Value of that item indicating that it's no longer available. If necessary, a validation message can be added to the dropdown that requires the user to select a current/valid value as well (though in some cases this might not be desired, as sometimes you only want to prevent new entries from using deprecated data, but legacy entries could keep it). This works pretty well over-all despite the extra bending-over-backwards needed to set it up. cc spencerb0001

Use of "inActive" to manage reference data

Top Replies