SharePoint lookup to document library can't see all documents, or only sees documents of a certain file type (Word, Powerpoint, etc.)

This question from a client this week - why is it that when I lookup from a list to a document library, I only see Word documents even though there are Excel and PDF files in the document library too?

The client was using a multi-select lookup field, but the same thing happened with a single-select field. 

I recreated the scenario with a document library like this:

 

And in my case, the lookup returned only three choices:

 

It's not a content type or file type issue, it's simply that the Title field was not filled out for all the documents.  When the Title field is blank, the lookup acts as if those documents don't exist. 

I have often wondered why you're not allowed to look up to the Name field of a document library.  To my mind, you should be able to, since Name is the primary key field for the document library, just as Title is the primary key for other kinds of SharePoint lists.  But since it's not possible in this version, it's another example of why it's a best practice to fill out the Title field of documents, either on upload or after the fact.

Comments

[Roger Bannister]

It's a good practice to crate a simple WorkFlow to populate the Title field of the document with value in the name field (or combination of fields) on creation or edit. That way no intervention is required to enable these lookups.

[sadalit]

Roger - thanks very much for your comment - that's an excellent suggestion.
I believe this could be achieved with SharePoint Designer for a single library, but I'd recommend having a developer create the solution in Visual Studio so that it could be used over multiple libraries.

[[email protected]]

Or, we could just teach our users how to take advantage of the native property set of MS Office and other doc types so that their work product is more useful to everyone. Using SharePoint proves the value of meta data when it is present, and the frustration that ensues trying to find a document when it isn't present. It took one conversation with an IIS admin over a decade ago to teach me the value of meta data in web sites. I've been an evangelist since. Teaching my users why it's important is easy: Show them that they are applying meta data already, but in their folder structures, then show them the advantage of a search for docs with, then without meta data. That usually does the trick. I believe that this low tech approach is more effective in the long run that a developed solution. You know, teach the man to fish and he eats for a lifetime...