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

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

19 Responses to How to filter a lookup column in SharePoint?

  1. Alex Meyer says:

    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

  2. Anonymous says:

    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.

  3. Artem says:

    Another solution:
    http://www.sparqube.com/sharepoint-lookup-column/
    It is not free, but it is easy to use and no code required.

  4. Dam ii says:

    Lookup Plus for SharePoint 2013, It is more than Sharepoint Lookup. Cascaded Lookup, Filtered lookup, Cross-site Lookup /drop down/ and
    some controls are free. (“Create new item” link) Visit, http://www.azu.mn
    Or watch the channel http://www.youtube.com/watch?v=70-hXWY6ARY&list=PL-5v3e65O1cFMddgCpzIURE_UAsL-oDdW

  5. This one can build parent-child relationships between a set of columns so that the values in the child list will change automatically according to its parent selection. There is no limitation on the number of cascading levels.

    It also can select a pre-defined view or folder in the target list to filter items presented in the lookup field

    http://www.sharepointadd-ons.com/sharepoint-cascaded-site-lookup

  6. 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?

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

  8. Based on your error message: One of your control has runat attribute and the same should be set to server. Please check that

    • I was able to duplicate your solution perfectly. I think the problem comes in trying to use your code in another field not named Active Interest.

    • 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

      • Sorry, I was able to get this to work by typing the complete command vs cut and paste. But now, I get a ddlb with only the name of my field in the selection. The list box is not populated with the data. Any ideas what I may have done wrong?

  9. You mean to say if you give different column name you are getting the error?

  10. Yes. If I use a different column name then I have to modify this section of code:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: