I want to know if it is possible to restrict the items in a lookup column of a document library based on another, related field in a separate table.
We have a document library for each job site.
We have a 'Project' List and a separate 'Company' List. The Company name exists as a lookup column within the Project List. Each project is given a unique code.
In each Project Library, the Project Code is a column, established as a Content Type, so the Project Library template always contains this field (column) and is a mandatory metdata field for any dfiles added to a Project Library.
My problem is that we have hundreds of Projects codes now, as well as hundreds more quote codes (quotes and project codes are managed in the same list: the 'Project List'
Therefore when adding documents to a Project Library i ahve to scroll through hundreds of items to find the Code i want.
I would love to be able to limit the list of Project Codes available in the Project Code dropdown in each Library, by the associated Company. The Company Name exists in the Project List, but because that is a lookup column you can not add it as an associated column in the document library, so as to apply a view filter for example. Similarly you cant add a second Company Name column as a calculated column.
Surely this can be achieved? If it was in Access i would have implemented this years ago :)