SharePoint Filtered Lookup Column in a List - 2010

As of now there is no way in Out of Box which will support filtering a lookup column to show only limited set of entries based on certain criteria. One thing to note is that if you modify the New and Edit forms, you will subsequently break attachments. There is a hotfix to address the attachment issue but it requires deployment to the server and adds work to your modifications. You can also achieve filter a lookup using javascript which keeps attachments intact and avoids the entire hotfix issue. Following are the steps to create filtered lookup column:

1. In order to start we will create two lists where one is our main lookup list (TeacherList) and other is the list that would use the lookup list as a lookup column (Math Classes Offered). The names are mentioned in parenthesis for better clarification.
2. Add some data in the TeacherList as shown in image below:
   
3. Make sure you DON'T SKIP THIS STEP - In the list ("Math Classes Offered") that will use the lookup list (TeacherList) create a Lookup column with "Teacher" as name and "TeacherName" as lookup column.
4. In SharePoint Designer go to the Data Source. Right click on the lookup list (MyLookupList) and select Copy and Modify.
   
5. In the Data Source Properties popup window, on the General tab enter the Name (FilteredLookup).
6. On the Source tab under Query click on the Filter button and enter your Filter Criteria.
   
   
   Click OK. You may also want to set the Fields by clicking on the Fields button (for best performance, reduce the fields to only those needed) and the Sort by clicking on the Sort button. Click OK.
7. Once you have done with new list creation named FilteredLookup in step 3. XML file for the same is created which you find at the path below.All files _catalogsfpdatasources( _ )FilteredLookup.xml [the _(underscore) may or may not be there]. Choose "Edit file" option after selecting the xml file.
8. Use the SharePoint Designer 2010 to edit XML file(FilteredLookup.xml) we have browsed in step 7
   1. Add { } around each of the 4 guids ( ="{17CFFB4F-665F-4F7F-9914-7DCE51312962}" )
   2. Add an id attribute right after the UseInternalName attribute and give it a value (id="FilteredLookup")
   3. Save the file with your updates but keep the file open
   4. Copy the text only from the starting to ending SharePoint:SPDataSource tags. Mine looks like below but yours will be different based on your list guid, the fields, sort, and filter values.
   5. Following screen shot will help explain things better.
   Note: In SharePoint 2010 sub step 1 will be already in place with { } around all GUIDs used.
9. Before starting this step always back up any files you will be editing. In SharePoint Designer, open the file you will be editing such as EditForm.aspx or NewForm.aspx file. This step will allow you to get to the individual list columns to edit them.
   1. Select "Math Classes Offered" list from List and Library under Site Objects tab on left. Click on List settings in the toolbar and select "List Form"
   2. Give file name for edit form, choose the type of form you want to create, if you want your new form to be default then choose "Set as default form for the selected type", also in case you want to add the same in the list item menu and ribbon then check "Create link in List Item Menu and Ribbon" and provide the link name. Click Ok.
10. In the code view of your form search for the tag
    1. After the entry for the existing tag, paste in the copied SPDataSource from step 8.
    2. Your code should look something like this:
11. Setup your SharePoint Designer environment to be in Split mode. In Design View select the column that will be used for the lookup field. Right now it will show as a textbox. In Code View the associated code for the textbox will be highlighted. Comment out the textbox.
12. Add the following code right after the commented out textbox and then modify the values in bold to match your particular list:

    Change	Explanation
    ff5	This should be the same ff# as what was commented out for the text field
    @MathTeachers	See the value of text= as was commented out for the text field
    FilteredLookup	The name of your lookup list datasource which is what was entered as the id (step 6)
    u	u or I where u=edit mode and i= new mode
    DataTextField="Title"	Change Title as necessary to match the field from the Lookup List (MyFilteredLookup)
    DataValueField="Title"	Change Title as necessary to match the value from the Lookup List (MyFilteredLookup)

13. Those are all the steps you need.
14. Any time you need to update your filter (for example sorting the items or changing criteria), you can always go back into the properties of your copied datasource, make your change, save locally, copy out the select command and paste into your new datasource on the page.