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?

2 Answers 2


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

  • This appears to do exactly what I want, and only took about 15 minutes to set up. Awesome!
    – Omegacron
    Nov 20, 2014 at 15:43

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.