Cross-Site Lookups in SharePoint 2010 Enterprise
How to get information from one SharePoint site to another is one of the most common type of questions that I come across. When you have SharePoint 2010 Enterprise or SharePoint Online E3 or E4, most any SharePoint list can be customized using InfoPath. All of your lists and your existing InfoPath forms can look up information from other sites. InfoPath forms have always been able to do this, so if you’re an InfoPath guru and know all about data connections, read no further.
Also, if you do not have the enterprise version, you can still accomplish this, but it’s not going to be browser-based, it will have to be created as a form library form, and all the people filling it out will need to have the client software installed on their computers.
One of the fundamentals of InfoPath is the ability to create data connections to look up information from other data sources. There are several types of sources that can be selected from, such as web services, databases, and XML files, but the one I’m going to focus on here is the SharePoint list or library.
For today’s story, the IT department has created a comprehensive SharePoint list of all of the company’s departments, along with some additional information about each one. They created this list at the root level site in the site collection http://forms.contoso.com. You are in the Marketing department, and you are creating a SharePoint list. You create a lookup column so that you can do a lookup to the list of departments, but you notice that in creating a regular lookup column in a list, the only available choices are lists and libraries on the same site as your form. This is a perfect situation where an InfoPath data connection can be used.
First, you’ll need the IT department to give at least READ access to anyone who will be filling out your form, so that they will have access to the list’s items. You’ll also need to have InfoPath 2010 Designer installed on your computer. The good news is that if you have the enterprise version of SharePoint, all of the people filling out the form do not need any client software because they will be filling it out in the browser.
- Instead of creating a column that is a Lookup as was already attempted, create a column called “Department”, and just make it a single line of text.
- In your SharePoint 2010 list, click the List tab, and click the big purple Customize Form button.
- In the Data tab in InfoPath, click the button From SharePoint List in the section labeled Get External Data.
- Your site’s URL will already be filled in here. You’ll need to put the URL of the SITE WHERE THE LIST IS that you want to look up to. In this example, the URL to the Marketing site will be needed. It doesn’t have to be parent child or any particular relationship, it just needs to be in the same site collection and you need to have been granted SharePoint permission to it. Once the correct URL has been entered, click NEXT.
- Pick the name of the desired list. In this case, it’s Departments. Click Next.
- Pick the field(s) you need. In this example, I only need the name of the department. For Sort By, also pick Department, so that they will be listed in alphabetical order in your drop-down box. Click NEXT.
- Skip the next screen about storing data, and click NEXT. Click FINISH.
- On your form (still in InfoPath), right click on the Departments list that you created at step one. Choose Change Control, and then click Drop-Down List Box.
- Right-click on this new drop-down box, and choose Drop-Down List Box Properties.
- In the List box choices section, choose Get choices from an external data source. For the data source, choose Departments. This is the data connection you created. For this particular list of departments, the default Title field in the list had been renamed to “Department”, but it’s still going to say “Title” in the value and display name boxes on this screen. Oh, and if you’re worried about there possibly being duplicates of some departments in the list, check the box to “Show only entries with unique display names”. Click OK.
Side note: If you don’t want there to be duplicate data in a situation like this, go to the departments list settings in the browser, click the department column, and choose “Enforce unique values”.
- Click the tiny blue Quick Publish button at the very top of the form next to the Save button. Close InfoPath after you get the message saying that the form was published successfully.
- Now fill out a new form in your list. You’ll see the cool new cross-site lookup drop-down box you just created!
Cross Site-Collection, Cross Web App
What about cross-site collection or across web applications? I knew you were going to ask that! Yes, this is also possible, but is a bit more technically involved. When you create a data connection to a list that is in another web app or site collection, you can follow all of the same steps above, but when you try to preview or fill out the form, you will get an error about the data being in another site collection.
“An error occurred when querying a data source”
In this case, you will need to create a universal data connection, and you will need to have access to Central Administration. This is NOT going to be possible with a SharePoint list form. In other words, cross-site collection lookups will not work in a customized SharePoint list form. A form library form must be created, and that form must be published as an administrator-approved template. This is a much, much more complex process, and will need to be covered in a separate blog post.
Thanks Laura, that was interesting. I hope you’ll tackle the cross-site collection example you mentioned at the end too – that’s a frustrating thing to try and accomplish but very commonly needed.
Sure Keith, I agree, and I plan on blogging about that later.
Thanks for posting this Laura. InfoPath is one of those things I have managed to avoid thus far in my SharePoint career. Regrettably, it makes me nervous now when anyone mentions it. Clear, well-written blogs like this one help me better grasp the capabilities and make it less scary. Thanks!
Hello Laura, This works great in Standard view but I cannot make it work in Datasheet view as the column still has the same type “Single line of text”. Trying to “Customize form” whilst in that view yields an error in InfoPath 2010: InfoPath cannot generate form template for the SharePoint list. The SOAP response indicates that an error occurred on the server: Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown. List does not exist. The page you selected contains a list that does not exist. It may have been deleted by another user. 0x82000006
Nicolas, This is not going to work in datasheet view.
From a contact list in another site I am pulling the name field across fine using the method you suggest. I also ticked address, phone, email to add the data available.
What I am struggling with is displaying this information in my form. The contact name is fine but what I want to do is to display the contact data associated with the name. Any suggestions?
So you’re picking something from a drop-down box and then you want other associated fields to be displayed?
In the list of data sources on the right, use the drop-down to select your sharepoint list data source. Expand the folders until you see the one with the little blue icon, which is the repeating part. Grab that and just drag the whole section ontp the form, as a repeating section. Select the repeating section, and in the rules pane, create a formatting rule. Condition: If Name (in the list data source) is not equal to Name (from the main data source of the drop-down they picked from), then hide this control.
I need ur help immediately how can i solve this problem with office 365 when i delete document from listview webpart this error is coming List does not exist. The page you selected contains a list that does not exist. It may have been deleted by another user – SharePoint 2010 error
I have a vacation request form, where the employee name is auto-populated on form load using GetUserProfileByName. However I could not auto-populate their employee number using that source. So instead I used a list on our site which has employee names and numbers (following directions from this blog). Is there a way that after the employee name is pulled in, that it would read the name in the other list from my site and auto-populate the employee number?
Thanks so much!
Yes you can do that. After the query to the user profile service, set the value of a query field in your other list, with the user’s name (or whatever data you have, like their login or whatever), then do the query via a rule. Then the next action in your rule is to set a field’s value to whatever number it returns from the one record it got in the query. You can read about how to do that in this post: http://www.wonderlaura.com/Lists/Posts/Post.aspx?ID=129