Creating filtered lookup fields in SharePoint edit forms

Yes - it is possible - even without coding! Using SharePoint designer you can create custom looukup fields where you set your own filter on which elements from the source list which you want to display. What we will do is to insert our own drop down list instead of the standard lookup field. This drop down will fetch its values from a new data connection.

Below is the steps to follow.
  1. Define your list with all the needed fields and lookups
  2. Create a new custom form(s) - you will need a new New and Edit form. For details see this blog entry.
  3. Delete the existing List Form web part
  4. Add a Custom List Form to your page
  5. Create a new Data Source to use to get and filter your lookup data
    • Open the Data Source Library
    • IF your lookup list is NOT in the same site as your main list do the following
      • Select Connect to another library (at the bottom of the Data Source Library window)
      • Click Add
      • Browse to the web site which contains the list with your lookup values
      • Add a name to your source
      • Click OK
    • Find your list either in Current Site or the site added above.
    • Right click on the list that will be your lookup source and select Copy and Modify
    • A Data Source Properties dialog will be display
    • Select the Fields you need
    • Select the Filter you want to apply
    • Select Sort to set the correct sorting order
    • Click OK
  6. Give your new datasource a name; FilteredDS (or something else :-))
  7. Delete the SharePoint:FormField which controls the existing lookup
  8. Insert a SharePoint Drop Down List Control
  9. Set the parameters of the drop down list
    • DataTextField="MyTitleColumnName"
    • DataValueField="ID"
    • SelectedValue="{@MyListLookupParameter}
    • Id="THEID" - the same as the ID of the FormField just deleted
    • DataSourceID="FilteredDS"
    • __designer:bind="{ddwrt:DataBind('i',concat('THEID',$Pos),'SelectedValue','SelectedIndexChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@MyListLookupParameter')}"
  10. The last step is to set your new input form as default form to use when elements in your list is either edited or created. Right click on the list (in the Folder List) you are customizing and select propertes.
  11. Save, test and debug!

It is important to get all the steps right, but step 10 takes care of posting the data back to the list.

Thanx to Dattard Zebug - which was the primary source on solving this challenge.

Issues and error sources:

  • Suddenly the data source is not recognized any longer and you get a server error. SharePoint designer had added a '0' after the ID of my datasource name. Why? Ask Microsoft.

To create a cross-site lookup field do the following

  • Create the list that you want to be the source of your lookup field - must be deone at the site collection root.
  • Create a new Site Column (at the site collection root as well ) which you define as a lookup field and the liste created above as your source
  • Use the new site column field in your new list - anywhere in the site collection

A lot of the limitations with the lookup field should hereby been solved and addressed - at least for the time being :-), Unfortunately no C# code was needed :-)


IT-Dev Sharepoint Components said...

Hi, if you need reliable filtered lookup functionality you should try IT-Dev View Filtered Lookup for WSSv3 and MOSS. It is based on built in Lookup Fieldtype and extends it with cross-site and filter functionality. It can be included in a list schema in a feature, and runs in minimal trust policy!

Anonymous said...

This would be really usefull, but can you please describe a little more in detail how you configure the drop down box, and what type of drop down box you mean !?


Christopher Ramsdal
Bergen - Norway

Anonymous said...

I am very new at sharepoint and I am really breaking my head on some of these steps! Is it possible to go into a bit more detail on where I need to go, and what I need to do in order to complete a step?

For example:

I can't find the SelectedValue property, and the "__designer:bind" doesn't tell me anything :-(

I also do not understand how this allows me to filter that data based on the selection of another lookup (dropdown) menu

Help is very appreciated!


Paul Keith said...

I'm not able to get this to work. My DVDropdown is saying "Databound", yet the drop-down box is empty when the page renders. What am I missing?

I think my problem is with the dvdropdown parameters. Can anyone that's made this work explain this in a little more detail. Some of the placeholders (like @MyListLookupParameter) are a little unclear to me.

Anonymous said...

SelectedValue Property is available in SharePoint asp.net DropDownList Control. How do we set this.

Blanca said...

How can I allow multiple values to the filtered lookup field?


Blanca said...

How can I allow multiple values to the filtered lookup field?


David said...

I have been able to customize the NewForm page with a dropdown list that is bound to a filtered data source. No errors are produced and I am able to create new items in the list and save them without issue. However, I am having trouble with getting the corresponding customization to work on the EditForm page.

The problem is that when I click on “Data Fields…” to bind the dropdown to the data source, the data fields for both display text and value do not show up in the “Change Data Bindings” dialog box. When I click the OK button, it then puts blank properties for these items as datatextfield=”" and datavaluefield=”". When I attempt to edit an item, I get a browser message that says “An unexpected error has occurred.”

If I then go back into the code and manually add the correct properties (which are datatextfield=”Flow_x0020_Addition” and datavaluefield=”ID”), I am able to open the item to edit it… however, when I make a change and then click save, I then get a browser message that says “Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again.”

What am I doing wrong and/or what else do I need to do?

chiqnlips said...

dude, do you monitor this at all? it seems that you are blogging solutions to make yourself look good but you are not actually familiar with the solution. this is counterproductive in our community. if you blog a potential solution and you are not going to answer questions then at least caveat the post with that information. i am certain my comment has been posted to deaf ears but at least i am doing something.

Frode Sørhøy, Avenir, Bergen, Norway said...

Yes I do monitor the blog and the comments, but in cases where I do not have the answer - I have to leave it to the community itself to find the solutions where I myself can't find it. In some cases I just don not have time to address it. I blog about things that have helped me, and that my help other to solve their problems or maybe bring people one step further. Sorry, that I've come short to meet your expectations.

Felder said...

Great work here!

Is there anyway to get the dropdown formatted to display the field text only and not "ID;#Name"?


Anonymous said...

Thank you very much. It foes to prove Microsoft SharePoint is a big piece of garbage consultants thrive on because of its stupid complexity.

Feter said...

Did you ever found a workaround for EditForms? NewForms seems to work just fine, but EditForms does not, thay tend to raise a “Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again.” Error

Frode Sørhøy, Avenir, Bergen, Norway said...

No, I've not found a soution to the Edit-form issue.