SharePoint Filtered Lookup Column in a List[Sharepoint过滤选项字段,无代码]

转载 2011年01月07日 19:25:00



Out of the box no way currently exists in SharePoint to filter a lookup column to show only a subset of entries based on certain criteria. I found this post which got me most of the way to a solution but thought I would add a few points for clarification with screenshots as below.

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. In my next post I will show you how to filter a lookup using javascript which keeps attachments intact and avoids the entire hotfix issue.

Here's how to create a filtered lookup column:

  1. To start with create two lists where one is the lookup list (MyLookupList) and one is the list that would use the lookup list as a lookup column (Math Classes Offered). The names of my lists are in parenthesis for clarification.


  2. DON'T SKIP THIS STEP - In the list ("Math Classes Offered") that will use the lookup list (MyLookupList) create a Single Line of Text column and NOT a Lookup column. If you use a Lookup column, you will get an error when trying to save new or updated entries.


  3. In SharePoint Designer go to the Data Source Library tab. Right click on the lookup list (MyLookupList) and select Copy and Modify…



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



  5. On the Data Source Library tab right click on your newly created FilteredLookup from step 3 above and select Save As… Navigate to a location on your local machine and save the file (FilteredLookup.xml).



  6. Open the local file saved in step 4 (FilteredLookup.xml) in a text editor.
    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>


  7. 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. Hide the existing List Form Web Part by changing the IsVisible property to false for <IsVisible>false</IsVisible> in code view.
    2. Place your cursor directly after the hidden List Form Web Part and add another List Form Web Part by clicking Insert/SharePoint Controls/ Custom List Form…



    3. Choose your list and form type. Click Ok.



  8. 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 6.
    2. Your code should look something like this:




  9. Setup your SharePoint 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.



  10. 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="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' )}"/>





This should be the same ff# as what was commented out for the text field


See the value of text= as was commented out for the text field


The name of your lookup list datasource which is what was entered as the id (step 6)


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)


11. Those are all the steps you need.


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



在SharePoint 2007和2010的版本中,look up类型的column有个特点,就是look up的值小于20个的时候,会展示一个下拉菜单,用户可以在下拉菜单中选择look up的值: ...
  • shrenk
  • shrenk
  • 2014年01月23日 15:04
  • 948

SharePoint2013 IT Professional - Site Column& List Column

大多数企业使用SharePoint,在List/Library下创建item/file的同时都会使用自定义的Column,也就是栏位,可以对item/file赋予一定的Column value用来管理...

SharePoint 2013 中使用客户化的 (Custom) Filter web part 过滤OOB list view

本文讲述如何在SharePoint 2013 中使用客户化的 (Custom) Filter web part 过滤OOB list view。...

SharePoint 2010 At Work--Taming the Elusive Calculated Column:Logic Functions


在 SharePoint 2013中使用 RelatedItems 列(Site column)

本文讲述 在 SharePoint 2013中使用 RelatedItems 列(Site column)

SharePoint JS Link 之(一) 改变column的颜色

在SharePoint 2013里,新加了一个功能叫JSLink,有了它,不用写任何后台代码,只需要写JS就能修改列表项的显示。本文介绍如何写JS Link。...
  • SPFarm
  • SPFarm
  • 2015年02月05日 20:20
  • 1272

SharePoint JS Link 之(二) 改变表单中column的颜色

按照前一篇(  SharePoint JS Link 之(一) 改变column的颜色 )修改之后,查看文档属性,发现Language列颜色并没有变: 要想让这个颜色变,需要...
  • SPFarm
  • SPFarm
  • 2015年02月06日 13:25
  • 1186


如果网站设置中没有导航操作 网站集功能中激活“SharePoint Server 发布基础架构” 网站功能中激活“SharePoint Server 发布” 这样就可以打开“导航”操作...
您举报文章:SharePoint Filtered Lookup Column in a List[Sharepoint过滤选项字段,无代码]