Guest Author: Toni Frankola
SharePoint Use Cases

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 displayed.

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 a 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 the 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 the 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 the 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 the Projects list you created before. Click Next >.


8. From the fields list, select the following fields: Title, and Active. Select to sort by Title. Click Next >.


9. Click theNext button a few times to finalize this wizard. Once completed you will be taken back to the 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 the Filter Data… button


11. On the filter dialog, create a filter as shown on the picture below. This will force the 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, the 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 only works with Enterprise edition. In future blog posts I will try to create some alternatives for SharePoint foundation.

Guest Author: Toni Frankola
SharePoint Use Cases

Toni started his Web adventure in late 90’s and has been working with various web technologies ever since. These days his main focus is SharePoint technology. He is active in the SharePoint community via his SharePoint blog at http://www.sharepointusecases.com/ and Twitter http://twitter.com/tonifrankola, and also speaks about SharePoint at various SharePoint conferences. Toni runs his own company Acceleratio Ltd., that specializes in SharePoint consulting and developing software products, and leads the Croatian SharePoint User Group.