2007-04-22

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 :-)

32 comments:

Anonymous said...

Hi,

I am not able to get from step 7.

Or should we create two lists?

thanks

Alex Talarico said...

I got this to work just great - but in my situation I need the filter condition to be based on another dropdown - so for example:

In your case you show me how to filter 'cities' based on a 'state' that you choose in the "filter" options for example.

I would like your input on how you would filter the cities by changing the 'state' on another dropdown. So for example, one dropdown has a list of states, another dropdown is empty. When you select a state from the first dropdown, the second dropdown gets filtered and shows the cities from that selected state...

I hope this makes sense :) great post though, definitely shows a lot of promise for the capabilities of designer tools.

Nalini said...

My experieced is that if we use "i" in __designer:bind property, it creates new list item. Hence I used "u" instead of "i". Now
If I Edit already saved lookup field values, It gives "Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again."

am I missing anything? Appreciate help.

Anonymous said...

Great Post. When you say create a sharepoint dropdown list do you mean dvdropdownlist? Also my validation doesn't work anymore on required fields, any idea on that?

Jean-Pierre Fouche said...

I get Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again." Do you have an answer to the above?

Daniel said...

Anybody solve the problem on the EditForm page? Able to add a filtered lookup on the NewForm, but get the above mentioned error on the EditForm.

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

Hi.

I've not had time to look into the problem and why you get the error. But please post your findings in case you do.

Frode

Feter said...

I have created a custom new form following your instructions. It worked just well.

But when I applied the very same concept to a customized Edit form the form fails when it is submited... stating that there were a conflicting data source error.

I am experiencing the very same problem nalini posted before... did you experience this same error?

Is there a way to solve it?

Anonymous said...

Excellent entry. Your solution worked perfect.

Thanks,

John Haigh

Anonymous said...

Hello,

I also cant get from step 7.

How to insert data connection?
How to configure dvdropdown list? Write the settings in design view or in property window?

Thank you very much in advance

Mitch said...

Hello,

I get the following error:

An unexpected error has occurred.

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

I dont know where i made mistake.

Maybe in the datasource? How can i test if the datasource is ok?

DVDropDown is configured as wirtten in your blog.

Thank you for any help
Best regards
Mitch

Anonymous said...

Hi, very usefull article. But how can i filter my lookup based on query string ?


Thanks.

Mitja said...

Hello,

i get stuck in creating data source. If i make copy/modify od a list i still cant select this DS in the properties of a dropdown menu.

What did i miss in creating DS?

thank you very much for your help,

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 !?

Regards,

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!

dobbelaere.glenn@herrenknecht.de

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?

Thanks

Blanca said...

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

Thanks

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"?

Thanks

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.

Frode

Mittali said...

I got this error on my edit page "Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again.". whats the solution for it

srila said...

I get Data Source Conflict: Your changes conflict with those made concurrently by another user. Refresh the form and submit your changes again." Does any one has solution to this??

Wiktor said...

Guys,
if you want lookups based on selection from other fields this is your solution.

http://cascddlistwithfilter.codeplex.com/

Cheers

Anonymous said...

FYI, you can do this without the SharePoint Designer:
http://www.sharepointforums.org/forums/moss-development/997-filter-lookup-column.html

Stephanie said...

I've found that on the EditForm, if you use a Custom List Form, for some reason the DVDropdown will not load the previously stored value for a column, no matter how many different work arounds I tried.

However, if I used a Data View and inserted it as a Single Item Form, the DVDropdown worked perfectly.

Go figure. Probably another SPD bug.