Iterate a SharePoint list with SharePoint Designer

Situation:

You have a list setup just the way you want it and it now has a just over ninety thousand items in it. Now the boss decides that [insert some activity that has to impact every item separately i.e. sending an email to the stakeholder listed for every matching item with the value of one of the columns]. If only this was as simple as setting up standard reporting where a simple CAML query or Data View Web Part would suffice. Being the knowledgeable developer that you are, the first thought is to pop open Visual Studio, but in your corporate environment you don't have access to Visual Studio and definitely don't get a virtual machine to develop on.  And even if you did, it would take two months for all the approvals necessary to get your solution on the server which clashes with the boss' demand that it is completed tomorrow. Time for a workaround.

Workaround.

In an ideal world all list iteration would be done with Visual Studio, but I understand that some corporate environments are not conducive to Visual Studio usage so here is the way to do it with SharePoint Designer. Please note, only use this if you cannot create a Visual Studio workflow.
 

1

In the list you want to iterate through (named Example for this) create a new column to be used as check. This can be pretty much anything you want since you will be changing it manually with the workflow. For this example I will assume a choice column named Iterate with X and Y as the options.

2

Make sure all current items are set to X (datasheet view would work well here).

3

Create a new list that will control the workflow (I'll call mine Taskmaster). Add a number column (I'll call this Iteration) and a choice column named Current with X and Y as the options.

4

Set your workflow to start on change and manually.

5

Create a List item ID variable (I'll call mine loc). Create a String variable (I'll call mine Next).

6

In Step 1 Conditions check if Taskmaster:Current is X. If it is then set Next to Y. Add an 'Else If' branch with no condition that sets Next to X.

7

In Step 2 Actions set loc to Source:List / Field:ID where Field:Iterate / Value:Curr.  

8

In Step 3 Conditions if loc = 0 then end the workflow.  

9

In Step 4 start with a blank condition. In the action portion update item in Example where ID = loc with whatever action you need and by changing Iterate to Next. Update current item with Iterate = loc.
As it stands now, the workflow will step through each item updating Iterate as it goes. When it finds there are no more matches it should stop and Iteration should be 0. You can of course change X and Y to whatever you like. You will need to make sure that new items are set with Iterate matching Taskmaster:Current using a workflow or event handler. Also note, this will touch every row so for really large lists it will need a bit of processing time and it will fill your Workflow History list with entries.