SharePoint Development & Administration + InfoPath

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