You have a field that you wish was a different data type. For example, you have an option set that you wish was a lookup field. Maybe the values in this list change frequently, and you want to have someone who is not a System Administrator be able to add/modify values in the list.

But what about all of the existing records that have data in the option set? You don’t want to just delete old the field, as the existing values will be lost. And you can’t just change the data type of a field.

Using the following procedure, you can replace a field fairly quickly, and keep the data from that field.

1. Add your new field. In this example, add a custom entity for your lookup list, then add the lookup field to the desired entity.
2. Add the values to the lookup table and set security permissions so that users have view and append permissions to the entity so they can select values in the new lookup field.
3. Using advanced find, create a view that includes the old field and the new field side-by side. Export this view to Excel, being sure to check the “for re-import” checkbox.
4. Edit the spreadsheet, populating the new field with the appropriate values that correspond to the old field values.
5. Import the spreadsheet.

At this point, the existing records will contain data in the new field.

6. Update existing views, forms, and workflows to replace references to the old field with the new one. One way to find all dependencies is to select the field in customization, click more actions, and then “show dependencies.”.

Once you have replaced the field in all dependencies, you can delete the old field.