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.
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.
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.
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.
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.
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:
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.
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
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