.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> SharePoint >> Post New Resource Bookmark and Share   

 Subscribe to Articles

SharePoint Filtered Lookup Column in a List - 2010

Posted By:Dhiraj Ranka       Posted Date: January 17, 2011    Points: 75    Category: SharePoint    URL: http://www.dotnetspark.com  

SharePoint Filtered Lookup Column in a List - 2010. 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 criteria.
 

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 criteria. 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. You can also achieve filter a lookup using javascript which keeps attachments intact and avoids the entire hotfix issue. Following are the steps to create filtered lookup column:
  1. In order to start we will create two lists where one is our main lookup list (TeacherList) and other is the list that would use the lookup list as a lookup column (Math Classes Offered). The names are mentioned in parenthesis for better clarification.
  2. Add some data in the TeacherList as shown in image below:
    Teacher List with data
  3. Make sure you DON'T SKIP THIS STEP - In the list ("Math Classes Offered") that will use the lookup list (TeacherList) create a Lookup column with "Teacher" as name and "TeacherName" as lookup column.
  4. In SharePoint Designer go to the Data Source. Right click on the lookup list (MyLookupList) and select Copy and Modify.
    Data Source - lookup list copy and modify
  5. In the Data Source Properties popup window, on the General tab enter the Name (FilteredLookup). Data Source - Providing name
  6. On the Source tab under Query click on the Filter button and enter your Filter Criteria.
    Data Source - properties

    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.
  7. Once you have done with new list creation named FilteredLookup in step 3. XML file for the same is created which you find at the path below.All files All files_catalogs_catalogsfpdatasourcesfpdatasources( _ )FilteredLookup.xml [the _(underscore) may or may not be there]. Choose "Edit file" option after selecting the xml file.Edit XML file
  8. Use the SharePoint Designer 2010 to edit XML file(FilteredLookup.xml) we have browsed in step 7
    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.
    5. Following screen shot will help explain things better.XML File changes
    Note: In SharePoint 2010 sub step 1 will be already in place with { } around all GUIDs used.
  9. 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. Select "Math Classes Offered" list from List and Library under Site Objects tab on left. Click on List settings in the toolbar and select "List Form"List form option
    2. Give file name for edit 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.New Edit list form
  10. In the code view of your form search for the tag
    1. After the entry for the existing tag, paste in the copied SPDataSource from step 8.
    2. Your code should look something like this:Data Source - code change
  11. 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.
  12. Add the following code right after the commented out textbox and then modify the values in bold to match your particular list:Changed code for eidt form
    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)
  13. Those are all the steps you need. Final resultTeacherList
  14. 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.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend