SharePoint 2007: Filtered Lookups

I've come across a requirement which is quite basic, creating a filter on a Lookup field in a List. Basically I have a list of Vacancies, and another list which looks up at the Vacancies list. I want the lookup field to only show those vacancies marked as Open.

SharePoint does not support this OOTB, however with a little playing around and using SharePoint Designer you can manage to get this to work.

• Create a new Custom Form
• Open SharePoint Designer, open the NewForm.aspx page, and create a new aspx page from the NewForm.aspx and save it as NewFormFiltered.aspx
• Delete the list Web part which exists and Click Insert > SharePoint Controls > Custom List Form, and choose based on a New Item (since you are editing the New Item form).

• Create a new DataSource to the List you need to filter upon
• In the custom page you are creating find the DataSource tag in the source code sharepoint:spdatasource and create another datasource similar to the one which exists, however with a new different datasource ID e.g. FilteredDS, and a different SelectCommand.
• The SelectCommand is used to create a query which filters your data e.g. if you filter on the Open Status it should contain something similar to the following:
  

• This filter can be written in the Tag Properties of the DataSource.


• Any parameters contained within the datasource and which contain List GUIDs based on the list you are editing should be edited to point to the GUID of the list you are filtering on.

• Add a SharePoint DropDown List
• Find the lookup field which you want to filter in the page, and comment out the SharePoint:FormField. Insert a SharePoint:DVDropDownList and customize the properties similar to the following:

Where ff9 should be the position in the FormField you have commented out, the datasourceid should be the id of the datasource you created, and the @Vacancy should be the name of your colum.

Save and test by clicking on New Item in the customised list, and in the address change the address of the page which comes up to the name of the page you have customised. If you’ve done everything correctly, you should have a drop down list bound to the filtered data source you created.

• Edit the List such that New button refers to the new page you have created
• Once you’ve ensured that the List is ok, you need to edit the List Properties such that your edited page is displayed when you press the New Item button.
• Go to the List Properties, Click on the Support Files tab and choose your page as the NewItemForm. Make sure the Content Item is set to Item or Task or your content Item not Folder. If it is set to Folder your changes are silently ignored, simply not saved.

The most toublesome parts are getting your data source(s) to filter correctly, and getting the DV drop down list to actually post the data to the list, but with a little playing around you should be able to do it.

Errors you may encounter: Strangely SharePoint sometimes changes the ID of the datasource to the name plus 0 e.g. FilteredDS0. Since your datasource is still bound to the original name, when you access the page you get an error: "An unexpected error has occurred".

Data Source Creations hints: to create your data source, go to the Data Source Library, click on Copy and Modify, create a new data source with the filter you require, same it as XML. Open the XML file and copy the SharePoint datasource from the xml file and paste it into your designer code. Strangely, the Guids in the data source from the XML file do not contain the curly brackets {}, and will result in the drop downs not getting populated until you surround each Guid with curly brackets.

Update:

BEWARE: Using this method you will be tying yourself down to a list Guid. Thus if you try to export and import onto a different server, you will get an error when you try to load the page since the list (Guid) will not be found. You would have to use designer (again) to update your Guids on the live server! Keep this in mind when using this hack.

Update 2:

This may solve the Guids problem: http://www.sharepoint-tips.com/2007/04/fixing-lookup-fields-in-list-definition.html

Update 3:

This definetely solves the problem: Solving the List Guid Export Problem

Update 4: Programmatic solution to filtered lookups - Cascading dropdowns

http://datacogs.com/datablogs/archive/2007/08/26/641.aspx