@WonderLaura

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.

1. 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.
2. In your SharePoint 2010 list, click the List tab, and click the big purple Customize Form button.
   
3. In the Data tab in InfoPath, click the button From SharePoint List in the section labeled Get External Data.
   
4. 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.
   
5. Pick the name of the desired list.  In this case, it’s Departments.  Click Next.
   
6. 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.
   
7. Skip the next screen about storing data, and click NEXT.  Click FINISH.
   
8. 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.
   
9. Right-click on this new drop-down box, and choose Drop-Down List Box Properties.
10. 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”.
    
11. 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.
12. 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.