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

Following are the steps to create filtered lookup column:

  • In order to start we will create two lists where one is our main lookup list (Category List) and other is the list that would use the lookup list as a lookup column (User Interest).
  • Add some data in the Category List as shown in image below:

fil1

  • Important Step - In the list (User Interest) create a Single Line of Text column (Active Interest) and Not a Lookup column. If you use a Lookup column, you will get an error when trying to save new or updated entries.
  • In SharePoint Designer go to the Data Source Library tab. Right click on the lookup list (Category Config) and select Copy and Modify

fil2

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

fil3

  • On the Source tab under Query click on the Filter button and enter your Filter Criteria

fil4

fil5

  • Click OK. You may also want to the Sort by clicking on the Sort button. Click OK
  • Once you have done with new list creation named FilteredLookup. XML file for the same is created which you find at the path All files / _catalogs / fpdatasources /

fil6

  • ( _ )FilteredLookup.xml [the _ (underscore) may or may not be there]. Choose “Edit file” option after selecting the xml file.
  • Use the SharePoint Designer 2010 to edit XML file(FilteredLookup.xml)
    • Add an id attribute right after the UseInternalName attribute and give it a value (id=”FilteredLookup”)
    • Save the file with your updates but keep the file open
    • 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" UseServerDataFormat="true" selectcommand="<View><Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq></Where></Query><ViewFields><FieldRef Name='ContentTypeId'/><FieldRef Name='Title'/><FieldRef Name='File_x0020_Type'/><FieldRef Name='Status'/><FieldRef Name='ID'/><FieldRef Name='Modified'/><FieldRef Name='Created'/><FieldRef Name='Author'/><FieldRef Name='Editor'/><FieldRef Name='_HasCopyDestinations'/><FieldRef Name='_CopySource'/><FieldRef Name='_UIVersion'/><FieldRef Name='_UIVersionString'/><FieldRef Name='Attachments'/><FieldRef Name='_ModerationStatus'/><FieldRef Name='FileRef'/><FieldRef Name='FileDirRef'/><FieldRef Name='Created_x0020_Date'/><FieldRef Name='FSObjType'/><FieldRef Name='FileLeafRef'/><FieldRef Name='ItemChildCount'/><FieldRef Name='FolderChildCount'/><FieldRef Name='PermMask'/></ViewFields></View>">
<SelectParameters>
<asp:Parameter Name="ListID" DefaultValue="{238AA30C-77ED-424A-8BC5-A11C3EDC77B7}" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="ListID" DefaultValue="{238AA30C-77ED-424A-8BC5-A11C3EDC77B7}" /></DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ListID" DefaultValue="{238AA30C-77ED-424A-8BC5-A11C3EDC77B7}" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ListID" DefaultValue="{238AA30C-77ED-424A-8BC5-A11C3EDC77B7}" />
</InsertParameters>
</SharePoint:SPDataSource>
  • 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.
  • Select “User Interest” list from List and Library under Site Objects tab on left. Click on List settings in the toolbar and select “List Form”
  • Give file name for new 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.

fil7

  • In the code view of your form search for the tag
    • After the entry for the existing SharePoint:SPDataSource … and before the closing DataSources tag, paste in the copied SPDataSource from FilteredLookup.xml
    • Your code should look something like this

fil91

  • 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.
  • Add the following code right after the commented out textbox and then modify the values in bold to match your particular list:
<SharePoint:DVDropDownList runat="server" id="ff2{$Pos}" DataSourceID="FilteredLookup" DataTextField="Title" DataValueField="Title" SelectedValue="{@Active_x0020_Interest}" __designer:bind="{ddwrt:DataBind('u',concat('ff2',$Pos),'SelectedValue','SelectedIndexChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Active_x0020_Interest')}"/>

fil10

  • Those are all the steps you need. You can see in the below screen the Active Interest shows only active categories

fil8