How to Keep Historical SharePoint Lookup Values Unchanged
Question details
The user wants to prevent SharePoint lookup columns from automatically updating past records when source values are modified, or needs a way to view the original historical values.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Managing historical records and data integrity in SharePoint lists where lookup columns are utilized to reference other lists.
- Observed behavior
- Lookup column values automatically change to reflect updates made in the source list, inadvertently overwriting historical data on older list items.
Ensure you have the necessary permissions to edit list settings and view version history in your SharePoint site before attempting to modify column configurations.
Copy Lookup Values to a Static Text Column
Since SharePoint lookup columns dynamically link to source data by design, the best way to preserve a point-in-time value is to copy it to a static column.
SharePoint lookup columns are designed to always display the most current data from the source list. To maintain a historical record that does not change when the source is updated, you must capture the value as static text at the time the item is created.
Navigate to your SharePoint list, select 'Add column', and choose 'Text' or 'Number' to create a standard column for storing the historical data.
When creating or editing an item, look at the dynamically generated lookup value and manually type or paste that exact value into your newly created static column.
Use Microsoft Power Automate to create a workflow triggered by 'When an item is created'. Configure the flow to automatically copy the text from the lookup column and paste it into the static text column.
Review Previous Values Using Version History
If you only need to check what the lookup value was before it changed, you can use SharePoint's built-in Version History feature.
Manage Your Documents Effectively with WPS Office
While SharePoint handles complex online list lookups, managing your actual offline and cloud documents can be simplified. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, ensuring your files are always accessible and easy to edit.
- 1. Download the software: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install WPS Office: Run the setup file and follow the simple on-screen instructions to install the suite.
- 3. Open existing files: Launch WPS Office and seamlessly open your existing Microsoft Office documents without format loss.
Frequently Asked Questions
Can I stop a SharePoint lookup column from updating automatically?
No. By design, a lookup column dynamically pulls the current value from the source list. Whenever the source item is modified, the lookup value updates everywhere it is referenced. To keep a fixed value, you must copy it to a standard text column.
How do I turn on Version History in my SharePoint list?
Go to your List settings, click on 'Versioning settings', and check the option to 'Create a version each time you edit an item in this list'. You can also specify the maximum number of versions you want SharePoint to retain.
Does deleting the source item affect the lookup column?
Yes. Depending on your relationship behavior settings, deleting the source item will either be blocked if it's currently being referenced, or it will leave a blank value in your lookup column if the deletion cascades.
