How to: Use Business Connectivity Services with SharePoint Workflows

Applies to: SharePoint Server 2010

Microsoft Business Connectivity Services (BCS) enables you to access external data in SharePoint workflows in two ways:

  • Through codeless workflows authored using Microsoft SharePoint Designer 2010 that interact with external lists.

  • Through workflows that contain code (code workflows) authored in Microsoft Visual Studio that interact with external lists or the Microsoft SharePoint 2010 or Business Connectivity Services object models.

Before you build workflows for your scenarios, consider the following:

  • Workflows cannot be associated with an external list. In SharePoint Server 2010, although workflows work with external lists, they cannot be associated directly with external lists. You can create a site workflow, or have a list workflow on a SharePoint list, such as a document library, and have it read or update from an external list. You can also use an external list item as a destination for a task process in SharePoint Designer; however, the link to the task will not display a title for the external list item.

  • Workflows accessing Business Data Connectivity (BDC) service always run as a service account, even under the impersonation step. A workflow always runs as a service account and is supported only when using Secure Store Service or RevertToSelf (which is disabled by default because of security implications, see Business Connectivity Services Security Overview for more information). This limitation can help protect SharePoint 2010 from malicious BDC models and code. Because access to the back-end system is always through one account, you can lose track of who is making the changes. You can have the workflow pass the SPUser name a column on the external list or to a custom activity that uses the BDC APIs. This scenario is presented only for informational purposes and should not be used as a security feature.

Defining a Codeless Workflow on a SharePoint List to Update an External List Using SharePoint Designer 2010

SharePoint Designer 2010 enables you to create workflows that do not use code (codeless workflows) that you can attach to any SharePoint list or site. The Create List Item, Update List Item, and Delete List Item workflow activities for SharePoint lists are extended to support external lists. In addition, you can use other activities such as Set Workflow Variable with external lists to pick a list or an item from a SharePoint list. The list workflows can be started automatically when a list item is added or changed, and they can contain conditional logic for associated actions. Site workflows do not require a trigger to start.

By using SharePoint Designer 2010, you can configure a list activity such as Create List Item or Update List Item in a SharePoint list to read or write data to an external list. For example, you can create a workflow that updates a list item in an external list whenever a list item in a default SharePoint list or a document in a document library changes. The following procedure walks you through the steps for creating a simple workflow that updates the Address field in a Customers external list when a New Contact Information document is added for the customer in the Customer Contacts document library.

To create a workflow that automatically updates an external list when a new item is created in a document library

  1. In SharePoint Designer 2010, browse to the Customer Contacts document library.

  2. On the Server ribbon, click New List Workflow.

  3. Specify a name and description for the workflow and click Next. The Workflow Designer opens in SharePoint Designer 2010.

  4. Insert an Update List Item action, as shown in Figure 1.

    Figure 1. Inserting the Update List Item action

    Inserting an Update List Item action

  5. Click the this list link and browse and select the Customers external list in the List field. Click the Add button to add the fields you want to update in the external list.

    Figure 2. Add fields to update in the external list

    Add fields to update in the external list

  6. In the Value Assignment dialog box, select the Address field from the external list that you want to update and the matching Address field in the document library, and then click OK.


    The Value Assignment dialog box shows all the fields in the return parameter of the SpecificFinder. If a Creator or Updater requires only a subset of the fields, select only those fields in this dialog box.

    Figure 3. Values Assignment dialog box

    Value Assignment dialog box

  7. Specify the matching fields in the source and destination lists so that SharePoint can identify the matching items in the document library and the external list. To do this, select the CustomerID field from the external list and the Current Item: Client Id from the document library (assuming these fields can be used to identify the matching items in both lists). Click OK.

    Figure 4. Specify matching fields in the source and destination lists

    Specify the matching fields

    You have now created a simple workflow that updates an external list, as shown in Figure 5.

    Figure 5. Simple workflow that updates an external list

    Simple workflow

  8. To automatically start this workflow when an item is created in the Customer Contacts document library, click the Workflow Settings button on the ribbon, and then click Start workflow automatically when an item is created, as shown in Figure 6.

    Figure 6. Workflow start options

    Workflow start options

You have now successfully created a workflow that automatically updates an external list when a new item is created in a document library.

Tips on Workflow Activities, Usage, and Common Mistakes

Use the built-in SharePoint workflow activities in these scenarios:

  • You must read a small number of columns in an external list to use later in your workflow.

    Reading multiple values from the same external list item does not cache the item. Because of this, the activity first calls the Read List method (Finder) and then the read item method (SpecificFinder) for each column that is read. So reading 10 properties in a list of 2,000 items causes 20 calls to the BDC, and retrieves 20,010 items from the external system.

  • You must create, update, or delete one item from an external list.

It is important to know the following:

  • Reading a value from an item that has no value returns null. In SharePoint 2010, this is the same as empty, but in external lists, there are differences between null and not found.

  • After creating an item in an external list, any changes made to that item from the same workflow must use the BDC identity that is returned from Create List Item.

  • By default, Business Connectivity Services shows all SpecificFinder fields. So if a Creator or Updator requires only a subset of the fields, select only the required fields in the workflow user interface in SharePoint Designer 2010.

  • The only way to obtain a BDC identity is from a Creator. You should use the BDC Identity returned by the Creator if you want to find the just-created external item. You cannot find it by using any other field. Also, to find an external item you just updated, you cannot use the BDC Identity because it is not returned by the Updator. The only way is to find it by another field.

  • If you are creating a workflow to use with an InfoPath form, make sure that the correct fields in the form template are promoted as SharePoint columns during the publishing process so that they can be used in workflow conditions and actions.

Creating Code-based Workflows Using Visual Studio

By using Visual Studio, you can create code workflows that interact with external data via external lists or the SharePoint 2010 object model and Business Connectivity Services object model. You can also create custom workflow activities. For more information, see the SharePoint Workflow topics in the SharePoint 2010 SDK.