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

https://www.mriwm.com/Public/PamBlog/Lists/Posts/Post.aspx?ID=39

 

 

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

     

Change

Explanation

ff5

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

@MathTeachers

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

FilteredLookup

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

u

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.