How to filter a lookup column in SharePoint?

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:

  • 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
  • In the Data Source Properties popup window, on the General tab enter the Name (FilteredLookup).

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

  • 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 /

  • ( _ )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=”&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;Status&quot;/&gt;&lt;Value Type=&quot;Text&quot;&gt;Active&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;ViewFields&gt;&lt;FieldRef Name=&quot;ContentTypeId&quot;/&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;File_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;Status&quot;/&gt;&lt;FieldRef Name=&quot;ID&quot;/&gt;&lt;FieldRef Name=&quot;Modified&quot;/&gt;&lt;FieldRef Name=&quot;Created&quot;/&gt;&lt;FieldRef Name=&quot;Author&quot;/&gt;&lt;FieldRef Name=&quot;Editor&quot;/&gt;&lt;FieldRef Name=&quot;_HasCopyDestinations&quot;/&gt;&lt;FieldRef Name=&quot;_CopySource&quot;/&gt;&lt;FieldRef Name=&quot;_UIVersion&quot;/&gt;&lt;FieldRef Name=&quot;_UIVersionString&quot;/&gt;&lt;FieldRef Name=&quot;Attachments&quot;/&gt;&lt;FieldRef Name=&quot;_ModerationStatus&quot;/&gt;&lt;FieldRef Name=&quot;FileRef&quot;/&gt;&lt;FieldRef Name=&quot;FileDirRef&quot;/&gt;&lt;FieldRef Name=&quot;Created_x0020_Date&quot;/&gt;&lt;FieldRef Name=&quot;FSObjType&quot;/&gt;&lt;FieldRef Name=&quot;FileLeafRef&quot;/&gt;&lt;FieldRef Name=&quot;ItemChildCount&quot;/&gt;&lt;FieldRef Name=&quot;FolderChildCount&quot;/&gt;&lt;FieldRef Name=&quot;PermMask&quot;/&gt;&lt;/ViewFields&gt;&lt;/View&gt;”><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.

  • In the code view of your form search for the tag <DataSources>
    • 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

  • 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’)}”/>

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

19 thoughts on “How to filter a lookup column in SharePoint?

  1. Quick question. Which fields need edited in that last part of copy and pasted code. There are no bold values so I don’t know what to replace for my specific situation

    Like

  2. I would recommend that you include the software packages you need for this capability near the top or as part of the intro. My IT guys disappointed me when they said it would take weeks to get SharePoint Designer.

    Like

  3. This is ALMOST exactly what I need. I got it working in pretty short order using the technique above for a Drop Down List. But what I need is a Multiple Selection Lookup, and I can’t figure out the Multiple Selection equivalent of DVDropDownList. Is there one? If not, is there any solution?

    Like

  4. Ran into an error in 2013 when trying your solution. Any thoughts?

    Web Part Error: The Runat attribute must have the value Server. Correlation ID: 07c8f89c-46fe-3057-9b17-c970fef75b6f

    Like

    • I’m using SP2013 and am receiving the same error. I had to change the ” to ‘ and the next error I received is below. As separate observation, I noticed that the SP DataView Controls are not enabled – which I’m thinking might really be the problem.

      Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

      Correlation ID:93d8259d-e084-2000-15b3-4945e0c83332

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.