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:

    Teacher List with data

    Teacher List with data

  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…

    Data Source - lookup list copy and modify

    Data Source - lookup list copy and modify

  5. In the Data Source Properties popup window, on the General tab enter the Name (FilteredLookup).

    Data Source - Providing name

    Data Source - Providing name

  6. On the Source tab under Query click on the Filter button and enter your Filter Criteria.
    Data Source - properties

    Data Source - properties

    Filter Criteria

    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
    All files

    All files

    _catalogs

    _catalogs

    _catalogs

    fpdatasources

    fpdatasources

    fpdatasources

    ( _ )FilteredLookup.xml [the _(underscore) may or may not be there]. Choose “Edit file” option after selecting the xml file.

    Edit XML file

    Edit 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…

      <SharePoint:SPDataSource runat=”server” DataSourceMode=”List” UseInternalName=”true” id=”FilteredLookup” selectcommand=”&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;SubjectCanTeach&quot;/&gt;&lt;Value Type=&quot;Text&quot;&gt;Math&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;ViewFields&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;SubjectCanTeach&quot;/&gt;&lt;FieldRef Name=&quot;ID&quot;/&gt;&lt;FieldRef Name=&quot;PermMask&quot;/&gt;&lt;/ViewFields&gt;&lt;/View&gt;”><SelectParameters><asp:Parameter Name=”ListID” DefaultValue=”{17CFFB4F-665F-4F7F-9914-7DCE51312962}” /></SelectParameters><DeleteParameters><asp:Parameter Name=”ListID” DefaultValue=”{17CFFB4F-665F-4F7F-9914-7DCE51312962}” /></DeleteParameters><UpdateParameters><asp:Parameter Name=”ListID” DefaultValue=”{17CFFB4F-665F-4F7F-9914-7DCE51312962}” /></UpdateParameters><InsertParameters><asp:Parameter Name=”ListID” DefaultValue=”{17CFFB4F-665F-4F7F-9914-7DCE51312962}” /></InsertParameters></SharePoint:SPDataSource>

    5. Following screen shot will help explain things better.

      XML File changes

      XML File changes

    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”

      List form option

      List form option

    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.

      New Edit list form

      New Edit list form

  10. In the code view of your form search for the tag <DataSources>
    1. After the entry for the existing <SharePoint:SPDataSource … and before the closing </DataSources> tag, paste in the copied SPDataSource from step 8.
    2. Your code should look something like this:

      Data Source - code change

      Data Source - code change

  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:

    Changed code for eidt form

    Changed code for eidt form

    <SharePoint:DVDropDownList runat=”server” id=”ff5{$Pos}” DataSourceID=”FilteredLookup” DataTextField=”Title” DataValueField=”Title” SelectedValue=”{@MathTeachers}” __designer:bind=”{ddwrt:DataBind(‘u’,concat(‘ff5’,$Pos),’SelectedValue’,’SelectedIndexChanged’,’ID’,ddwrt:EscapeDelims(string(@ID)),’@MathTeachers’)}”/>

    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.
    Final result

    Final result

    TeacherList

    Teacher List

  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.
Posted in Sharepoint, XML Tagged with: , ,
  • Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

  • Dhiraj
    Great work.
    I want to ask you, i want to link 2 documents say incoming letter with outgoing letter.
    Both are stored in same doc library. In coming letter will have a lookup field for the outgoing letters of the same library which are not answered. Can this be achieved with the steps shown above?

    Thanks
    Riyaz

  • Ritz

    Dhiraj,

    This is great, however I can’t get the data to save the into the list. Could you clarify the following changes I need to make in the code:
    1. @MathTeacher
    2. DataTextField=”Title”
    3. DataValueField=”Title”

    Thanks

  • prasvi

    hi Dhiraj

    Great Post.

    I am able to get the filtered lookup using your method. However in the Edit form the SelectedValue of the dropdown is not getting set to the existing value..

    Any Suggestions. Please help

  • prasvi

    And I can get it to save back only if the DataValueField is set to “ID”

  • Danny

    Hi folks,

    This works for me. But is that possible to make the dropdown list to allow multiple values?

  • hey Dhiraj i really appreciate your work..
    but i want to ask about when you edit current inserted record how to set lookup field,Because i set these fields but getting hyperlink on it… if you have any solution about that problem post the comment…

  • Jhonny Marcelo

    Hi Dhiraj,

    First of all for your smart solution, It helps me alot. Second, I would like to add that filter in a form where user needs to select one or more items from that combo box. Please tell me how to do it.

    Thanks.

  • End

    good solution. thanks a lot…

  • Pooja

    Dhiraj,
    One follow-up question for you. Can you clarify what the “Copy and Modify” step is doing to the list data source? Is it creating a copy of the source data or just the list? In that case, is it actually reading off of the same data as the original list? So, if someone was to update the original list, will this copy also be updated automatically each time?

    Thank you

  • Kashif

    Nice but how can i make it work if inline editing is enabled? thanks,

  • Uday Kiran B

    while opening the edit form, the lookup selected value was coming with value anchor tag, i didn’t understand whats wrong here… ?

  • Thanks, very useful!

  • batz09
  • Dennis

    What if the value which im going to compare is from the other list?
    like i will first compare the date in list1 and list2, if the same ill get the value of certain field in list1 which will be saved in list2?

  • Tica

    Hi, is it possible to makes it run for a lookup which allows multiple value? I searched all ofter the internet but i found nothing. Do you have any advice? Thanks in advance