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.

Tips on Action Usage and Common Mistakes

Use sandboxed workflow actions for External Lists if:

• You do not have permission to deploy full trust activities and want any of the following:
• Read many values from an external list
• Write a simple function that will work on regular lists as well as external lists

Things to keep in mind:

• Sandboxed actions can cause your workflow to fail if it fails to obtain a sandbox worker process application domain. There are an administrator configured fixed number of these application domains, and they are shared with all sandboxed code (actions, web parts, event receivers, etc.). Because of this, be cautious of using sandboxed actions in the following situations:
• Complex sandboxed code actions that take time to complete
• Slow external lists
• Environment where many workflows will run at roughly the same time
• Scenarios that cannot handle workflow failures
• Sandbox code service can run these calls with a different account than the application pool account, so you will need to ensure that this account has been set up in the Secure Store Service (SSS).
• If you are storing a local copy of the data you have read from an external list item, like I do in the sample below, you will need to ensure that your data is fresh.

Building the Scenario- Sandboxed Workflow Action

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.

Create the Sandboxed Workflow Action

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!

Setup Instructions

To use the attached sample, you will need to do the following

1. Unzip the attached file
2. Open in Visual Studio 2010
3. Rebuild the solution
4. Deploy the solution
• If you are deploying to a separate machine, you can use the WSP created from step 3 and do the following:
• Go to Site Settings
• Under Galleries, go to Solutions
• Upload the WSP and activate it

Building the Workflow

1. In SPD, create an External Content Type called SafeLimit
• It should have seven fields: EmployeeID, EquipmentLimit, EquipmentApprover, MoraleLimit, MoralApprover, TravelLimit, TravelApprover
2. Create an external list which the SafeLimit External Content Type called SafeLimits
3. Create a Document Library for expense reports called ExpenseReports
• Add two additional columns for EmployeeID and ExpenseType
• ExpenseType should be a choice field with the following choices: Morale, Equipment, Travel
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
• Note: At this point, you are in the workflow designer, and you start to add your actions
7. Insert the “Get External List Item by Field (Sandboxed Function)”
• Pick your SafeLimits external list for the first parameter
• Pick the EmployeeID column using the CurrentItem by using the button for the second parameter
• Type in “EmployeeID” for the third parameter
• You can leave the output parameter the same(Defaults to ItemString)
8. Now to read a column of data from that item you stored in ItemString, insert the “Read External List Item Data (Sandboxed Function)”
• For the first parameter, Type in a field you want to see. For this case, we will use the value stored in the choice field ExpenseType and append “Limit” to it
• For the second parameter, choose the ItemString variable
• The last parameter will be the value that is found, call it “limit”
9. Repeat step 8, expect append “Approver” to the ExpenseType Value and change the output to “approver”

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