In the last post, we covered how to use the out of box SharePoint Workflow activities to work with an External List. In that post, I mentioned that every time you read a column, it will execute both the Read List method (Finder) and the Read Item method (Specific Finder), and that could cause unnecessary calls to your external system. One of the ways to decrease that load on the external system is to get the item once, and store it in a way that you can reuse that data. In this post, we will show how you can implement such a solution using the SPList Object Model to read from the external lists in a Sandboxed Workflow Action. Sandboxed Workflow Actions are code functions that can be used in a declarative workflow designer and called from a declarative workflow. They are written in Visual Studio and uploaded to SharePoint’s Solution Gallery. These actions run in isolation to ensure that the core system process is protected and boundaries respected.
To expand upon the previous scenario where we only have one safe limit for all expense submissions, in this scenario we will add Safe Limits for certain budget categories. For instance, I may have different safe limits for travel expenses than I do for equipment expenses. When an employee submits the expense report, the workflow will validate the category and its expense limit to determine what action needs to be taken.
I’ve attached a Visual Studio project to this post that implements the actions we will be talking about. At a high level, we want to be able to read an item once and then read other properties out of that one transaction to the external system. We also want this action to show up in SharePoint Designer (SPD) so that more users will be able to write workflows against our list. Because SPD does not support looping, the action can’t return an array of items. So instead, we will create an action to read an item and store all of its properties in an xml string. Then we’ll create another action to read a specific field value out of this xml into an SPD variable. There are optimizations you can do to this, like only store the values you know you are going to read, but this example is implemented to be the most generic and reusable way to return properties for an item with any set of properties. Let’s dive into the details!
To read values from the external list we have the function with the following signature:
public static Hashtable GetExternalListItemByField(SPUserCodeWorkflowContext context, String externalListId, string fieldName, string fieldValue)
This function takes an external list id and then finds an item in that list by finding the first item that has “fieldValue” for a field named “fieldName.” Once the item is found, it puts each field into a Data Table and saves it as XML so that we can parse it later. This function is pretty generic and could even work on regular list items as well. This will help us improve our performance because it will only call the finder and the specific finder once and get all of the properties; however, we will still call the finder. This can be optimized so that you only call the finder twice, but that is an exercise we can explore later.
Once we have read the item, we have the function with the following signature:
public static Hashtable GetItemDataFromString(SPUserCodeWorkflowContext context, string itemData, string value)
This function takes the XML string with all the item properties in “itemData”, reconstructs the Data Table, and looks up the field specified by the “value” parameter. This does involve reloading the Data Table on every call of the function, but can be much faster than going to the external system for every call. It also allows the SPD user to pick which columns they want to look at instead of hardcoding it into the function itself.
The other file of interest in the project is the elements.xml file in the BCSWorkflowActivities directory. This elements file is what tells SPD how the sandboxed action should be presented in SPD as well as how the user can map the properties. The details of this file are out of scope for this post, but it’s has the same basic structure as the ACTIONS xml SPD used in 2007.
And now, to play with the sample!
To use the attached sample, you will need to do the following
You should end up with something that looks like this:
That’s it on Sandboxed Workflow actions and external lists from me. In the next blog post, I will explain how you can build the same scenario using full trust activities that call the BDC APIs. By calling the BDC APIs directly, we will have more control over exactly what calls are made to the external system as well being able to operate on that data without needed an external list.
- JD Klaka