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 you 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!
</update>
posted @ Thursday, January 21, 2010 2:56 PM