SharePoint Lookup field allows you to render items from another list and displays them in a drop-down choice format. When working with a larger dataset in the source list, you may want to apply a filter to the Lookup field so that it displays only relevant items.
With SharePoint Server 2010 Enterprise, you can do so using InfoPath.
As an example, I have a “Source List” with an “Active” column. With this post, I will demonstrate how to exclude the last 2 list items below that are marked as inactive.
In another list called “My Demo List”, I have a Lookup field referring to the “Source List” item. By the end of the post, “My Demo List” would have excluded the inactive Source List item like displayed below:
So, here’s how I filtered the Lookup Field:
- Go to the corresponding list with the Lookup Field. Then click “Customize Form” on the ribbon
- Once you have the form opened in InfoPath, click Data Connections (located within Data tab)
- Disable automatic retrieval for the data connection of your source list:
- Find the source list of your Lookup field values. Then select “Modify”
- Deselect “Automatically retrieve data when form is opened”
- Click “Finish”
- Add another data connection to your source list
- Click “Add”
- Select “Create a new connection” > “Receive Data”. Then click “Next”.
- Select “SharePoint library or list”. Then click “Next”
- Enter the URL of site where the Source list is located in. Then click “Next”
- Select your Source List. Then click “Next”.
- Select 2 of the following fields:
- The field that you want to display in the drop-down. In this example, I want to display “Title”.
- The filter that you want to apply. In this example, I want to filter against the “Active” field.
- Click “Next” twice
- Before clicking “Finish”, do the following:
- Rename your data connection for future references
- Be sure that this field “Automatically retrieve data…” is selected
- Then click “Finish”
- Close the Data Connections window
- Click “Add”
- Change Data source of Lookup field and apply filter
- Right click on Lookup Field. Then select “Dropdown List Box Properties”
- In “Data source” field, select your new data connection
- Click the icon next to Entries
- Select “Filter Data”
- Add your filter criteria here. Click “Add”. Then select your filter criteria.
- Click Ok until the Drop-Down List Box Properties is closed.
- You’re almost done at this point. You can click Preview to see how you did.
- If all is kosher, click “Quick Publish” to save your changes.