SharePoint: Populating Drop Down List Field with Data from Different Site
Guest 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:
- From the Data Source Library tab click on “Connect to another Library…”
- Click “Add”, Give the Data Source Library a name and specify the url to the site that has the “Facilities” list.
- 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:
- Click on the Facility Drop Down List and then click on the little “>” button. The “Common DVDropDownList Tasks” menu appears. Click on “Data Fields…”
- 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:
- Expand the “Lists” from the Folder List view. Right click on the “Help Desk Calls” list and select “Properties”
- 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!
Guest 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: [email protected]
Twitter: http://www.twitter.com/mrackley
- SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date
- Creating a SharePoint List Parent / Child Relationship - Out of the Box
- Passing Multiple Query String Variables Using SPD – Follow Up on Creating Parent / Child List Relationships
- Setting SharePoint Form Fields Using JavaScript
- Tips When Asking For SharePoint Help
- Setting SharePoint Form Fields Using Query String Variables Without Using JavaScript
- Creating a SharePoint List Parent/Child Relationship – VIDEO REMIX
- SharePoint: Populating Drop Down List Field with Data from Different Site
- Building The Right SharePoint Team For Your Organization
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.
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.
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?
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.
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!!
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
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. :-)
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!
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
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
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!
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
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
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
“
Email it to me and I’ll get it inserted. — Mark (Miller)
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.
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
Charles or Mark,
i am having the same issue – what is the solution?
thanks!
Charles or Mark,
I’ve looked all over for a solution to this exact problem. Can you please share the solution? Thanks!
Good job Charles! See… you guys don’t need me. :) You can email me at
mrackley at gmail dot com
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?
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
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 ?
Thanks Mark – I’ll try that and let you know.
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
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?
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?
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!?
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!
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!!
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!
Hi Mark,
I have one related query, could you please help me in find out solution.
Problem Overview:
I have one List, Which has some Fields like
1. Title
2.Location
3.People Picker Control
4.Start Date and Time
5.End Date and Time
6 Lookup Field -Name Template.
All these are OOTB field of List.
Issue:
I need to populate OOTB list control on Lookfield item change ,
Data should come from different list on Same site..One field would be common between two list.
Thanks