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. This allows processing of data from SharePoint to take place during times when the SharePoint environment is not being used as well as performing tasks that take a long time to complete.

For example, this method can be used to configure a looping timer workflow to run at COB each day (5:00pm) to perform calculations and processing on a list item before emailing a reminder, or details to a user associated with the list item or document. When initiated for the first time, the workflow waits until the specified time (5:00PM) before executing workflow actions. After the workflow is complete, a secondary list item is modified, which in tern initiates a new instance of the workflow. As the initial time (and date) that workflow waited until to start would have passed by the time the workflow is started the second time, the workflow waits for a specified period of time instead (eg. 1 day) before completing the workflow actions. The result is a looping timer workflow that starts at the time specified, then repeats on a periodic basis (every 24 hours / daily) indefinitely, or until certain criteria is met.

As the repeating sequence only needs to be initiated once, this method can be used to build workflows to complete background processing to help minimise the impact on users, as well as improve usability and functionality of web applications created using components of SharePoint. See below for an example of a Looping Timer workflow used to complete processing as a background task at a specific time each day.

Looping Timer Workflow for Background Processing

When a workflow takes time to process but doesn't require user input or intervention, it is possible to configure the workflow to run as a background process to eliminate the need for users to initiate the workflow each time it is required, as well as allowing complex and timely processing to be completed "behind the scenes". As these workflows may take a long time to complete forcing a user to wait for the workflow to complete after manually initiating, or modifying / adding an list item to a list to initiate the workflow can result in frustration if there are long delays. Configuring workflows such as this to repeat periodically allows the workflow to start and finish in it's own time, eliminating the need for SharePoint users to initiate and control the workflow. Other workflows initiated by users can then refer to the data generated and stored by the looping workflow allowing them to complete in a short amount of time as no complex or in-depth processing is required to complete the operation.

The looping workflow sequence is initiated once, then repeats indefinitely or until a specified date if required. The workflow will complete the operations required for each iteration, then pause until the operation is to be executed next. This will mean that the workflow will continue to complete the processing without a user needing to manually initiate a new instance of the workflow. A common implementation of this technique may be to send a daily or weekly email report or reminder with details of a list item or data from many lists and items similar to the example above. Other scenarios where this can be utilised are times when workflows are used to process many list items, as well as perform calculations and other operations on the list items that require time to process. With scenarios such as this, configuring a looping timer workflow to automatically initiate and complete the processing at a specific time each day (or hour) eliminates the need for a user to have to initiate the workflow, as well as allowing the workflow to complete seamlessly to users in the background.

Example: Daily Sales Report Workflow using SharePoint

Calculate sales totals for the current day's invoices. Store the data in a separate "Reports" list item for the day.

A looping workflow may be configured to loop through each item in a SharePoint list (Invoices), then extract data from a specific field from each list item which is the added (concatenated) to a field in a secondary list item (Invoice Reports) for other workflows to refer to, or later use by SharePoint users. The workflow would process all Invoices which have not yet been processed, which will be any from the current day, as invoices from previous days will already have been processed.

A looping timer workflow can be configured to run at a specific time, then repeat every 24 hours to initiate the workflow(s) that loop through all items in a SharePoint list. The looping timer workflow will initiate the looping workflow to process invoices each day. A looping workflow can sometimes take a while to complete if there are a large number of items in the list. Configuring the workflow to be initiated by a user may not be appropriate as the delay may reduce the effectiveness of the workflow and increase user frustration. Configuring a timer workflow to initiate the workflow loop allows the processing (of all items in a list) to complete in isolation to workflows initiated by users that refer to the data generated when processing all list items.

Depending on how often the data changes and how important it is that the data is up to date, you may need to configure the looping timer workflow to initiate the workflow to process all items at shorter intervals (eg. hourly, every 10 minutes, etc.). As long as the list containing the items being processed is separate to the list item that stores the data from all items, it would also be possible to initiate the workflow loop to process all items when any of the items in the list are updated or added. If configured this way, an "On Change" and "On Create" workflow would initiate after a list item is added or modified, which would then make a change to the secondary list item to initiate the workflow loop sequence. The loop sequence would then process each item in the list until complete.

See below for a breakdown of a Background Processing Workflow that repeats daily using the above example.

Looping Processor / Looping Timer Workflow Components (Daily Sales Report):

SharePoint List: Invoices

- Invoice 1

- Invoice 2

- Invoice 3

The "Invoices" list is the list which contains the items to be processed. Each item / "Invoice" ion the list is processed to calculate daily sales totals for each staff member.

Invoice Reports

- Report xxx (20-01-10)

- Report xxx (21-01-10)

- Report xxx (22-01-10)

This "Invoice Reports" list contains one list item for each day. The report data from each invoice item for the current day will be added to an Invoice Report item in this list once all have been processed to calculate sales totals.

Workflow Control List

- Temporary items used to initiate and control workflows and the looping functionality.

Workflows:

- Invoice Processing Workflow

- Initiate Invoice Processing Workflow

- Invoice Processing Timer Workflow

Looping Timer and Background Processing Workflow Diagram:
(Click to enlarge)

Workflow Details:

1. Invoice Processing Timer Workflow

This workflow will commence at a specified time (eg. 6:00 PM), then repeat every 24 hours (daily) after the first instance has completed. This looping timer workflow will be used to initiate the "Initiate Invoice Processing" (Looping Timer Workflow) at the end of each day by creating an item in the Workflow Control list with the title "Start Report Loop". This "Invoice Processor Timer Workflow" workflow will only need to be initiated once, as it is configured to automatically trigger a new instance of the workflow each day. When configured using SharePoint Designer, one of the first steps in the workflow would be to test if the current time is before or after the start date/time for the workflow (specified in a "start_date" field in the list item).

2. Initiate Invoice Processing Workflow: (Looping Timer Workflow)

The purpose of this workflow is to initiate an instance of the "Invoice Processor Workflow" on each item in the list being processed. The workflow will initiate when an item is added to the Workflow Control list with a specific title (eg. "Process Next Invoice"). The workflow will find the first unprocessed item available list item from the Invoices list, and update a field which indicates that it is being processed ("Processed" = no/false). Updating the field will initiate the On Change workflow (Invoice Processor Workflow) to process the updated item.

3. Invoice Processing Workflow

Process an item from the Invoice List. A separate instance of this workflow will be initiated for each item in the list each time the looping workflow sequence is executed. Updates the item from the invoice Reports list with data from each Invoice (daily totals: sales, turnover, profit, sale type, compute staff rank based on total profit, etc.).

Workflow Control List & Workflow Triggers

The workflows associated with the Workflow Control List Invoice Processing Timer (1), Initiate Invoice Processing (2) ) are should be configured to start when a new item is added to the list. The first step of the Invoice Processing Timer (1) workflow will be to stop the workflow if the Title of the current item (item added to the list) is not equal to "Start Report Loop". This will mean that other items added to the workflow control list with a Title that isn't "Start Report Loop", won't initiate the Invoice Processing Timer (1) workflow unnecessarily. The Initiate Invoice Processing (2) workflow should have a similar configuration for the first step: Stop if the Title of the current item is not "Process Next Invoice". This will mean that only items with this title will continue into the remain workflow steps.

The Invoice list will need a column/filed to flag if the Invoice has been processed or not. This could be a hidden boolean (true/false) field "Processed" or similar, which would be set to No/false as default when an Invoice is added to the list. The "Processed" field would be used by the Initiate Invoice Processing (2) workflow to find the next Invoice item to be processed. The unprocessed Invoice item found should then be updated by setting "Processed" to yes/true, which will then initiate the Invoice Processor (3) workflow for that Invoice. The final step of the Invoice Processing (2) workflow after initiating the next Invoice Processor workflow in the sequence can be to remove the current item ("Process Next invoice"), as t is no longer required. The final steps of the Invoice Processor (3) workflow should be to create a new item in the Workflow Control list with the title "Process Next Invoice", which will re-initiate the Initiate Invoice Processing (2) workflow creating the looping functionality, then set "Processed" to yes/true.

As the workflow is scheduled to run every 24 hours, items in the Invoices list from previous days will already have been processed by previous instances of the workflows. Only invoices added to the Invoice list since the last time the Report workflows were run, which will be any from the current day.

Related:

SharePoint Looping Workflow - How to: Loop through and process all items in a list
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.

How to get an SPD Workflow to Run at a Specific Time – Employee Vacation Reminder: Part 5

(Looping) Timer Workflows Using SharePoint
Looping timer workflow (eg. send a daily notification/reminder until a specific condition is met.


How to wait for a change in any list, wait for multiple field changes in the current item (SharePoint Workflow)
This article describes techniques which can be used when developing workflows in SharePoint using SharePoint Designer. They allow a workflow to wait for a field change in an item in another list, or to wait for multiple field changes in the current item before continuing.

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.


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.

SharePoint Designer: Get account details of the user who initiated the workflow using OOB Workflow Activities

Get account name of the user who initiated / triggered a workflow using OOB SharePoint Designer Activities:
The ability to access the account details of the user who started a workflow in Sharepoint is not supported when developing workflow using SharePoint designer and OOB workflow activities. The User data type allows selection of the User who created the item, but not the user who initiated the workflow. Using the Modified By field will return the user who made the most recent changes to the document/list item or meta data, but may not be the user who initiated the workflow. For example, if a workflow is used to set the approval status of a list item or document with content approval enabled is initiated, the user who published a major version of the documnent (submit for approval) will be the user who initiated the workflow regardless of who made the most recent changes to the document.

A number of solutions relating to this issue / limitation are available, including a workaround solution using OOB workflow activities and SharePoint designer explained below:

How to get the account name of the user who initiated a workflow using OOB workflow Activities and SharePoint designer:

1. Create/modify item in secondary list.
This action will be executed using the credentials of the user who initiated the current workflow. Once the list item from the secondary list has been created or updated, you can then retrieve the Creaded By or Modified By user.

2. Pause, then store the user who created/modified the secondary list item in a Workflow variable (string: "DOMAIN\user").
After the secondary list item has been created or modified, you can store the value of either the Created By or Modified By fields of the secondary item to get the account name of the user who initiated the current workflow. Note that you may need to configure the workflow to pause for 1 minute after creating or modifying the secondary list item, as it takes a few seconds for the account details to become available after creating or modifying the item.

3. Use the value of the workflow variable.
Once you have stored the user who started the workflow in the workflow variable, you can then use the value when setting the Assigned To, email recipient or any other field that uses the user account data type.


Related:

In sharepoint designer’s workflow editor how do I get the workflow initiators username?
"In Sharepoint designer's workflow editor I wish to retrieve the username/name of the work flow initiator (i.e. who kicked it off or triggered the workflow) - this is relatively easy to do using 3rd party products such as Nintex Workflow 2007 (where I would use something like {Common:Initiator}) - but I can't seem to find any way out of the box to do this using share point designer and MOSS 2007."

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

SharePoint Forums, Blogs and Online Communities

SharePoint Forums

The following page provides a comprehensive list of websites hosting popular forums relating to SharePoint. Learn how to customise, develop and administer SharePoint environments from industry experts by browsing through existing threads, or by posting a new thread if you have a question which hasn't been answered in one of the forums. All of the forums listed are free to join and contribute.

Forums relate to WSS, MOSS 2007 and SharePoint 2010, and include the official Microsoft product and Technologies and other forums such as the SharePoint Development & Programming, SharePoint Setup and Administration, SharePoint Design and customisation.

SharePoint Blogs

The SharePoint Blogs listed on the page are predominantly SharePoint MVPs, with years of experience, and many useful articles, tips and tutorials to help when customising, developing and administering SharePoint.

SharePoint Groups

There are many groups (newsgroups) available that contain information, and references to other useful information and resources that can help when customising, developing or administering SharePoint. The groups provided on the following page are moderated to ensure that inappropriate or illegal content, including spam is not added to the groups.