Looping Through Items in a SharePoint List with SharePoint Designer Workflows !

Looping Through Items in a SharePoint List with SharePoint Designer Workflows

Update: June, 2009: I first wrote this article in May of 2008. At the time it seemed like the best way for an Information Worker to be able to do some looping through items in a SharePoint list without the need for Visual Studio development. As many people have pointed out, it is difficult to implement and because of the back-and-forth nature of multiple workflows running and updating things to start other things to update things and so on and so on, there is considerable opportunity for failure. With this in mind, our Workflow Essentials product adds a new activity to your SharePoint Designer workflows called Loop through List Items. If you need to do loops in your SharePoint Designer workflows, I highly encourage you to Watch a video of the Loop Through List Items activity in action, learn more about the more than two dozen activities and conditions it adds, and then download and try it for free yourself.

One of the things that most frustrates me about SharePoint Designer workflows is that there is no convenient way to write loops. Not only can we not create For…Next, For…Each, Do…While, and Do…Until loops within our workflows, but there is no obvious way to loop through items in a list.

The Scenario

For example, assume you have a document library with 1,000 documents in it. You have just written a workflow with SharePoint Designer to e-mail the document creator and have him or her enter certain pieces of metadata. This will work fine for new documents going forward, but what about the 1,000 documents that already exist? You certainly don't want to have to start all those workflows manually!

The Solution

You can accomplish this automatically by setting the workflow on the library to run whenever an item is changed. Then write another workflow on another list to cycle through all the documents in this Document Library and set a processed flag to both indicate which ones have been processed.

Getting the Library Ready

First, you need some way in the document library to identify which items have been processed—that is, on which items your workflow has already initiated the workflow. You can do this by adding a custom column named "Processed", of type Yes/No (checkbox), with the default value set to "No".

Second, you'll need to modify your workflow to run when an item in the library changes. If you don't want the workflow to run every time an item changes, but only when a new item is created, you may want to check your new Processed flag at the beginning of your workflow and have your workflow set it to "Yes" after it runs.

Writing the Loop Controller

You will need another list on which the Loop Control workflow will run. I prefer to use a custom list named "Loop Controller". I also add a field to track the ID of the last processed item. This column serves two purposes: we can change this value to re-run the workflow and it also serves as a gauge to show how far along the loop controller is in its process. This field is just a number field and you can name it "Last Updated Item" with a default value of "0".

Now you are ready to write a workflow on the Loop Controller. It should be set to run manually and when an item changes.

The workflow will not have any conditions, but will have several actions together:

1. Look at the Document Library and get the ID for the first item that does not have its Processed value set to Yes. Store the ID for this item in a workflow variable named CurrentItemID.
   
2. Change the value of the Processed field for the item in Document Library with the ID equal to the CurrentItemID. This change will cause the workflow on the Document Library to run for that item because you wrote the workflow to run when an item changed.
   
3. Update the value in the Last Updated Item column on the Loop Controller list item. The workflow will stop after this action. However, because the item changed, and the workflow is set to start when an item changes, it will kick-off a new instance of the workflow. Through this stopping, restarting, and setting the Processed flags, you have created a workflow that will loop through the items in the Document Library.
   

Your completed workflow on the Loop Controller will look something like this.

Once you have finished writing the workflow on the Loop Controller list, you just need to add a new item and manually start the workflow.

If everything worked properly, then you can refresh the view of the custom list and watch the value in the Last Updated Column increase as it loops through the items.

You will notice that once the workflow can no longer locate an item in the Document Library with the "Processed" field equal to "No", the workflow will error out and stop. That's OK, because you want it to stop, otherwise you would have an infinite loop.

If you look at the history of the Loop Controller workflow, you will see where it ran once for each document in the Document Library.

If you look at the Document Library, you can see the workflow for each item was initiated when each item's "Processed" column was updated to "Yes".

When you are finished using the Loop Controller you can just delete the list and workflow.

Looping Inside a Workflow

Using this same technique of changing a value on an item, you can create workflows that run multiple times. You can even set a value in a column to act as a counter to control the number of times it runs. In some cases, by using conditions, you can simulate simple loops within a SharePoint Designer workflow.

Enjoy!

Update: June, 2009: I first wrote this article in May of 2008. At the time it seemed like the best way for an Information Worker to be able to do some looping through items in a SharePoint list without the need for Visual Studio development. As many people have pointed out, it is difficult to implement and because of the back-and-forth nature of multiple workflows running and updating things to start other things to update things and so on and so on, there is considerable opportunity for failure. With this in mind, our Workflow Essentials product adds a new activity to your SharePoint Designer workflows called Loop through List Items. If you need to do loops in your SharePoint Designer workflows, I highly encourage you to Watch a video of the Loop Through List Items activity in action, learn more about the more than two dozen activities and conditions it adds, and then download and try it for free yourself.