Fix SharePoint Target List Data Missing When Lookup Source is Deleted
Question details
Users need a way to stop lookup column values from clearing out in a target list when the corresponding source list item is deleted.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Managing connected SharePoint lists using lookup columns where source records are occasionally removed or archived.
- Observed behavior
- When a source item is deleted, the lookup value in the target list is automatically removed, even if list relationship settings restrict the deletion of the entire target item.
Ensure you have Site Owner or List Administrator permissions to modify column settings and create automated flows in Power Automate.
Use Power Automate to Copy Lookup Data to a Text Column
This is the most reliable workaround. By automatically copying the lookup value into an independent text column upon creation, your data is preserved even if the source item is deleted later.
SharePoint's native lookup logic inherently depends on the source item existing. If the source item goes away, the linked reference is broken and clears out.
Creating a duplicate text column bypasses this issue by statically saving the text string.
Navigate to your target SharePoint list. Click 'Add column' and select 'Single line of text'. Name it appropriately (e.g., 'Preserved Lookup Value').
Go to Power Automate, click 'Create', and choose 'Automated cloud flow'. Set the trigger to 'When an item is created or modified' and select your target SharePoint list.
Add a new step, search for 'Update item' (SharePoint), and select your site and list. Map the 'ID' field to the ID from the trigger.
In the 'Update item' action, find your newly created text column and populate it with the dynamic value from your lookup column. Save and test the flow.
Review and Enforce 'Restrict Delete' Settings
You can configure the lookup relationship to prevent users from deleting a source item if it is currently linked to a target item.
Looking for a Simpler Way to Manage Documents? Try WPS Office
While SharePoint handles complex list relationships, everyday document management and editing shouldn't be complicated. WPS Office provides a complete, lightweight, and highly compatible suite for creating, editing, and sharing documents without the steep learning curve.
- 1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
- 2. Open Your Documents: Launch WPS Office and directly open your existing .docx, .xlsx, or .pptx files without formatting loss.
- 3. Save and Share: Save your work locally or use WPS Cloud to securely share documents with your team instead of complex SharePoint list setups.
Frequently Asked Questions
Why does my lookup column go blank in SharePoint?
When a source item linked via a lookup column is deleted, SharePoint automatically clears the referenced data in the target list because the underlying source data it points to no longer exists.
Can 'Restrict Delete' stop lookup values from disappearing?
The 'Restrict Delete' setting prevents the deletion of the source item as long as it is referenced by a target item. However, if an administrator breaks the relationship or forces a deletion, the lookup column in the target list will still clear.
Is it possible to recover a deleted SharePoint lookup source item?
Yes, if a source item is deleted, it is usually sent to the SharePoint site's Recycle Bin. Restoring the item from the Recycle Bin within the retention period (typically 93 days) will generally restore the broken lookup relationship.
