Tuesday, May 06, 2008

Looping Through Items in a SharePoint List with SharePoint Designer Workflows


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!

49 comments:

Shridha said...

hi Ricky,

Great article. It seems to solve the looping problem with SPD.

It would be appreciated if you could explain the following point in little more detail.

1. In the Loop controller workflow, first action assigns a document ID to workflow variable CurrentItemID, what lookup condition have you used to get the document Id?

2. Also the last action in the Loop controller workflow is to update the value in the Last Updated Item column on the Loop Controller list item. what value should be assigned to it. In other words what lookup should be done?

Hope I am able to explain my problem. Please suggest.

Regards,
shridha

Ricky Spears said...

Shridha - For the first action, I used the "Set Workflow Variable" action. The value this sets to is the ID for the first item in the Document Library that does not have the "Processed" field set to yes.:
- Source: Document Library
- Field: Processed
- Field: Document Library:Processed
- Value: No
This returns the ID of the first docuement that has not yet been processed by the workflow.

The last action sets the Last Updated Item field in the current item to the ID you stored in the first action.

Good luck!

Shridha said...

Hey, thanks for that great tip !!

Mahesh said...

Great article...Thanks a lot Ricky..We were banging our heads on implementing this till we came across your article..

nickd23 said...

I am a newbie and trying to understand some of the aspects of the article. This is exactly the thing I have been looking for!

First question - Is this possible to use with updating items from a list (not within a document library)?

Second question - In your workflow step 1 you have your "set workflow variable" to variable:current item ID. Did you have to create a new variable? If so, can you let me know the steps of creating the variable, and also the steps of assigning your "set workflow variable" action to the "variable:CurrentItemID"?

Ricky Spears said...

Nickd23 - This should work on both lists and libraries.

After you add the "Set Workflow Variable" action to your workflow, you will see a blue hyperlink in the action that says, "workflow variable". Click that link and you will get a drop-down box showing all your current workflow variables and an option to "Create a new variable..." If you select the "Create a new variable" option, then you will see a new dialog box where you can create your new variable with the name you want. In the example here, you'll need to choose the data type for "List Item ID" too.

Good luck!

James said...

Hi Ricky

Really useful article - I've managed to get the loop working well.

I wonder if you can help me: How would I set this loop to re-run through all list items, say every few hours? I want to periodically run a workflow that checks through all itmes in a list for changes against items in another list.

Thanks

James

Ricky Spears said...

James - Off the top of my head, probably write another workflow to kick this one off periodically using a PAUSE action. It may also have to reset some parameters each time too. Good luck!

Andres said...

Hi Ricky, awesome post. I currently have a status list and I would like to create an email that sends a status report of what users have not completed a task. Is it possible to use the loop solution you mentioned to go though the list and capture specific status items in the contents of an email?

Thank you!
-Andres

Ricky Spears said...

Andres - That's an excellent idea. My first thought was to create a SharePoint list to store the information you want to be in the email. This list will only need one record and it will need a column that is of type Multiple Lines of Text. Add a step to the workflow to create a dynamic string that contains the information for each task that you want to show up in the email. Then create a new dynamic string that extracts the information currently in that Multi-Line Text field you created, apends the line you created just before that, then update that same Multi-line text field with the new combined string you just created.

That's how you can compile everything for the email using this looping method. The problem comes in that this workflow dies when it finishes so it can't send all that information out in an email.

It's another hack, but maybe you could also write another workflow that runs on the list that has tha Multi-line text field. It would run whenever an item is updated. The workflow could pause for 15-minutes at the very beginning, or maybe even much less--you'll need to play with this to see what works, it depends on how frequently the looper workflow runs. Anyway, it can compare the value in the multi-line text field and if it has been updated since the workflow began then it dies. If it hasn't been updated in 15 minutes it sends out the email you want and clears the text that is in the multi-line text field.

Great idea! Good luck!

Mike said...

This is an amazingly versatile solution to the looping problem. It really opens a lot of doors!

One thing I have changed in my implementation though, is that I have made the Loop Controller a 2 step workflow.
Step 1 stores the ID of the first document it finds that matches the criteria.
Step 2 then checks to see if the ID is greater than zero. Using the 'Else If' conditional branch, you can have one set of actions for ID > 0 and an else. This traps the final step when the workflow can't find another item to meet the criteria and allows the workflow to be stopped cleanly.

Again - excellent post

Ricky Spears said...

Mike - Excellent addition! Thanks for sharing!

samantha said...

In step 2, when I set the lookup detail to Workflow Data, my variable is not in the drop down. Any idea why?

Dave said...

Ricky,
I concur with everyone else, this is great blog item! I have been able to get it to work but also been playing with a loop that deletes every item in a list. I can get the loop to delete the first item only.

It is a simple WF that follows yours: capture the list ID, delete the list item and update the controller list. I have the manual and changed item switches on.

Any ideas on how to keep the loop going?

Thanks!!
Dave

Ricky Spears said...

Samantha - I've occasionally seen times that variables don't show up initially. I think it a rare, but occasional bug in SharePoint Designer. I've notice that if I will go ahead and compile the workflow by clicking Finish, and then re-open the workflow that the variable is always available. Good luck!

Steve V said...

Thanks for the nice article.

This resolved a fairly complex scenario for me:

I have a calendar, which users can register for on a seperate 'Registrants' list (lookup against calendar).

I wanted to have the registrants notified whenever an event is modified. I used your approach, but I had to create an additional workflow to 'reset' the processed flags back to NO when everything was done... so that it could be reused.

Sean said...

HELP Please. I think you can help me. I am using WSS 3.0.
I have the following custom Lists:
1. Test Case
2. Test Result
3. Test Iteration
I'd like to create a workflow so that when a new entry is created in Test Iteration list, the workflow would create an entry in the Test Result list for every Test Case. In other words, go to to the Test Case list, grab some fields, and create a new entry in the Test Result list.

Can this be done using the designer ? if not how ?
Thanx, Sean

Ricky Spears said...

Sean - Off the top of my head, I don't see any reason this wouldn't work using the methods I outline here. Good luck!

Joe said...

Hello Ricky,

thanks for your article, it is very helpful to me.
Additionally I have another problem with looping:
I have on main task list. When an item is created a workflow should create tasks in different sub task lists. When all sub tasks are completed, the workflow should set the main task to completed. Is it possible to to that with one workflow and a loop, if yes how? Or would you recommend to run workflows on all the sub task lists.

Your help is appreciated. Thank you very much!

kr

Joe

Ricky Spears said...

Joe - If I'm understanding your business case correctly, you don't need looping at all. In the block that assigns the tasks, just select the option to "Run all actions in parallel." Maybe there is something I'm missing, but I think what you want to do is assign multiple tasks at the same time. Good luck!

Narayana said...

Rick,

Thanks for great post,

I have one similar requirement related to looping.
I have one List created and in that, if the "Due Date" field is empty then I want to send an email for every(until due date is filled) 90Days with subject "Escalated Email" and for every 1 Week "Weekly Reminder Email".
Hope I explained clearly.

Please help me on this

Regards
Narayana

Ricky Spears said...

Narayana - It may be easier if you try the "Loop Through List Items" activity that is part of our Workflow Essentials product. For more information visit:
http://moss.sharepointsolutions.com/SharePoint-Add-ons/Products/Pages/WorkflowEssentials.aspx

You can download a free trial to see if it will meet your needs before you have to purchase it.

TZA said...

Hi Ricky,

I am a newbie and trying to understand your article. I have a question and hope you are able to answer it.

1. I have this one form that I created using InfoPath and then I publish it to SharePoint.

2. My work flow start when the form is submitted by a requester. Then someone will approve or reject the submitted form. If the form is approved, then the work flow will end.

3. But when the form is rejected, can the requester open the form and update it?

4. After finish updating, can the form be submitted again and trigger the work flow?

5. The point is so that the requester will not have to enter the same data again.

Hope you could understand my question.

Thanks and have a nice day.

Regards,
TZA

Alberto Escola said...

Hi Ricky,

A smart solution to solve the problem!

I've tried to implement it but I've a problem (I'm using MOSS 2007)

I created the "Processed" custom field in the original list with the default value set to "No", then I saw the existing items in the list, the field wasn't set to No, was set to "empty" so the first step in the loop workflow didn't work.

I've tried to set the search condition in the set variable action to "not Yes" but I don't know how to do it

How can I fix this problem?

Thanks in advance

EnoNomi said...

Awesome! This is just what I needed. Thanks!

Mani said...

Hi nicky,
I have similar kind of requirment.

I should check one condition.that condition true mean workflow will continue.condition false mean work flow move previous steps.....how can i kick off workflow steps....

Anonymous said...

Hi Ricky,

Thanks for taking the time to post this-you have obviously helped a great many people including myself.

One question: have you been able to make WF's loop on a SharePoint server with Service Pack 2 installed yet? I have been trying, and although it seems to work fine on pre-SP2 servers it does not seem to work with SP2.

BTW, I tried using the "...whenever an item is changed" start option, and when that wouldn't work I also tried the "start another workflow" action... that wouldn't work either. I cannot get it to loop with SP2.

Your help/comments are greatly appreciated.

Again, thanks from all of us in SharePoint land..........

Ben

Ricky Spears said...

Anonymous - I haven't tried it on a machine with SP2 installed yet. I've made a note to try it though. I don't know when that might be though. Perhaps others can chime in here with their experiences before I get to it.

Anonymous said...

I found another way:
-create 2 workflows in the same list, starting at any change
-in the first one, check a field e.g. wf1 is set to no, if true, set it to yes (and send mail, wait 30 days or whatever)
-in the second, check if wf1 set to yes, if true, set it to no (and do what you want)
In my case, each of both waits for 30 days, writes a mail telling users to check their documentaion.

Steve V said...

This won't work on SP2.

Microsoft decided to remove the ability for SPD workflow to trigger on change item if workflow changes itself. (Reportedly because customers were creating infinitie loops)...

So all of us will have to rework anything using this another way. I'm guess in Visual Studio - thought still trying to figure it out myself.

Bummer! :-(

Poolio said...

This is a great solution but for one problem - if you create the Processed column after your list is already populated with data, which it usually would be if you're bothering to write a loop workflow, then even if you set the default value to No, existing items will contain a null value in this column. This is impossible to run a workflow against as far as I can tell, I tried lots of different variations but could not make a workflow find the next empty column.

I went round in a lot of circles getting around this, as however you populate that field, you need a looping workflow. Argh!

The solution I found is this:

1) Create a Procesed column of the Number type. It doesn't matter what you set the default value to - in all the existing columns this will appear as empty.
2) Now create a second column called ProcessedPlusOne. This should be a calculated column, with the formula =[Processed]+1
3) Now when you look at your list, even though the Processed column appears empty, your ProcessedPlusOne column will be set to 1 on all items. Now you have a value to loop against.

Hope that helps someone.

mrpush said...

Hello,

I cannot get this to work.

Reason: When setting the variable CurrentItemID, type "List Item ID" we are supposed to set it to Document Library: ‘Processed’ field as instructed below:

Variable Lookup:
- Source: Document Library
- Field: Processed
- Field: Document Library:Processed
- Value: No


The "processed" field is type ‘YES/NO’ and therefore does not show up as a selection in the lookup fields, only numeric fields show up.

How in the world are you guys getting this to work?

Also, are the "lookup that you defined is not guaranteed to reture single value" messages ok in this case?

Thanks,

Mark

Anonymous said...

Hi Ricky,

Good post. I tried creating three lists.

1. Test1
2. Test2
3. Loopcontroller

I have 2 columns in Test1 List called Approved(Yes/No) and Processed (Yes/No) and default is set to No.

Processed Field - is for the looping control

Approved Field - If it is set to Yes, then it should copy the list from Test1 to Test2.

Once the LoopControl Workflow is triggered manually, the Processed Field doesn't change its default value. The history of the workflow says that it is completed, but it doesn't loop through and the LastUpdatedItem in the LoopController list is 0.

I don't know where I am missing the flow. Please advice me for I need to make this work for my process.

Below is the workflow:

Condition: If Test1: Processed equals No

Action: Set Variable: CurrentItemID to Test1:ID
then Update item in Test1 (Processed is set to Yes)
then Update item in LoopController (LastUpdateItem is set to Test1:ID
then Copy item in Test1 to Test2

Defry said...

Hi Ricky,

I was execute action plan as your mention but not working. Update list item can be done for only first record. Other record cannot be update.
Please help me.

Regards,
Defry

Marcus Holmer said...

Defry,

I have the same problem and I think SP2 is the cause...

Try update the Item list/library instead of the LoopController, then update LoopControl after you made your action in the list/library workflow.

MrPush, there´s a typo in the lookup, you have to look for the ID to set:

Variable Lookup:
- Source: Document Library
- Field: ID
- Field: Document Library:Processed
- Value: No

Regards,
Marcus

Pradeep said...

Hello Ricky,

I have a list and a workflow associated to it. I need to keep sending emails until an item is marked 'Closed'. To start the workflow, I have created an action to update the field in the list item, after pausing for one hour. The field is updated but the workflow does not start all over again. Do you have any idea to help me out.?


Thanks
Pradeep

Ricky Spears said...

Pradeep - You'll have to use a secondary workflow. Currently, if a workflow makes a change to an item, it won't trigger another instance of itself--however, it will trigger any other workflows that are set to run when the item changes.

So, the simple thing may be to just write two identical workflows and have them both run on change. Good luck!

Laurent said...

Hi Ricky,

I hope you can help me like you helped the others.
I am trying to have a workflow run and check against an item field which has a date in it. Once that date has passed, the status should change to "Closed". I have tried with 1 workflow, then 2 workflows running (as per above comment), checking the date, waiting 1 min (for testing) and then updating the item's title with a workflow variable which gets the item's title prior.
Issue is, my worlflows only run for about 5 min then stop with status "Completed".
Any idea?
PS. it's sharepoint 2007 SP1.

Laurent said...

Hi Ricky,

Never mind I think it's working now. The odd thing is the first workflow will run about 5 min (5 times because of the one min pause?) before completing and triggering the 2nd workflow, and so on.
Thanks for your article anyway, really helpful

smillerkyd said...

How could this have worked?? number one - setting the variable to a value will not work as the lookup parameters (specified in article) potentially returns multiple items from the documents list. Therefore trying to set the LastUpdatedItem field with the CurrentItedID variable is not possible because you don't have that option to select the variable from the workflow data lookup option?

Ricky Spears said...

smillerkyd - When multiple items are returned, SharePoint Designer just uses the first one it encounters.

Chinh said...

For those who don't see the CurrentItemID in the Workflow variable list, you may have declared it as 'Number' type instead of 'List Item ID' type. So the solution is:

In the Workflow variable list, change the type from 'Number' to 'List Item ID'.

abhishek Kumar said...

thanks a lot Ricky...for this blog...I was searching for exact solution for duplicate booking issue using workflow only.this has truly helped me to reach on correct solution.

Reid said...

Note I have completed this with SP 2010 and you need to have a condition to get it to upload / publish properly. Just to get the workflow to complete I inserted a condition where the created date was between a date and 'Today'. Although, when thinking of ways to get the workflow to re-run I have switch this to the if title field contains... condition.

I have gotten this work when the items are all "new" and don't have a workflow state (when all items are processed they have a 'Completed' state). What if I want to re-run this workflow in the future? Now all of a sudden all the processed flags are set to 'Yes' and when I edit an item the workflow completes but nothing happens.

I have made an else if branch of the workflow (they both are if title field contains... conditions), and on top of changing the processed flag I set the title field to whatever value the processed flag equals so the if statements can work properly. I selected the condition "IF title field contains 'Yes'" and then an else if on the "IF title field contains 'No'" Each set of actions following are spot on with how this article details the workflow, with the exception where I also set the title field to the processed flag value. How can I systematically get it to re-run? From what I've read on the web the workflow will continue to process each record until it sees an unprocessed record or errors out. In the case here all records are processed from the original run and so it stops once the only record has been changed initiating the workflow.

Arthur said...

I have 2 workflow setup.

Once in a while on random list items the workflows add endless number of completed workflow records, and the site get flooded.

How can I fix this or remove the completed workflows

I would appreciate any feedback.
Thank you.

Arthur said...

how can I get rid of the endless number of completed workflow items...this happens randomly in my list.
The completed workflow items keep going to more then 1000 records.

If I can't permanetly fix this, can I remove the completed workflow items so when I click on workflow it won't take 10 min for it to open.

khetienne said...

Hi
I think I understand what you are doing, but my problem is I have an existing list. Adding the Processed column with a default of "No" ( yes/no checkbox) does not update the existing entries

What I was thinking was of looking to find Id that are noy "Yes" - but I dont know how (being a sharepoint newbie)

amit rao said...

For those who are facing issue with SP2 .

Try adding the same workflow two times.

This works for me ,.. !!!!!!!!!!!1

Brian Stamper said...

There is also the idea of using a "cycle" column in the original list and changing the value of the "cycle" in datasheet view.
A little Excel trick of highlighting all of the "cycle" cells down to the bottom of the list and CTRL+D copies the top cell all the way down.
As long as you have an update workflow that runs on any item change, the datasheet allows you to change every "cycle" value quickly and easily. I even created a private Utility view specifically for this purpose.