One of the usual challenges with SharePoint 2007 was to build a lookup field between two lists but also apply a custom filter to the values being displyed.
Let’s consider a simple scenario: You have two lists on your site: Projects and Project Tasks. You want to link each task in Project Tasks list with a project in Projects list. This was simple even in v2007, you only had to create a lookup column and you were good to go.
But what if you wanted to link tasks with only active Projects (each project has Yes/No column indicating if it is active), that was a bit more challenging.
Let’s check how you can solve the problem with SharePoint 2010 (Enteprise edition only). Here is what you need to do:
1. On a SharePoint site create one custom list Projects with two columns: Title and Active (Yes/No column). You can add additional columns if you need them.
2. Use Tasks list list template and create a list Project Tasks. Add one additional Lookup column connected to Projects list (do not select “allow multiple values”)
SharePoint is going to create all the required forms for you. The default form for Project Tasks will look like this. The problem is, it shows inactive projects.
To change this, follow these steps:
1. Open your list. In the List Tasks > List Ribbon click Customize Form button. You must use IE to use the button. You will perform form customization with InfoPath 2010.
2. In the InfoPath form designer right click on Project field and click on Drop-Down list properties option.
3. On the newly opened dialog click Add button
4. You need to create new connection to receive data from Projects list. Select Create a new connection to: Receive Data. Click Next >.
5. Select SharePoint library or list as your source. Click Next >.
6. Type in the URL of your SharePoint site. Click Next >.
7. Select Projects list you create before. Click Next >.
8. From the fields list, select the following fields: Title, and Active. Select to sort by Title. Click Next >.
9. Click Next button a few times to finalize this wizard. Once completed you will be taken back to properties dialog.
Select ID field as value field. Click the button Next to Entries field, then Select a Field or Group dialog should appear.
10. On the Select a Field or Group dialog click on Filter Data… button
11. On the filter dialog, create a filter as shown on picture below. This will force drop down to load only active projects.
Close all dialogs and save the changed form. When you go back and try to create a new item in your list, projects dropdown will only show active projects.
Conclusion
This article describes how you can create a filtered lookup for SharePoint 2010. It is easy to use and no code is required to make it work. A pure end user solution. There are some limitations: it does not resolve the problem of updating old Tasks that have already been assigned to inactive projects. It also works with Enterprise edition. In future blog posts I will try to create some alternatives for SharePoint foundation.
