Tim Coalson is a Senior Consultant in the Transformation Practice at ThreeWill. Tim has been developing solutions on the SharePoint platform for over 15 years and has been a developer/consultant for over 30 years. Tim has been involved in migrating SharePoint on-premises farms to the Microsoft Cloud, Power Apps, and Power Automate (aka Flow) which are part of the Microsoft no code/low code solutions.
SharePoint Choice Options Issues
I was recently reminded of what I’ll call a “gotcha” that exists with SharePoint Choice columns. As I was working on a migration from SharePoint On-Premise to SharePoint Online (SPO), I noticed that some list items were not migrating properly. As I investigated the cause of the issue, I realized that over a period of time some of the valid choice options defined in the source SharePoint list had been changed. And they had been changed for good reason. However, the problem was that, when you change a choice option in a column definition, the items in the list with the “old choice option” value do not get changed automatically.
Introduction
Let me paint a typical scenario. Customer A creates a Project Status list in SharePoint. They want to track the Project status, so they create a new Choice column and define valid values of Not Started, In Progress, and Closed. The Choice columns is configured so that “fill-in options” values are not allowed, since these would not be valid statuses. At some point, a creative SharePoint User decides to change the Project Status column options definition from “Not Started” to “01-Not Started”, “In Progress” to “02-In Progress” and “Closed” to “03-Closed”. The primary reason for this change is to allow these projects to sort in the order of “Not Started”, “In Progress” and “Closed”. This is all well and good if the SharePoint user realizes that changing the option values in the column definition does NOT change the values that have already been stored in the list. Values already stored in the list must be updated to match the values in the column definition or potential problems will occur later….such as during migration time.
During migration, the list definition is copied from the source SharePoint instance to the target SharePoint instance. Next, the list items are copied. In the scenario described above, there are old/ values stored in the source list and these will not be copied to the target list. This is because they have a Project Status value that is no longer valid in the choice column definition.
How to Fix the Problem During a Migration
One option is to add all the Project Status values from the source list into the target Project Status column definition. This makes all the old Project Status values valid in the target list so they can be copied during migration. A second and possibly cleaner option, is to update the Project Status column definition in the target list to accept “fill-in” values. After all the old data is migrated from the source to the target list, this configuration can be updated to NOT allow “fill-in” values. However, at some point, the data in the list needs to be either updated manually or updated with a PowerShell script to match the valid project status values in the Project Status column definition.
Conclusion
Having consistent Project Status data will be important as you slice and dice your data using SharePoint views to provide some intelligence to the raw data. Hopefully this helps you if you encounter this problem. Migrations can be complicated, this is just one tip that I’ve discovered over time. Here are some other related articles that I’ve written:
- SharePoint Column with Multiple Values Using PowerShell and Set-PnPListItem
- No Code SharePoint Solutions
- Updating the Task Approval Form for the “Out of Box” SharePoint Approval Workflow
