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 list 设置中column没有排序选项

问题:用户在在设置list的时候,发现column ordering不見了,无法进行排序设置.   解决:该list可以在在Content Types中可以调整colum ordering...
  • pclzr
  • pclzr
  • 2011年10月26日 10:05
  • 1227

sharepoint 2016 学习系列篇(13)-自定义列表应用篇-(2)添加字段栏

前面创建完列表之后,我们需要在用户信息表中,添加一些我们需要用到的字段,当然还可以添加更多的字段,这里我们只是简单做一个示例,介绍如何在自定义列表(Custom List)中创建字段栏。我们先创建一个...
  • cxx2325938
  • cxx2325938
  • 2015年10月18日 21:38
  • 7024


一:读取值 using (SPSite site = new SPSite("http://localhost/")) { us...
  • imsasuke
  • imsasuke
  • 2013年10月16日 17:13
  • 1240

彻底搞懂SharePoint List Threshold(列表阈值)

  • shrenk
  • shrenk
  • 2014年09月13日 07:17
  • 2127

在SharePoint中使用关联列表(Related List)

使用关联列表(related list)
  • shrenk
  • shrenk
  • 2015年02月26日 01:15
  • 1070

如何“正确”为SharePoint字段命名 - Sharepoint internal name and display name

  • guojingDua
  • guojingDua
  • 2012年08月31日 13:49
  • 1126

SharePoint 读取选项字段所有Choise

对象模型SPFieldChoice SPSite site = SPContext.Current.Site; SPWeb web = site.OpenWeb(SubWebUrl); SPList ...
  • linyustar
  • linyustar
  • 2013年07月05日 14:36
  • 1229

sharepoint 查阅项 赋值

  • yl_99
  • yl_99
  • 2012年03月28日 16:49
  • 2535

SharePoint 2013 Designer系列之数据视图筛选

  • linyustar
  • linyustar
  • 2014年08月14日 13:51
  • 1520

sharepoint 2016 学习系列篇(12)-自定义列表应用篇-(1)创建一个自定义列表(Create a custom list)

sharepoint平台,有很多特别实在又好用常用的功能之一,自定义列表(Custom list),我们可以用它,来创建很多列表数据应用,来存储数据,无需开发,就能在线,增加,修改,删除,查询等操作数...
  • cxx2325938
  • cxx2325938
  • 2015年10月18日 21:37
  • 6150
您举报文章:SharePoint Filtered Lookup Column in a List[Sharepoint过滤选项字段,无代码]