SharePoint 2013: How to find orphaned lookup values

Question

I've got a projects list in SharePoint 2013 that connects to two document libraries by way of a lookup column. Each document set in the libraries should ideally have its lookup column pointing to a valid project in the list. This system has been in use for a while now, however, and I'm finding that it needs to be cleaned up somewhat. Some libraries don't have a project assigned, while others are pointing to projects that have been deleted.

I attempted to turn on the cascade delete feature for the lookup column, but I'm getting an error that states one or more rows points to an invalid value. The specific error is below.

This lookup column cannot enforce a relationship behavior because it contains values that reference one ore more non-existent items in the target list.

This is apparently referring to the ones that point to a project no longer in the list. My list has about 5000 projects in it, though, with each project having two libraries. Is there an easy (or even semi-easy) way to identify these orphaned lookup connections, or do I have to go through all 10000+ document sets manually?

Answer 1

I would actually use Access to do this... there is a builtin way to quickly do what you want and it can handle large lists fairly easily.

Link the two tables into an Access database, then go to the 'Query Wizard' under create and then select 'Find Unmatched Query Wizard.'

Select your Documents in the first dialog box, and then your Projects in the second -- the next screen should automatically identify the relationship between the two -- then click finish and you should get a nice list of all the Documents that do not have related Projects

Answer 2

Logically this can be done via

1. Using a powershell script which matches the lookup column values against the target values
2. Export data into Excel. Once you have in Excel you can easily find un matching items.