Using SharePoint workflows with Business Connectivity Services (BCS)

Hi, my name is JD Klaka, a test lead on the BCS team. I have worked on both SharePoint Workflow and BCS, and I love solving user scenarios by combining features and learning more about SharePoint. In this series, I will show how workflow and BCS can be used together to enable powerful scenarios.

Why would you want to use BCS and Workflow? A simple scenario is a basic expense approval where you want a workflow to send the expense report to an approver only if the total expense amount is greater than a “safe limit”, but otherwise auto-approve. Each person’s safe limit is different and can be looked up from an external system, so you will need BCS to retrieve that data. If this data is surfaced in an external list, a workflow can lookup data within that list to perform its logic.

In this first post we will cover some general things to keep in mind when using BCS and Workflow, and how to build the workflow mentioned above using the out-of-box activities in SharePoint Designer (SPD). Future blog posts will highlight the pros and cons of doing similar tasks using a custom sandboxed workflow action and custom full trust workflow activities.

Things to Keep in Mind

Before we get into the scenario using SPD, it is best to call out some aspects of the feature design that you will need to keep in mind no matter how you build your workflow.

Workflows cannot be associated with an External List

In SharePoint 2010, workflows cannot be associated directly with external lists. This is because the data is not stored in SharePoint, so the workflow cannot be notified when items change. This does not mean that workflow does not work with external lists. You can create a site workflow, or just have a list workflow on a regular list, like 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 SPD, although the link to the task will always show no title for the external list item.

Workflows accessing BCS will always run as service account, even under impersonation step

Workflow will always run as a service account (typically the IIS Application Pool account) and is only supported when using Secure Store Service (SSS) or RevertToSelf (which is turned off by default due to security implications). This limitation is designed to protect SharePoint from malicious models/developers. Because access to the backend will always be initiated as one account, you will lose track of who is making the changes. To work around this, you can have the workflow pass the SPUser name to a column on the external list or to a custom activity that uses the BDC APIs, but this would be more for informational purposes and shouldn’t be used as an iron-clad security feature.

Building the scenario- Out of box SPD Activities

Using SPD List item activities against external lists should be familiar if you have used them to operate on regular lists. The main difference is that you will not find an item by ID, but rather by its identifying column.

Building the safe limit workflow using SPD Activities

Let’s go back to the scenario described above where you want to use an external list to get users’ safe limit for expenses. To build your workflow, you can do the following:

1. In SPD, create an External Content Type called SafeLimit
   • It should have two fields. EmployeeID and Limit for the safe limit value.
2. Create an external list with the SafeLimit external content type called SafeLimits
3. Create a Document Library for expense reports called ExpenseReports
   • Add an additional column for EmployeeID
4. In SPD, go to the ExpenseReports document library.
5. Click on New List Workflow
6. Give the workflow a name and description and continue
   • At this point, you are in the workflow designer, and you start to add your activities
7. Insert a “Set Workflow variable” action
8. Click on the workflow variable hyperlink, click on create new variable, call it SafeLimit
9. Click on value and then on the button to start building the lookup into the external list
10. For Data source, choose the SafeLimits external list. This is the source for where you want to pull the safe limits from
11. For Field from source, choose the field that shows the safe limit. In my example, it is called “Limit”
12. Now we have to define what we are going to look the item up by. In this case, we want to find the external list item by the employee ID, so pick the EmployeeID field.
13. Now we have to pick the value you want to look for. In our scenario, we are going to pick the employee id field from the current item, so click the button in the dialog.
14. In the new dialog click Current Item for Data source and EmployeeID for the field from source

When you are done, it will look something like the image below. Now you can take your SafeLimit variable and use it to make decisions in the workflow.

Note that after you press ok you will get a warning that looks like the one below. You will always see this for external list items, so don’t be alarmed. It is telling you that if you picked a column to find the external list item that does not have unique values, it will return the first one it finds.

Tips on Activity Usage and Common Mistakes

Use these activities if:

• You need to 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 will first call the Read List method (Finder) and then the read item method (Specific finder) for each column read. So if you are reading 10 properties in a list of 2000 items, this will cause 20 calls to the BDC and 20010 items being pulled from the backend.

• You need to create, update or delete a small number of items from an external list

Things to keep in mind:

• If you are trying to look for an item (for example, the office number of a particular employee) and NULL is returned, you cannot tell the difference between 0 items returned and 1 item returned but that particular field of the item NULL. To work around this problem, you can first confirm existence of the item – for example, find the employee based on name or ID. If the employee exists, then find the office number; if it is NULL, you know that the employee has no office number listed.
• After creating a new item in an external list, any changes made to that item from the same workflow must refer to the item by the BDC Identity that is returned from Create List Item. Trying to find the item by looking for a value from any other column will fail.
• You will never get a value back for BDCIdentity if you try to read it on an existing item. You must find existing items by looking for columns other than BDCIdentity.

In the next post we will look at how you can use a sandboxed workflow action to work around some of these limitations.

- JD Klaka, Sr. Test Lead