Receiving Data from SharePoint Lists and Business Connectivity Services

  • 10/15/2011

The External Item Picker and Business Connectivity Services

Many companies use BCS in SharePoint to connect to external systems such as customer or product databases and make that information available in SharePoint. BCS is different from the previous types of data connections we’ve discussed in one major way: the data resides in its own application rather than in a SharePoint list. BCS gives SharePoint (and thus InfoPath) a “view” into that data, but the data still resides natively in its own application (typically a database).

Fortunately, for situations in which your company is utilizing BCS in SharePoint 2010, InfoPath 2010 has a control called the External Item Picker that exists specifically for consuming data from BCS. When added to a form, this control provides an easy way for users to select items from the data source to which BCS is connecting. For example, perhaps the user is filling out a customer service form, and you’d like her to select the customer name directly from BCS rather than creating a duplicate list of that information in SharePoint and another data connection in your form.

BCS is typically set up by a SharePoint administrator. Assuming that BCS is in place for you to utilize, the External Item Picker has several unique advantages for the InfoPath forms designer, including a pre-built security and data access model.

In the following exercise, you will follow the steps to add an External Item Picker control on to a form and set the control properties so that you can select an item from an external data source via the BCS. This exercise makes some assumptions about configuration work that is already done on your system to set up an external content type (ECT) in BCS. The ECT uses XML to define data that is stored in an external system, such as a Microsoft SQL Server database. If you’re not the SharePoint administrator, you will need to work with that person to gather some information that is required—and perhaps provide some instructions about how you need the ECT configured to make it work with InfoPath.

  1. Create a new blank form, and then publish it to a form library in SharePoint.

  2. httpatomoreillycomsourcemspimages1713906.png Add an External Item Picker control to your form.

  3. Right-click the control, select the control properties, and then click the General tab.

    This page includes several properties that you will need to gather from your SharePoint System Administrator. The easiest way to collect these properties is by going to Manage Service Applications in Sharepoint 2010 Central Administration and choosing Business Data Connectivity Services.

  4. Click the specific ECT to which you want to connect. The following screenshot, shows an ECT with the name Sales Order DB.

  5. Gather the following information from the External Content Type Information page:

    • Name

    • Namespace

    • External System

  6. Switch back to InfoPath, with the properties of the External Item Picker control still open. Then do the following:

    • Populate the ECT Namespace field with Namespace.

    • Populate the ECT Name field with Name.

    • Populate the System Instance Name with External System.

    • Skip the Finder Name for now; you will add that data in a later step.

    • Populate the Display Field Name with the actual database field that you’d like to use in the control. If your administrator used SharePoint Designer to create the ECT, you can easily go to the properties of the ECT in Designer and see all the available fields, as shown in the following screenshot. In this example, it is Customer Contact.

  7. In the control properties dialog box, switch to the Other Settings tab. Ensure that the Refresh on Open check box is selected, and then switch the Picker Mode drop-down box at the bottom of the page to Connect to External Data Source through SharePoint. You need to set it that way because you are using BCS in SharePoint.

  8. Switch back to the General tab.

    You are almost finished, but to have your control work properly, one very important field remains to be filled out: the Finder Name field. The Finder Name is equivalent to a filter on the ECT. This gives the user the ability to search for information in the control. If you do not configure a filter, the user will be presented with a list of all results from the database, and he will have to scroll through them all rather than simply searching.

  9. If your administrator has already defined a filter on the Read List operation of the ECT, add the filter name to the Finder Name field in the External Item Picker Controls Properties dialog box. In this example, the filter name is simply Filter.

  10. Click Apply, and then OK.

    You should be back on your form design surface. Use the Quick Publish button to update the form library with your new form. If you go to the form library and create a new form instance, you should be able to use the External Item Picker control to browse and search for data from the external system.