Supporters of End User
Web

SharePoint: Populating Drop Down List Field with Data from Different Site

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 

Mark RackleyGuest Author: Mark Rackley
The SharePoint Hillbilly

So, one of my SharePoint buddies from Dallas gave me a call yesterday and had a problem. They needed to populate a drop down field with data from a SharePoint List on another site. If the list were in the same site, it wouldn’t be an issue, just create a lookup field on that list right? Well, what do you do if the list is in another site? Okay, so maybe you know, but some of us out there don’t, okay? It turns out to be fairly simple. So, I figured if my friend found it helpful, maybe you guys would too…  It is important to note that this solution appears to only work if the different sites are somewhere in the same Site Collection which totally makes sense from a security perspective.

Okay, here’s the scenario I’m proposing. Let’s say you have a Help Desk in your organization that takes calls and logs issues, because hey… that’s what Help Desks do, right? So, as part of taking a call and logging an issue the Help Desk personnel needs to select which Facility called in an issue. You may think that the easiest solution would be to just have a choice field where you manually enter each facility as a choice right? Well, you’re wrong! That’s too easy… let’s pretend that during this rough economy your facilities are dropping like flies and this list keeps changing! Wouldn’t it be great if your had a list of all the Facilities that you could use to populate the choice field? A list that is maintained by someone else so you don’t have to mess with it? Wouldn’t that be great?  Oh wait! You do have such a list? Awesome! Oh, but it’s on a different site… wow, sucks for you… or does it? 

Here is what we are gong to do:

Add a Facility Choice Field to the Help Desk Calls List

So, for my scenario I took the standard SharePoint Issues List and added an empty choice field called “Facility”

Create a new Web Part Page and drop a NewItem Form for the Help Desk Calls on the Page

If you haven’t already done so, create a document library of type “Web Part Page” and call it “Pages”. I have done this for you in a previous blog if you need help there too. Create a new page in the document library and call it “NewHelpDesk”. This page will become the default page for entering new Help Desk Calls.

Open this page up in SharePoint Designer and drop the NewItem form for “Help Desk Calls” on the page.

Convert the Facility field to a drop down list field

Now we need to convert the Facility Choice field into a Drop Down List. This is quite easy. Simply right click on the “Facility” field and select “Format Item as->Drop Down List”

When this is done the facility field will look like this on your form:

Insert a Data Source for Facilities List

We now want to insert a Data Source on the page for our Facilities list that exists on the other Site.  The steps to do this are as follows:

  1. From the Data Source Library tab click on “Connect to another Library…”
  2. Click “Add”, Give the Data Source Library a name and specify the url to the site that has the “Facilities” list.
  3. Now when you expand the new Data Source Library you will see the SharePoint List for our Facilities list. Click on the List and select “Insert Data Source Control”.  This will insert a Data Source Control (imagine that) on the page.

Connect the Data Source to the Facility drop down list

We’re almost done.. can you feel the excitement building? Now we need to connect our Drop Down List for Facility to the Data Source for the “Facilities” list.  Follow these steps:

  1. Click on the Facility Drop Down List and then click on the little “>” button. The “Common DVDropDownList Tasks” menu appears. Click on “Data Fields…”
  2. From here the Data Bindings appear for our Drop Down List. By default it selects the field currently associated with the Drop Down List “Facility” which is what we need. It also automatically selects the Data Source we just dropped on the page “spdatasource1”. If you have multiple Data Sources you will need to select the appropriate one here.  Next we can specify which fields from our “Facilities” list we want to use in the Drop Down list. We can choose both a Display Field and a Value Field. For our purposes we just want to use the Title of the facility for both. Click “OK” and save the page.

Re-associate the New Item Form for Help Desk Calls List

Now we just need to tell SharePoint to use our newly created page as the default page when creating a new Help Desk Call entry. To do this:

  1. Expand the “Lists” from the Folder List view. Right click on the “Help Desk Calls” list and select “Properties”
  2. Make sure you change the "Content type specific forms:” drop down from “Folder” to whatever content type your list is. Mine happens to be “Issue”, yours is probably “Item”. Then click on the “Browse” button for the “New item form:” and browse to the page in your page library. Click “Apply”, save everything and you are all set.

Now when a user goes to enter a new Help Desk Call in the list the Facility drop down will be populated with the list of Facilities from the other site:

That’s all there is to it… Nothing too problematic or hairy…

So… I’m wondering… could the same functionality be duplicated using the Content Query Web Part and Web Part Connections? Might be something for me to look into.

Update

SO! Apparently if you want to follow the same procedure for your Edit Form it will not work. The Data Source will appear empty when you drop it on the page. What I had to do to get this to work for the Edit Form was:

Instead of converting the Facility Choice Field to a drop down list, you need to delete it and instead insert a “Data View DropDownList” This is done by clicking on “Insert->More SharePoint Controls…”

When you click on “More SharePoint Controls” a Toolbox Panel will appear on the right, drag and drop the “Data View DropDownList” where you need it and then add your Data Source and follow the remainder of the blog.

For some reason I had to do this a couple of times before it actually worked (Don’t you love SharePoint).

Good luck!

Mark RackleyGuest Author: Mark Rackley
The SharePoint Hillbilly

Mark has been developing software applications for over 15 years filling the roles of Project Manager, Business Analyst, Lead Developer, and Software Architect.  He has been involved in projects for such companies as Dell, Motorola, Intel and Agilent Technologies. He has worked in large corporate environments, small software start-ups, and as a consultant.  Mark currently works for UNFI where he was introduced to the world of SharePoint and has taken on a lead SharePoint architect role within his organization making key development, administration, and architecture decisions.  Mark’s goal is to help ever new architect and developer avoid the frustrations and brick walls he ran into while learning SharePoint.

Blog:  http://www.sharepointhillbilly.com
Email: mrackley@gmail.com
Twitter: http://www.twitter.com/mrackley

 

[tweet]

Comments

Boris Gomiunik

SharePoint: Populating Drop Down List Field with Data from Different Site

Neat trick. The way I used to solve this is using jquery autosuggest feature, but this worked only within the same web application due to same domain name and limits to cross-site javascripting.

Posted 25-Jan-2010 by Boris Gomiunik
Niche

SharePoint: Populating Drop Down List Field with Data from Different Site

Wouldn't Site Columns be alot simpler? You define a site column at the site collection and you can push this out to different sites.

Posted 25-Jan-2010 by Niche
Mark Rackley

SharePoint: Populating Drop Down List Field with Data from Different Site

Thanks for the feedback Niche...

I guess i'm not seeing how site columns would work for my proposed scenario. I bet Mark Miller would love to host a blog about it if you were to write something explaining in detail?

Posted 25-Jan-2010 by Mark Rackley
Ben

SharePoint: Populating Drop Down List Field with Data from Different Site

Niche was talking about the following scenario as an example...

Create a list on your top level site in a site collection that houses the items you want to reference (Facilities in this scenario).

Create a site column called Facility at the top level of your site collection and make it a look up column to the Facilities list located in the top level of your site collection.

Create your help desk list in a sub-site, add the Facility site column to this list.

Posted 25-Jan-2010 by Ben
Mark Rackley

SharePoint: Populating Drop Down List Field with Data from Different Site

Thanks Ben for the comments..

The site columns would work great if the Facilities list was at the top level site. My friend with this problem had their "Facilities" list in another sub-site. I'm not sure if it justifies moving a list to a top level site just to create a site column?

Regardless, if the list is in the top level site I agree it makes much more sense to use site columns.

Thanks again for the feedback and presenting an alternative method!!

Posted 25-Jan-2010 by Mark Rackley
EndUserSharePoint

SharePoint: Populating Drop Down List Field with Data from Different Site

Seems like it would be worth it to keep a lookup-facilities list as high up the food chain as possible, and then use it to create a site column of facilities for use through the entire site hierarchy. -- Mark

Posted 25-Jan-2010 by EndUserSharePoint
IdoSP

SharePoint: Populating Drop Down List Field with Data from Different Site

Well drat! Another COOL thing that just gets exciting until I read the words "Open this page up in SharePoint Designer and ..."

But some of the "comments" ... that gives me hope. Not all of us are "allowed" to use SPD which is why my gravatar looks the way it does. :-)

Posted 26-Jan-2010 by IdoSP
Andy Parkes

SharePoint: Populating Drop Down List Field with Data from Different Site

Mark,

Just wanted to echo what the other guys are saying

If you create a list at the top level of a site collection and create the site column at the top as well you can use it in the subsites

I do exactly this all the time

Example,
I've got a list at the top level called CUSTOMERS, I then create a site column using the lookup data type

I then can use this column in any list in any subsites underneath the first site
Your solution is still good to know about though!

Posted 26-Jan-2010 by Andy Parkes
Andy Parkes

SharePoint: Populating Drop Down List Field with Data from Different Site

Further to my last comment,

I blogged about this here:

http://andyparkes.co.uk/blog/index.php/2008/03/14/using-site-columns-to-lookup-information-across-sites-in-sharepoint-2007/

Hope it helps!

Andy

Posted 26-Jan-2010 by Andy Parkes
Yvan

SharePoint: Populating Drop Down List Field with Data from Different Site

Hi there, I came across your blog, good write though.

Quick question for you, Have you tried the same procedure using External Lists?, the ones created via BDC.

Currently, I'm customizing a page using SharePoint 2010 and SPD 2010 that needs a lookup dropdown, the form is there, however, when I add the DataSource and the Data View Dropdown to the page I get a webpart error.
Thanks

Posted 27-Jan-2010 by Yvan
Charles

SharePoint: Populating Drop Down List Field with Data from Different Site

Great solution. I am looking to do the same thing. However, instead of linking to another sharepoint list, I am looking to link to an XML file as a datasource. I tried it, but the "Select a data field for the display text" and "Select a data field for the value" does not show the fields in the XML file. Could it be a limitation of change data bindings feature or something wrong with the XML file? I am exporting the XML file from Microsoft Access. Thanks so much!

Posted 28-Jan-2010 by Charles
Mark Rackley

SharePoint: Populating Drop Down List Field with Data from Different Site

Charles,

If I had to guess it would be something wrong with the formatting of the XML? I've never tried this though, however. Give me a couple of days to look into it and I'll see what I can come up with. If you'd like, feel free to send me your XML and I'll see what I can do.

thanks for the feedback,
Mark

Posted 29-Jan-2010 by Mark Rackley
Charles

SharePoint: Populating Drop Down List Field with Data from Different Site

Mark,

Thanks so very much! It's just a simple XML file (posted below) that contains the a list of offices and an ID number. What we want is just to point to a single XML file that will be exported from another database that can populate the sharepoint drop down.

If I use the below XML code and make the DataValueField="Office" and the DataTextField="Office", then I just get back "2 San Francisco" but nothing else in the drop down choices.

Thanks so much! This is driving me crazy!

- Charles






2
San Francisco


3
Panama


4
Suite Data

Posted 29-Jan-2010 by Charles
Charles

SharePoint: Populating Drop Down List Field with Data from Different Site

Tried to post the XML code, but this website went ahead and converted it into data results - wow!

"


2
San Francisco


3
Panama


4
Suite Data

"

Posted 29-Jan-2010 by Charles
EndUserSharePoint

SharePoint: Populating Drop Down List Field with Data from Different Site

Email it to me and I'll get it inserted. -- Mark (Miller)

Posted 29-Jan-2010 by EndUserSharePoint
Mark Rackley

SharePoint: Populating Drop Down List Field with Data from Different Site

Charles,

Not having a lot of luck so far. I think the XML is probably fine. It's getting the XMLDataSource into a SharePoint control that seems to be the issue.

So far, I've successfully put the XML in both the asp.NET drop down list control and a DVWP which I then converted into a drop down list.

I'll continue digging.. there has to be a solution here.

Posted 29-Jan-2010 by Mark Rackley
Charles

SharePoint: Populating Drop Down List Field with Data from Different Site

Ok. I found the solution! And this totally opens up a new world of possibilities to populate the drop down boxes from XML feeds from other databases. Since I can't post XML here, can someone (Mark) post an email address so I can send you the solution?

Thanks!

-Charles

Posted 30-Jan-2010 by Charles
Mark

SharePoint: Populating Drop Down List Field with Data from Different Site

Good job Charles! See... you guys don't need me. :) You can email me at

mrackley at gmail dot com

Posted 30-Jan-2010 by Mark
Tim

SharePoint: Populating Drop Down List Field with Data from Different Site

I'm having issues with the Edit form in SPD2007. I simply cannot get it to bind to the data source successfully. I cannot choose the field from the data source correctly - it is blank.

Any further suggestions how to get this part working?

Posted 03-Feb-2010 by Tim
Mark Rackley

SharePoint: Populating Drop Down List Field with Data from Different Site

Tim,

Try putting the DataView DropDown List on the same row as the top "Ok" button. Then add the Datasource and connect it up.

After that, drag and drop the field where you need it. I was able to successfully get that to work for the edit as well.

For some reason the Edit field is quirkier. I would love to know why if anyone has any insights?

good luck!
Mark

Posted 05-Feb-2010 by Mark Rackley
ejwalk

SharePoint: Populating Drop Down List Field with Data from Different Site

How can I populate other fields on ths page based on the selection from the drop down list example

I am on the category page and have a droplist of products which is linked to products on another site.

I select my product from the list but also want to populate like fields on the category page from the product site.. example i select product "X" from this list and relate it to category but also want to set the category bin number to the same bin number from the product site related to product "x".

I have gotten the dropdown list to work how do i set other like values on the site once the product is selected ?

Posted 09-Feb-2010 by ejwalk
Tim

SharePoint: Populating Drop Down List Field with Data from Different Site

Thanks Mark - I'll try that and let you know.

Posted 09-Feb-2010 by Tim
Pani Bala

SharePoint: Populating Drop Down List Field with Data from Different Site

I am new to SharePoint designer. Installed SD yesterday and creating a web site for an internal project. Created a drop down list and mapped it to a field in the sharepoint list. The web page displays all the values. It is a new form type. When i tried to save, the insert skips the value selected in the drop down list and adds the other entries. I know I am missing something. Googled and gound your site. I wud really appreciate if you can provide me a tip. thanks. Pani

Posted 12-Feb-2010 by Pani Bala
ejwalk

SharePoint: Populating Drop Down List Field with Data from Different Site

Hi,

I have List A in a site and List B in its sub site. List B captures its values from LIST A based on dropdown list selection. For example:



List A: Emp ID,Name,Age

List B: Emp ID, Name,Age, Department, Designation.



In List B, EmpID is a dropdown, whose values are obtained from List A. Based on Emp ID selected, Name and Age gets populated. The user can enter the Department and Designation and can update the list.



Is there any solution to this?

Posted 15-Feb-2010 by ejwalk
Tim

SharePoint: Populating Drop Down List Field with Data from Different Site

Now that I have cross-site list linking working, I'm looking at customising it a bit. I have a list of projects, but on the drop-down list they are appearing in ID order, not alphabetical order. If I edit the DataSource to order by Title, we're back to the usual issue of the cross-site list failing to be filled in.

Any ideas?

Posted 19-Feb-2010 by Tim
Dusty

SharePoint: Populating Drop Down List Field with Data from Different Site

This does not appear to work.

Made a copy of NewForm.aspx for my list and renamed.
Dropped custom new item form into WebPart Zone.
Converted field to DropDownList and added datasource from another list in the same web.
When attempting to view the page. The following error is displayed: Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.

DDL works fine is it and the datasource are outside of the DVWP. HELP!?

Posted 12-Mar-2010 by Dusty
Dusty

SharePoint: Populating Drop Down List Field with Data from Different Site

Nevermind... now it works. However it must be done as Mark descibes in the update.

Insert the SPDDL into the DVWP.
Insert the datasource outside of the DVWP.
Hookup the SPDDL.
Drag the datasource into the same cell as the SPDDL.

Thanks!

Posted 12-Mar-2010 by Dusty
Tony

SharePoint: Populating Drop Down List Field with Data from Different Site

Thanks for the great article, but I am having this problem: Say a user makes selections in the drop down lists, and then clicks OK, but the user is not allowed to continue (for example a validation error is found upon postback: a nonexistent user is given in a "people" field on the form)... in this case the drop downs appear to remain selected but when the form is finally submitted, no data will be added to the list for these fields! The fields just end up being recorded as blank.

This seems to be the case for any postback that doesn't result in submission of the data. I am using a third-party field that causes the page to post back, and this also results in null submissions for the customized drop downs. Does anyone know of a solution to this?
Thanks!!

Posted 24-Jun-2010 by Tony
Tony

SharePoint: Populating Drop Down List Field with Data from Different Site

Okay, I seem to have solved the problem by setting EnableViewState="false" on those DropDowns. Hopefully I didn't introduce any new problems by doing that, and I don't know why I did it, but so far so good!

Posted 24-Jun-2010 by Tony
Byron BAxter

SharePoint: Populating Drop Down List Field with Data from Different Site

Charles or Mark,

i am having the same issue - what is the solution?

thanks!

Posted 22-Jul-2010 by Byron BAxter
swati

How do we do this in sharepoint 2010

How do we do this in sharepoint 2010

Posted 30-Sep-2011 by swati
SK

NewItem form????

"Open this page up in SharePoint Designer and drop the NewItem form for “Help Desk Calls” on the page."
 
Help!!!! I don't see NewItem form, I have NewForm form. My enrivonment is SP2K7 and SPD2K7.
 
How to drop new NewItem form on SPD2K7????
 
 

Posted 10-Feb-2012 by SK
SK

NewItem form????

"Open this page up in SharePoint Designer and drop the NewItem form for “Help Desk Calls” on the page."
 
Help!!!! I don't see NewItem form, I have NewForm form. My enrivonment is SP2K7 and SPD2K7.
 
How to drop new NewItem form on SPD2K7????
 
 

Posted 13-Feb-2012 by SK
SK

NewItem form????

"Open this page up in SharePoint Designer and drop the NewItem form for “Help Desk Calls” on the page."
 
Help!!!! I don't see NewItem form, I have NewForm form. My enrivonment is SP2K7 and SPD2K7.
 
How to drop new NewItem form on SPD2K7????
 
 

Posted 16-Feb-2012 by SK
balaji

SharePoint 2010 dropdown box shows multiple entries under common name

 

i need your help to fix this issue in sharepoint 2010 site.
 
the problem is when the drop-down box while editing a form is clicked it shows multiple entries under common name, i have no idea why it appears like that.
 
i want to remove the duplicate entries, can you help me with it.

Posted 13-Mar-2012 by balaji

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: