SharePoint Looping Workflow - How to: Loop through and process all items in a list - SharePoint Development & Administration + InfoPath

Wednesday, January 13, 2010

SharePoint Looping Workflow - How to: Loop through and process all items in a list

Objective
To use SharePoint workflows to loop through all items in a list to perform calculations, process and update metadata values, or concatenate the value of a specific column in all list items into a single string.

Background
The OOB workflow actions provided by SharePoint don't allow a loop to be configured explicitly. Instead, a number of workflows can work together to create a looping affect. Another limitation of Workflows in SharePoint using SharePoint Designer (SPD) is the inability to simply test if the user who initiated the workflow is a member of a SharePoint group, or to test if the value of a field in the item which initiated the workflow is equal to the value of a specific field from any item in a separate list.

For example, the OOB Approval Workflow requires that a user who is an Approver of changes to approve their own changes each time a modification is made. In many cases, Approvers are also the users who make many of the changes that require content approval which can result in the user wasting time and becoming frustrated when many documents/items need to be updated. To simplify the workflow, you could loop through each item in a separate list that lists each of the users who are approvers to check if the user who initiated the workflow is an Approver. If the user is an approver, the Content Approval Status of the item can be set to "Approved" making the changes visible to other users. The list of approvers would been to be stored in a list instead of a SharePoint group, as SharePoint Designer doesn't allow you to test if a user is a member of a group. Storing the list of approvers in a SharePoint list allows you, or someone else to easily add/remove users from the list. Note that the users added to the approvers list would still require the appropriate permissions applied to approve changes. To loop through the items in the Aprovers list, a looping workflow would need to be configured to process each item in the list.


How To Create a Looping Workflow in SharePoint

A number of lists will, along with multiple workflows configured to trigger each other will be used to create the looping workflow. As there is no option when developing OOB workflows using SPD to loop through items in a list, a separate workflow will be configured to perform the required actions on a single item. This workflow would start when an item is modified, completing the required actions/processing for the item. When complete, the "Item Processor" workflow will re-initiate the parent workflow which will then initiate a workflow to process the next item in the list. A custom Boolean (True / False) field "Processed", or similar will be added to the list that contains the items that need to be processed. This field will be used to find and trigger the workflows to process each item in the list once.

When all items in the list have been processed, the workflow not be able to find the next item in the list based on the specified criteria and as a result will cause the "List item not found" error and the workflow will stop.

As new instances of a workflow won't always start if there is another running instance that has an error, the parent workflow will be initiated each time by creating a new item in the Workflow Control list. The parent workflow can remove the workflow control item once it is complete to help keep the SharePoint lists tidy.


Considerations & Limitations
If Service Pack 2 or higher has been installed on the servers hosting SharePoint, workflows will function different to an environment at the SP1 level or less as after SP2, an "On Change" workflow won't re-initiate itself when the current item is modified. This can affect looping workflows, as they may have been configured to loop by updating the current item. The looping workflow solution detailed below should work on servers with or without SP2 installed.

Looping Workflow Components

Lists/Libraries:


Items - The list containing the items you wish to process using a loop.
The Items list will need to have a Boolean column/field added with a title "Processed" or similar, which will be used to create the looping functionality as well as to determine if an item in the Items list has been processed or not.

Workflow Control - The list to initiate and control the workflow loop. Use a custom list for the Workflow Control list, as the title of Workflow Control items will be used to help control the Parent workflow.

Workflows:

Item Processor:
- Associated with the list of items you wish to process.
- Starts when an existing item is modified.

Workflow Control:
- Associated with the Workflow Control list.
- Starts when a new item is created.


Looping Workflow Sequence:
Each step in the following sequence should be interpreted as a step in a Workflow created using SharePoint Designer. Step 2.2 (Items Processor) can be configured in as many workflow steps as required to complete the processing for an item. It is important that the remaining steps are configured and executed in the sequence below.

Workflow Control (Parent) Workflow (1):
1. Item created in "Workflow Control" list initiates parent workflow.
1.1 - Update the "Processed" field from an item in the "Items" list to No/false: find the item by searching for an item in the list where "Processed" = Yes/true
1.2 - Remove the current item as it is no longer required.

Items Processor Workflow (2):
2. Workflow starts when an Item is modified by Parent Workflow (Processed = No/false)
2.1 - Stop if "Processed" = yes/true (Prevent instances of the workflow running when they are not supposed to, if a user makes a change in the item which triggered the Item Processor workflow).
2.2 - Complete processing/actions required for the current item.
2.3 - Create a new item in the "Workflow Control" list to continue the workflow loop.
2.4 - Pause for 1 minute (allow the remaining items in the list to finish processing)
2.5 - Set Processed = Yes/True : Reset the item for next time it needs processing.

Looping workflow diagram (Click to enlarge):
Looping workflow diagram


















Looping Workflow Details:
1.1 The parent workflow starts when an item is added to the Workflow Control list. All the Workflow Control workflow needs to do then is update the value of the "Processed" field in of an item in the "Items" list, by finding the first item where "Processed" equals Yes/true. This will initiate the Items Processor workflow for first item found that matches the criteria. The current item in the Parent (Workflow Control) list can the be removed as it is no longer required.
1.2 - Once an Items Processor workflow has been initiated, the current item in the Workflow Control list is no longer required and can be removed. Once all items in the "Items" list have been processed, Step 1.1 of the Workflow Control workflow will have completed, as it will halt with the "List item not found" error, which will result in one (the last) item in the Workflow Control list to remain.

2.1 When the Items Processor workflow is initiated after a Parent workflow sets the "Processed" field to Yes/true, the first step is to test if the Processed field equals yes/true, and stop the workflow if it does not. This prevents the workflow from initiating out of the looping workflow context. If using SharePoint with SP1 or lower, the final step of the "Items Processor" workflow (Setting the "Processed" field back to Yes/true) can cause an endless loop. Stopping the workflow in the first step if Processed doesn't equal Yes/true will prevent an endless loop.

2.2 The next step(s) of the items Processor workflow complete the actions or processing required for the current item. This may include adding up the value of a numeric field to find the total for all items in the list, or concatenating the value of a field in from all items in the list into a single string which can then be used to perform more complex tests/tasks based on data from multiple items. If calculating or generating a single value or string from data from all items in the list, you will need to store the results of the calculations in a separate list item that each workflow would refer to and update after completing the processing for an item. For details of an implementation of this example, see the article How to wait for a change in any list, wait for multiple field changes in the current item (SharePoint Workflow) (Test if an item exists in a list with certain criteria, continue workflow if not and create a new item in the list with the specified criteria).

2.3 Creating a new item in the parent "Workflow Control" list re-initiates a new instance of the parent workflow, which will find and initiate the Items Processor workflow on the next item in the Items list that has not yet been processed.

2.4 Pausing for 1 minute allows the remaining items to be processed by the looping workflow sequence. After pausing for a minute, the "Processed" field in the current item is set to Yes/true to indicate that the item has been processed. This will also prevent further instances of the workflow initiating unless by another (new) instance of the entire looping workflow sequence.


Related Articles:
How to wait for a change in any list, wait for multiple field changes in the current item (SharePoint Workflow)
Test if an item exists in a list with certain criteria, continue workflow if not and create a new item in the list with the specified criteria

(Looping) Timer Workflows Using SharePoint
How to create a looping timer workflow (send a daily notification/reminder until a specific condition is met)


How to configure a workflow to start at specific time, then loop / repeat periodically (Daily, Weekly)The following method can be used to start a workflow at a specific time, then repeat periodically (each day). Periodic Workflow Processing: (send daily email notification or reminder)A useful implementation of this technique allows you to configure workflows to run as background tasks at a specific time instead of being triggered by a user manually, or after creating or modifying a list item.

SharePoint Workflow Errors: Descriptions, resolutions and WorkaroundsThe following table lists some of the common errors that can occur in a Workflow developed using SharePoint Designer. A short description of some of the likely causes for each error are also provided.

SPD Workflow - Test if value exists in any item in SharePoint list - create new item if not found
SharePoint Designer Workflow: How to test if a specific value exists in any item in a list without the workflow stopping due to a "List item not found" error if no items exist with the required value.
Share this article:
Stumble This Delicious
Delicious
submit to reddit
Facebook
MySpace
MySpace

17 comments:

Unknown said...

I tried this and worked great but after 5 iteration sharepoint stopped workflow. Is there a configuration I'm missing. Any ideas?

DanielS said...

What is the workflow status of the final instances of the control looping workflow? Was it able to find the next list item to process, as it will stop with an error if the list item isn't found (also how the loop is broken once all items are processed).

Check the workflow completion and error reports of the items being processed to see how many instances of the workflow are being executed, and if there are any that are getting stuck in a loop.

If the processor workflow updates values in the current item, it may be initiating new instances of the workflow on the same item. If so, you may need an additional field (on the items list) to flag when an item processor workflow is in progress. In the Item Processor Workflow, you would need to set the field ie. "processing" to True/Yes before making any change to the current item. In the fist step, a condition would need to be added to stop the workflow if another is is progress on the current item (Stop if processed equals yes)

William said...

Hi DanielS - I get exactly the same thing happen to me.

We are using SharePoint 2010 is this solution for 2007 only? I wonder if they have added something to workflows to stop them getting into a loop of more than 5 times or something?

The new item that's in the control looping workflow says that the workflow has not started (The workflow that's set to run when a new item is created in the list).

This has obviously worked previously 5 times and then decided to do nothing!

Weird...

Rich

Laurent said...

Same issue here. Only 5 items' workflow get started. any idea?

Laurent

Anonymous said...

can anyone break this down anymore for me, I just don't undertand what's going on here at all.

Manuel said...

I have the same issue in SP 201O only 5 times to looping the workflow, somebody know how to increase?

Thanks,

DanielS said...

Hi Manuel,

This article was written for MOSS 2007 so the issue may be differences in the workflow processes between SP 2010 and MOSS.

I will implement for 2010 myself and attempt to replicate and resolve the issue. There may also be better ways to achieve this using SP2010 which I will investigate also.

Thanks

Laurent said...

Hi all,

Although I recently used the workflows for a slightly different application (change an item's status based on current date), I found out that my workflow would only run 5 times, after which it would stop, showing a "completed" workflow status. I had to set 2 identical workflows. One workflow would trigger the second one, the second one would trigger the first one and so on. So I guess we need the same in here: 2 workflow controls.

DanielS said...

Thanks Laurent,

I still haven't had a chance to test in SharePoint 2010, but from your comment I sounds like similar workflow functionality is available in 2010 to MOSS with SP2.

After SP2 is installed in MOSS, an "on change" workflow running would no longer trigger a new instance of itself if the "current item" is updated. The workaround for MOSS was to use a second list/item that triggers each iteration of the loop, which is similar to what you have described above.

The solution in this article was supposed to cater for both environments using the two separate lists, so the same concept should work in 2010, but may be slightly different from the MOSS equivalent.

Thanks again.

Laurent said...

Hi DanielS,

I should have mentioned, the comments above refer to MOSS 2007. Can't remember which SP though

Unknown said...

A problem with this is that if you add the Processed column to an existing list SharePoint will not backfill existing values (they will be null) so the parent workflow falls over at 1.1 - there's no way of searching for null values or of setting all the Processed fields to an initial value!

Traveler said...

@ Aaron - You can autofill or fill down on a list in the datasheet view. So when you add the new colomn, select yes on the first item (in datasheet view) then right-click on the column and select "Fill Down." Of course it's best to do this before adding the workflow.

Todd Wilder said...

I have 5 years of experience in SP customizations / workflows and I needed to develop a daily workflow. None of these "workflows calling workflows" methods work for long periods of time or after a catastrophe. The only really enterprise way to do this is to use Information Management Policy with Expiration and a mirror list where items are deleted and maybe recreated every day.

DanielS said...

Thanks Todd,

This solution uses a "mirror" type list as you have described. This "Workflow Control List" is where the items are created and deleted to create the repeating workflow (not a Workflow calling a Workflow). The workflow control list is separate to the list that contains items that are being processed. In this case the loop works through and updates a field in each items in a list, which instantiates a separate workflow on each item to complete the processing. Creating a workflow using SPD to run daily would be more like this post (also referenced above).

The solution here is one method to create a loop using workflows put together in SharePoint Designer, which I would describe as a type of workaround that caters for a lack of OOB functionality to achieve using SPD.

This solution can be useful when Workflow extensions for SPD that would assist with loops are not installed, or if other tools such as Visual Studio are not available.

In my opinion, most Workflows created using SharePoint Designer are not appropriate for enterprise solutions that require high availability. I would suggest completing daily processing such as this using a custom timer job that executes the workflow daily, or by eliminating the workflow and have the timer job complete the processing directly.

Jayvardhan Patil (Jay) said...

Hello Daniel,

The solution provided by you is really good. The only tool I use to develop sites for my clients is sharepoint designer as at max I can get only site collection admin rights.

But the beauty of this approach is that you need to find a really smart work arounds for getting things done.

DanielS said...

Thanks Jayvardhan,

I too use SharePoint Designer for the majority of workflows that I develop as most are very achievable using the functionality available. It is only when the process and is highly custom and complex that I tend to develop using other tools as it makes it easier to build the workflow without relying on lengthy workarounds. It really depends on the requirements of a workflow as well as the number of instances that are likely to be running, in my opinion.
Even with the limitations, SharePoint Designer Workflows are great because most workflows are not highly complex and custom, and are not used by large numbers of users. Smaller scale SPD workflows would be easier to change in the future if required which is another plus.

In the scenarios where the workflow process was very long and complex, I may break the process into a series of (SPD) workflows that work together. This is because it is easier to have a workflow start midway through the process and repeat a component if required. When all steps of the process are in a single SPD workflow this is not as easy to achieve. Even when breaking the process down into separate workflows, there have been cases where these component workflows were still complex as well. If I foresee this issue when designing the process, I may choose to implement the workflow using Visual Studio, or even application pages, event handlers and/or timer jobs instead. In my experience, developing the process in this way is more likely to work consistently once in use, and is much easier to build to exact specifications without having to rely on workarounds. Workaround solutions generally include something additional that can possible go wrong with a complex process.

Avoiding SPD can also help if there are likely to be many hundreds or possibly thousands of workflow instances running at any one time, especially if there would be multiple workflows created as part of a larger process as they sometimes don't work as expected when the server is under a lot of pressure.

Another thing that I do in most cases is use separate lists to store data used to control a workflow process so that it can be adjusted/configured at a later stage without having to modify the workflow itself. This is applicable to workflows regardless of if SPD is used to develop or not. Specific users can then be given access to these lists to give them some control over the workflow without needing skills in SharePoint Designer or Visual Studio.

Kevin said...

Has anyone got this type of solution to work with SPD 2010 on SharePoint 2010?

It stops after 5 iterations.
I have tried the solution by SharePoint Guru's site but it also stops at 5 iterations (4 if you use their loop count).

This seems like such a basic necessity I don't know why they wouldn't include it.

Bamboo and Nintex have solutions but this needs to be done with SPD 2010 on SP 2010 without a 3rd party purchase.

anyone?

Post a Comment