How do I filter a Document Library based on the value of a column in a related List

Question

I'm using SharePoint 2013 (Office 365).

I have created a List to store details of our Projects. The Project list has a column which stores the current status of the project. This is a drop-down field containing things like 'Potential', 'Current', 'Archived'.

I have also created a document library for 'Sales Documents'. I have added a metadata column which is a lookup based on the Project list. The idea being I can add a proposal document and link it to the relevant project.

What I want to do now is create a view on the document library which will only show documents that are related to, for example, projects whose status is set to 'Potential'.

Any help in achieving this would be appreciated!

Thanks.

Answer 1

When you create the lookup field you can select more fields from the related list. Select the fields you need so you can filter with their values in a view.

Because your field 'Current Status' is a choice field it will not be available for selection when creating the lookup field. As a workaround you can created a calculated column in the Projects list that would contain just the choice field in its formula and set it to return a single line of text. Now in the lookup field you can select the calculated column.

Create a new view for the document library and set up the filter with condition the calculated column (you'll see it named like lookupColumnName:calculatedColumnName) to be equal to Potential. This filter will show only the documents for the projects with status 'Potential'