Search This Blog

Thursday, November 3, 2011

Create a For Each Loop for Workflows Using SharePoint Designer 2010

(The technique described below is for SharePoint 2010, if you are using SharePoint Designer 2007 check out my other post SharePoint Designer Workflows 2 – Developing a For Each Loop for Workflows ).

1. Introduction: Many times when designing workflows there is a need to use a “For Each Loop”. As you may know a For Each Loop will continue to process an action against a list of items until all items in the list have been processed. While present in all programming languages whether declared, script or object oriented, the For Each Loop is not present in SharePoint Designer 2010 workflows, or the other GUI driven workflow tools I know. This article will help you build a For Each Loop for your SharePoint Designer workflow.

2. Scenario: In our simple scenario we want our workflow to run through each of several items in a list and to update each of the items Boolean “Processed” field from No to Yes (For Each list item update Processed field to Yes).

3. Explanation: Essentially we will have three workflows, one to create and delete a new item in the “Loop” list, and two others to update the Data list. The logic for the first workflow looks like this:

a. Create a new item in the Loop 1 list

b. Pause (allowing the Loop list workflow to run)

c. Update the current item's Loop count field

d. Delete the item created in Loop list

e. Log the actions taken

The logic for the second workflow looks like this:

a.  Calculate the Loop Count Value

b. Update the first item whose “Processed” field is set to No

c. Update the Current Item's Loop Count

d. Log actions

e. After 4 Loops Update the item in Loop 2 (thereby starting the third workflow)

f.  Reset the Current Item's Loop Count

The logic for the third workflow looks like this:

a.  Pause  (allowing the Loop 2 list workflow to finish)

b.  Update the first item whose “Processed” field is set to No

c.  Update Current Item Loop Count

d.  Log actions

4. Overview: To build a For Each Loop we will need three SharePoint Lists, the first list will contain our data, and the other two list will control the For Each Loop. For our example we will call the first list “Data”, the second list “Loop 1” and the third list "Loop 2". We will also need three SharePoint workflows, one, to create and delete items in the For Each Loop list, and two to update items in the Data list. To create the lists follow these steps:

a. From the SharePoint site create a custom list, call it “Data”

b. From the Data list add a custom Boolean column, call it “Processed”, set it’s default value to No and add it to the default view

c. Create 1 custom number field and call it "LoopCount"

d. From the Data list create 10 to 15 list items (this is sample content for our workflow)

e. From the SharePoint site create a second custom list, call it “Loop 1”

f. Create 1 custom number field and call it  "LoopCount"

g. From the Loop 1 list create 1 item, set the title fields value to  "For Each Loop 1", and set the LoopCount field's value to "0"

h. From the SharePoint site create a second custom list, call it “Loop 2”

i. Create 1 custom number field and call it "LoopCount"

j. From the Loop 2 list create 1 item, set the title fields value to "For Each Loop 2", and set the LoopCount field's value to "0"

5. To create the For Each Loop 1 workflow follow these steps:

a. From SharePoint Designer create a workflow that is associated with the Loop 1 list, call it “For Each Loop 1”

b. Set this workflow to start automatically when an item is created

c. Create 1 step for the workflow, call it “For Each Loop”

d. Create a condition – If Created By is not empty – this means the workflow will always run

e. Create a second condition – If  Current Item LoopCount is less than or equal to 3 – this will ensure the first set of actions runs only for the first 4 loops

f.  Create an action  – Calculate Current Item LoopCount plus 1 – this will increment the Loop Counter

g. Add an Update List Item activity, configure the activity as shown below:




h. Add a Update Current Item activity, configure it as shown below:


i. Add a Log to History List activity, configure the log to write “For Each Loop – Ran”

j.  Add an Else condition

k.  Add a Pause for 2 minutes – this will ensure the first set of triggered "Data" workflows has completed

l. Add a Update Item activity, configure it as shown below:




















m.  Add a Update Item activity, configure it to update the original item in the Loop 1 as shown below:




















n.  The complete workflow configuration should appear as below:















6.   To create the For Each Loop 2 workflow follow these steps:

a. From SharePoint Designer create a workflow that is associated with the Loop 2 list, call it “For Each Loop 2”

b. Set this workflow to start automatically when an item is changed

c. Create 1 step for the workflow, call it “For Each Loop”

d. Create a condition – If Created By is not empty – this means the workflow will always run

e. Add a Pause for 2 minutes – this will ensure the first set of triggered "Data" workflows has completed

f.  Add an Update List Item activity, configure the activity as shown below:




















g.  Add a Update Item activity, configure it to update the original item in the Loop 1 as shown below:




















h. Add a Log to History List activity, configure the log to write “For Each Loop – Ran”

i.  The complete workflow configuration should appear as below:









7. To create the Process Data workflow follow these steps:

a.  From SharePoint Designer create a workflow that is associated with the Data list and call it “Process Data”.

b. Set this workflow to start automatically when a item is changed

c. Create 1 step for the workflow, call it “Process Data”

d. Create a condition – If Created By is not empty

e. Create a second condition and Processed not equals No – this means the workflow will not run if the Processed equals no

f. Add a Create Item activity, create a new item in the Loop list, title the new item “New Loop Item”, and output to variable “idLoopListItem” of the type list ID, the activity should appear as below:


g. Add a Pause for Duration activity, pause for 1 minute

h. Add a Update Item activity, update the current item in Data and change the value of Processed to No.

i. Add a Delete Item activity, and delete the item in the Loop list that has the same id as the idLoopListItem or the item you just created (by deleting the item we will keep this list from growing too large in the future). The configuration should appear as below:




















j. Add a Log to History List activity, configure the log to write “Processed – Ran”

k. The complete workflow configuration should appear as below:












8. Test the Workflows: To test the workflow we only need to start the For Each Loop workflow from our item in the Loop 1 List.

9. Error Messages: The last item created in the Loop list will have an error occurred in the workflow metadata field. This is because the last item to start cannot find any items that have not been processed. This error can be ignored; it’s part of the normal processing and will eventually be deleted by the Process Data workflow.

10.  I want to thank the many folks in the community who used the technique I described in my earlier blog SharePoint Designer Workflows 2 – Developing a For Each Loop for Workflows

11. Thanks: I want to acknowledge Ricky Spears for his article “Looping through Items in a SharePoint List with SharePoint Designer Workflows”, which provided the technique that I describe in this article.

55 comments:

Cam said...

Having some trouble. In item 5 (g), you are asking to update the list item in Data. I can select the Processed Field, but I did not create a LoopCount field in the Data list. Without having that item I can't seem to complete (g). I double checked the instructions in 4 and cannot find a place where you've asked me to create a LoopCount field in the Data list. What am I missing?

Thanks for all your help!

Tom Molskow said...

Hey Cam,

Sorry for the late reply, please check step 4(h):

Create 1 custom number field and call it "LoopCount"

Thanks!

Tom

Cam said...

Hi Tom. Sorry, I'm still having trouble. Doesn't 4(h) ask me to create the 'LoopCount' field in the 'Loop 2' list? The workflow that is supposed to update the 'Data' list doesn't have the 'LoopCount' field. How can I update 'LoopCount' in 'Data' when I haven't created that field in 'Data'?

I'm relatively new to Sharepoint so I apologize in advance if I'm missing something obvious.

Steve Brown said...

Hey Tom,

Thanks so much for providing this detailed description.

I am running into an issues were it only processes the first four items (I'm assuming ID's 0-3) and then stalling. Both the For Each Loop 1 and Process Data workflows are hanging on the "Pausing for 1 (and 2) minute[s]".

Any idea why this would happen and how to get by this?

Tom Molskow said...

Hey Steve,

Try reducing the number of loops and the pause times until it begins to work, and then slowly increase both until you can identify the break point. My theory is the workflows may be out of sync with each other and so you have to try "tuning" them.

Thanks!

Tom

Tom Molskow said...

Hey Cam,

Sorry for the late reply! You are correct, I have added step 4(c) Create 1 custom number field and call it "LoopCount" - my bad, I forgot that step! Please add this field and try again.

Steve - you may want to double check this addition as well.

Thanks!

Tom

Cam said...

Hi Todd. The workflow worked perfectly now. Thanks for updating with the fix!

Do you have any tips for having this looping workflow run on a timer, say every 24 or 48 hours?

Thanks,

Cam

Cam said...

Oops, sorry about calling you Todd. Thanks Tom!

Cam

Tom Molskow said...

No problem Cam!

Tom

Cam said...

Tom, how can I edit this workflow to run at a set time every night?

Tom Molskow said...

Hey Cam,

You can trigger the For Each Loop workflow using my Daily Timer workflow located here -

http://sharepointgypsy.blogspot.com/2011/01/sharepoint-designer-workflows-3.html

To make the Timer Loop workflow run at midnight essentially follow these steps:

- Set the long pause workflow to 23 hours and 45 minutes
- Set the short pause workflow to 8 hours
- Start the short pause workflow at 4:00 PM.

The short pause workflow will pause until midnight and then start the long pause workflow, the long pause will start and then pause for almost 24 hours.

- When you come to work the next day reset the short pause workflow to 15 minutes

Now the Timer Loop workflow will trigger the For-Each workflow once every 24 hours at midnight.

I hope that helps!

Tom

Cam said...

Excellent. I will try this over the Christmas holidays!

Thanks Tom

SharePoint Developers said...

your post is very interesting. I like it very much. It is very helpful and useful for me. Thanks for share this valuable post.

Kevin said...

I don't know what I am missing but this only goes through the first 4 items and stops and never continues

Additionally step H. doesn't make sense with the picture included.

I get the first 4 items "processed" moved to YES and their loop count updated in my "data" list but the they all switch back to NO before anything elese could even fire off.

Is there a new limitation? Using SPD 2010 on SP 2010 Foundation server

~AJ~ said...

Hi Tom, thanks for posting this! I have it working great except for 1 issue on the process data wf. I understand the 'error occurred' is ok for loop 1, however, I'm getting a few random errors actually on the main data list (of 1000 items). It won't run 'process data' on those items. I've been tweaking timing, amt of loops & numbering but still same result. Any ideas? Thanks, AJ

Tom Molskow said...

Hey Kevin,

I have a step h with a picture in 2 places - which one doesn't make sense?

Try adjusting your delay time, it may be that the time is too short and so the workflow is looping back onto itself.

This does work with SPD 2010 on SP 2010 Foundation server so it's probably just the delay time which can vary based on your server's responsiveness.

I hope that helps!

Tom

Tom Molskow said...

Hey AJ,

Can you provide me anymore information about those particular files and their environment - library or list, item level security, file type, check in/out, etc?

Thanks!

Tom

~AJ~ said...

Hi Tom,
I solved it by adding the Process Data ID number to the "new item create" title in Loop 1. Works great! However, I'm stuck again. How do I get this list to reset processed back to "no" to be able to run thru it every 24 hours? I saw the timer option but I couldn't get it to work for my situation. I did try to create additional workflows and then tried adding to the existing ones but both got caught in a viscious loop of setting and then resetting even when setting pauses. Any ideas? Thanks SO much!!! ~AJ

Tom Molskow said...

Hey AJ,

Can you provide any additional information about why the Loop Timer Workflow did not work for you?

Aside from the Loop Timer Workflow another option is to create an Information Management Policy in the list where your workflow runs. In that policy enable a Retention Plan. Configure the Retention Plan as follows:

Activate: 1+ Created Date
Action: Start a Workflow
Workflow: Your Workflow Name
Recurrence Period: 1 Day

This cycle will repeat everyday running your workflow.

I hope that helps!

Tom

Lewis said...

Okay, I have a bunch of questions on this...

First, you're rather vague when saying some things, for example:

in 5.a You say to associate this workflow with the "Loop list". Well, you just instructed us to create 2 loop lists, which one do you mean? 1 or 2? I'm assuming you mean the "Loop 1" list simply because this is the "For Each Loop 1" workflow, but i'm not sure.

in 6.a you do the same thing. You simply say "loop list" which isn't very specific.


in 3.a you say "create a new item in the Loop list", which again isn't very specific. Which loop list do you mean?


Where do we actually put our processing of the data that we want to do? For example, I want to create a new item in a completely different list, using data from the Data list. What step would I put that in?

Also, I can't get it to work unless all of them are already marked as "No" in the processed column. Perhaps that is because I've already got something messed up in something else, but if this is going to run every day, as I would like, it would first have to set an item to be changed to "No" and then process it.

I'm sorry, I'm just trying to figure all this out. This is my first attempt at working with Workflows, and what I've got isn't working so far.

Armin said...

Great Work.
Thanks
Maybe i am wrong but at the Loop-2 Workflow:
6. b. : Should it be: "Set this workflow to start automatically when an item changes" ?

Sorry for my english...

Tom Molskow said...

Hey Armin,

You are correct - good catch! I have corrected the blog.

Thanks!

Tom

Tom Molskow said...

Hey Lewis,

I have corrected the post in the locations you mentioned - thanks for that - I want to be sure the instructions are clear for everyone who is trying to use the workflow.

Also, note Amin's comment about step 6b which is now corrected to say "when an item changes" - this was another mistake in my instructions and it may be what is causing your issues.

You can add an additional step to create a new item in a seperate list after step 5g or after step 6f.

I hope that helps!

Tom

Unknown said...

Hi Tom,

Do you know how can I set the "Processed" field back to "No" so the workflow can run again?

Unknown said...

Hello,

How can I modify the workflows and lists to loop and add 100 items to the Data list? I need to add test data.

Thanks,
Brady

Tom Molskow said...

Hello Helcio Junior,

You can try this -

In the Process Data Workflow, after the last action (Log "Process Ran") add a "Pause For Duration" step, set the duration period for roughly twice what the amount of time you think is needed to process the For Each Loop throughout the list or library.

After the "Pause for Duration" step, add a "Update List Item" step. Update the Current Item, and then change the "Processed" field's value back to "No".

LMK if that works for you.

Thanks!

Tom

Tom Molskow said...

Hello Brady,

In the Process Data Workflow, after the last action (Log "Process Ran") add a "Create List Item" step. Add the item to the current list, and also add a "Processed" field, setting the value to "Yes". You can also add whatever addtional metadata you want. Finally, name the "Output To" variable whatever you like.

LMK if that works for you.

Thanks!

Tom

Unknown said...

Thanks for the quick response. I added that step, but it is not looping. Only adding 1 new item, not four. Any idea what I have wrong?

Thanks,
Brady

Tom Molskow said...

Hey Brady,

It's working for me, so I'm not sure what the problem is. Can you post some screen shots of your workflow design on Sky Drive or something similar? Looking at those I might be able to see the problem.

Thanks!

Tom

Unknown said...

I created a Word Doc with Workflow Screen Shots. I can share in Box if you will provide an email address?

Thanks,
Brady

Tom Molskow said...

Hey Brady,

If you send me your e-mail address, since all comments are moderated, no one will see it but me (and I won't post that comment). Once I have your e-mail I will grant you access to a Shared Folder on my Sky Drive and you can post your word document there. If need be I can also post the workflow and list templates to that same shared folder so you can just download my copies and try them in your environment.

Thanks!

Tom

Tom Molskow said...

Hey Brady,

I got your word document, I will look it over this weekend and see what I can find out.

Thanks!

Tom

Tom Molskow said...

Hey Brady,

I noticed two differences in your workflow:

For Each Loop 2, Update item in Loop 1, Field LoopCount Value should be 1, but you have 0

Process Data, Then Create Item in Current List, Processed should be No, you have Yes

Please make those changes, save, redeploy and test and then LMK if that helps.

Thanks!

Tom

Anonymous said...

Hi Tom,
I followed all your stepa, but can't seem to figure out how to kick off the workflow. I added a new item to Loop 1 list. Is this correct?
Thanks,
Ninel

Tom Molskow said...

Hey Ninel,

Did you try step 8?

"Test the Workflows: To test the workflow we only need to start the For Each Loop workflow from our item in the Loop list."

I hope that helps!

Tom

Anonymous said...

Tom,

First off, great work! I am having an issue that the loop is only processing 5 items. It looks like my loop2 is never running which I assume is part of the issue.

Also, if my data list has a many items and I am adding to it every once in awhile can I just set the conditon of the loop1 to a really high number?

Any ideas?

-Scott

Anonymous said...

Is there any way to do this similar action for SharePoint 2007 server when the server wont allow the system account to initiate a workflow?

Thanks,
RD

Tom Molskow said...

Hey RD,

I have a different post for SP 2007 - you can find it here:


http://sharepointgypsy.blogspot.com/2011/01/sharepoint-designer-workflows-2.html

As to will this work when the server won't allow workflows? Maybe not, but you can always give it a try.

Thanks!

Tom

Tom Molskow said...

Hey Scot,

Sorry for the alte reply - it's been crazy here lately!

To get the second loop working will require some "tinkering" with the timing of the first loop, modify the delay up or down and see if this helps.

Unfortunately you can never set the loop above 5, this is the maximum the workflow engine will allow before it assumes that the loop is a "run away" and shut it down.

I hope that helps!

Tom

Unknown said...

Hi Tom,

Please help me, i want to create a workflow that will email hourly, just to make sure that the site is online, is that possible?

Tom Molskow said...

Hello Ittech,

Sorry for the late reply, I have been working on a project. To create the type of workflow you need please see my other blog on creating a timer job workflow:

http://sharepointgypsy.blogspot.com/2011/01/sharepoint-designer-workflows-3.html

I hope that helps!

Tom

Anonymous said...

Hi
Thanks for the great post.. the problem I have is that I am left with the processed field containing "Yes" so I cannot reuse the field for the next update requirement without doing a post list update clean-up of the secondary list.
Can I ask how others manage the processed indicator in order to make the loop reusable on a target list.

Tom Molskow said...

Hello,

Unfortunately in SP 2010 you would have to essentially run the workflow twice, once for flipping the yes to no, and once for flipping the no to yes. You could add this as a secondary workflow that gets called when the primary workflow completes.

Thanks!

Tom

Graham Shaw said...

Hi Tom,

Newcomer to workflow design, but basically just want a workflow to run the same time everyday to update a field with the current date in it. (Basically so I can calculate the age of a file since it was last modified, so there may be an easy way of doing this) I thought this would be a good way to do it and learn a bit about work flow in the process. However when I get to step 5g the system will not allow a variable to be input and keeps asking for a number. I might be missing something but followed the guide a few times and come unstuck at the same point every time.

I am currently using Office 365 2010 but understand that MS are currently upgrading users to the 2013 version which might be causing my problem as I have been advised by MS to upgrade to SP Designer 2013.

Any help and guidance you can give would be greatly appreciated.

Many Thanks

G

Tom Molskow said...

Hello G,

In SP 2010 there is a free workflow scheduler tool available from HarePoint - the tool provides the following capabilities:

•Starting site and list workflows manually or automatically by schedule.
•Starting list workflow for all documents or list items or for those selected manually or by CAML query.
•List views support. It is extremely topical for such lists as Tasks where you may select, for example, “Due Today” view so that the workflow would process only tasks for today, rather than all ones in the list.
•Setting workflow launch parameters, including parameters described in the InfoPath form linked to the process.
•Adjusting flexibly the autorun schedule: by days of the week, by dates in a month, daily at a specified time, etc.
•Receiving alerts, daily or weekly summaries by email or via SMS with workflows results.
•Specifying accounts under which the workflow can be launched.

I have not tried the solution in 2013, but SPD 2013 does support SP 2010 workflows. On the other hand, in SPD 2013 there are better ways to do For-Each-Loops, so you might try those methods as well.

Thanks!

Tom

Anonymous said...

Folks,

I am having the same problem many of you are reporting, the workflow stalls after 5 items are processed. Has anyone actually got this to work? I have 'tuned' the pause timings no end and still the problem persists. Any help would be most appreciated.

TNX,
Mike

vmrao said...

Hi Tom,

Nice article and exactly what I was looking for. But, does not work for me as stated.

First of all, few things are not clear in the steps mentioned.

In the step 5L, the screenshot shows to match Title field with value of 'New Item'. I think the value should be
'For Each Loop 2' as that is the Title for the item created in Loop2.

In the step 7H, the instruction says to change the value of 'Processed' to 'No' but the screenshot does not show that.

In step 8, the instruction says to run the For Each Loop workflow in the Loop List. It is not clear which Loop List,
Loop 1 or Loop 2 List ?

I started the For Each Loop workflow on Loop 1 List in two different ways but both did not work as desired.

a) If I go along with the screenshot under 7H (processed is not set to No), all the items in the Data List are
getting processed but they remain at processed value 'Yes' and starting the For Each Loop workflow again would
lead to Error.

b) If I go along with the text instruction under 7H (processed set to No), only the first 4 items are getting processed
again & again in the Data List.

In our environment, the pause is not working exactly as specified as 1 or 2 minutes. Sometimes it takes 5 to 10
minutes for the workflow to trigger again after pause. May be that is the reason that my b) approach does not work and
the first 4 items are getting processed again & again.

For this solution to work properly, the pause should work perfectly in the environment. Is that correct ?

vmrao said...

Hi Tom,

Thanks. Nice work around solution for 'For Each Loop'.

It looks like, the processed should not be set to 'No' as mentioned in the instrcution 7H.

But finally when the workflow completes, all the items have processed set to 'Yes'.

How to reset the Processed value to 'No' for all the items after the workflow completes ?

Also, What are the additional steps needed so that the For Each Loop runs automatically everyday ?

I think the above two are common requirements if someone is looking for a For Each Loop solution. If you can incorporate those steps also as an addendum to this solution, I think that would be really helpful.

Tom Molskow said...
This comment has been removed by the author.
Tom Molskow said...
This comment has been removed by the author.
Tom Molskow said...

Hello Vmrao,

Thanks for your posts and I apologize for not apologizing sooner but I have been working on a new 2013 workflow project.

I'm glad you seem to have gotten the solution to work, and I have updated the posts in the area of 7h and 8.

Unfortunately in SP 2010 you will have to essentially run the workflow twice, once for flipping the yes to no, and once for flipping the no to yes. You could add this as a secondary workflow that gets called when the primary workflow completes.

In SP 2010 there is a free workflow scheduler tool available from HarePoint - the tool provides the following capabilities:

•Starting site and list workflows manually or automatically by schedule.
•Starting list workflow for all documents or list items or for those selected manually or by CAML query.
•List views support. It is extremely topical for such lists as Tasks where you may select, for example, “Due Today” view so that the workflow would process only tasks for today, rather than all ones in the list.
•Setting workflow launch parameters, including parameters described in the InfoPath form linked to the process.
•Adjusting flexibly the autorun schedule: by days of the week, by dates in a month, daily at a specified time, etc.
•Receiving alerts, daily or weekly summaries by email or via SMS with workflows results.
•Specifying accounts under which the workflow can be launched.

You can also try the timer loop workflow I designed - http://sharepointgypsy.blogspot.com/2011/01/sharepoint-designer-workflows-3.html

Again, thanks for the comments - if there are any other changes needed to my post or you need additional answers please let me know.

I hope that helps!

Tom

Philip Stichtenoth said...

I think I found the reason some commenters (myself included) were having issues with the workflow stopping after the first four items:
In 4l you posted a picture for configuring the activity to update the item in the Loop 2 list. In the picture the matching value is where Title = "New Item." My guess is that people were actually typing "New Item" in when the name of the item in their Loop 2 list was something different. Therefore, when the For Each Loop 1 workflow finished pausing and attempted to update the item in the Loop 2 list, it could not find a match and the workflow stopped.

It is probably safer then to pull the match where ID = 1, as you did in 6g. This should alleviate the problem.

Of course, after finding that out I ran into an issue wherein the workflow doesn't stop, but does endlessly run its cycle over the first four items again and again. Your earlier recommendation of having one workflow set Processed to "Yes" and a second to set Processed to "No" should solve this.

Finally, I noticed in an above comment (7/10/2012) that Brady had the Field LoopCountValue set to 0 in the Loop 1 item update, and you indicated the value should be 1. Your picture in 6g displays a value of 0; I am assuming that's why Brady had it set the way he did!

Thanks for the post! It's proving very educational for me.

Saroj Saini said...

All above post is very clear to understand about for each loop.
Thanks for sharing this .

Read also here about - for-each loop limitations Three scenarios where you can not use for each loop

http://efectivejava.blogspot.in/2013/07/for-each-loop-limitations.html

Greg said...

Thanks for the WF loop Tom, I was able to take it and alter it to my library or any library actually and after many trials, errors and all I finally got it linking through all items and finishing off, I also got it down to just 2 WF.
Again thanks a lot for the great work. If you want I can post it up.
Greg

Tom Molskow said...

Hey Greg,

Yes definitely post it up, I'd love to see what you have done with it.

Thanks!

Tom