<

Iterate a SharePoint list with SharePoint Designer

Published on
6,565 Points
3,465 Views
1 Endorsement
Last Modified:
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.
1
Comment
Author:GreatGerm
3 Comments
 

Administrative Comment

by:Ted Bouskill
I have a few comments.

Expand SPD and VS to the full terms.  It's an article and I think it's worth the time to use the full product names.

What level of site access is required to execute this?  If they are a site owner then why couldn't they use Visual Studio (which you already mentioned) to access the list web service to iterate through the rows and execute an action?  We do that all the time.

However, what is the benefit to this technique versus exporting the list as a spreadsheet then using VBA to iterate the rows in a list?

You can even write CAML queries to access lists as well:
http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1252

It's clever but I just don't see a benefit without a real world example.  Like emailing each member of a contact list in Sharepoint.
0
LVL 7

Expert Comment

by:meispisces
thats a really useful article.
0
LVL 1

Expert Comment

by:Snehal Rana
First of all great article. I am working on similar requirement to create a for-each loop to update multiple List items. On your Step 4 you want to start a workflow. Which list are you referring to? Main list where all the items are or the Taskmaster list?
0

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

Key to your CPU's ability to stay cool is to use the right amount of thermal paste and apply it correctly. In other words you want as much thermal conductivity between CPU and the cooling block. Use a quality thermal paste and apply it in a manner…
Check How effective MS Exchange Expert thinks Exchange Mailbox Recovery by SysTools IS. Visit the Official site to get detailed information:- https://www.systoolsgroup.com/exchange-recovery.html (https://www.systoolsgroup.com/exchange-recovery.h…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month