Looping Through List Items in SharePoint 2013 Designer Workflows

SharePoint 2013 Designer workflows now has two new interesting options: the ability to call HTTP web services and the option to loop over some code a number of times. This, together with the new REST API, which supports querying lists and returning data in JSON, allows iterating through list items in a workflow, something that was not possible before.

In order to demonstrate this, let’s create a new Site Workflow in SharePoint Designer, that will iterate through the Tasks list:

image

Call it Process Tasks, for example, and make sure you select SharePoint 2013 as the platform type.

In the workflow designer, let’s start by creating a new stage, call it Retrieve Tasks:

image

In it, we add a new Set Workflow Variable action which creates a new String variable called url with the value “http://sp2013/_api/web/lists/getbytitle('Tasks')/items”. This uses the new REST API, and you can pass in additional options, such as for ordering by the DueDate field in descending order:

http://sp2013/_api/web/lists/getbytitle('Tasks')/items?$orderby=DueDate desc

or filtering:

http://sp2013/_api/web/lists/getbytitle('Tasks')/items?$filter=DueDate gt DateTime’2014-07-31T00:00:00’

or paging:

http://sp2013/_api/web/lists/getbytitle('Tasks')/items?$top=20

Next, we add a Dictionary variable (Build a Dictionary action), call it requestHeaders, and initialize it as this:

image

Both “Accept” and “Content-Type” entries are of the String type and they both contain the value “application/json;odata=verbose”, SharePoint REST API understands this and sets the response content type appropriately as JSON. If we don’t pass these values, the output would come as XML.

Following, add an Call an HTTP Web Service action and set its properties. The request will be the url variable:

image

Response content will go to a new variable called responseContent:

image

Response headers will go to a new variable called responseHeaders:

image

And the same goes for the response code (variable responseCode):

image

Then we set the request headers to be the requestHeaders variable we created just now, by clicking on the properties for the Call an HTTP Web Service action:

image

Now, create a new stage, call it Process Tasks, and, at the end of the initial stage, add a Go to Process Tasks action.

On the Process Tasks stage, add a Get an Item from a Dictionary action, set the item as d/results, the source variable reponseContent and the output to a new variable of type Dictionary called list. Then count items from this list variable using a Count Items in Dictionary action and store the result in a new Integer variable called count. This variable will tell us how many times we have to loop. Finally, create a new Integer variable called index and set it to 0 (Set Workflow Variable), this will be the loop index.

Next, add a loop (Loop n Times), call it Loop Task Items, and set the loop variable to count. Inside the loop, get value d/results([%Variable: index%]) using a Get an Item from a Dictionary action from responseContent and store it in a new Dictionary variable called item. Get some fields (Get an Item from a Dictionary) from the item variable, such as Title and DueDate  (mind you, these will be task item fields) and store them in appropriate variables and do whatever you want with them, like logging its contents (Log). Time to increment the loop counter: add a Do Calculation action and in it increment the index variable into a new Integer variable called indexPlusOne. Then set the index variable to be the indexPlusOne (Set Workflow Variable). Finally, exit the loop and set the workflow status (Set Workflow Status action) to Finished. At the end of the stage, select Go to End of Workflow.

That’s it. Your workflow should look like this:

image

The new functionality makes SharePoint Designer workflows much more powerful than before. I will continue to talk about it in the following posts.

                             

58 Comments

  • Sounds cool. I will try this and post back the outcome when done. Thanks.

  • I am using SharePoint Online...
    I have followed the instructions provided to the letter (as I do not entirely understand the programming details behind each action) and...
    - first of all I am getting an error when I use "Inside the loop, get value d/results([%Variable: index%]) - SharePoint Designer is complaining about the [%%] portion and does not allow me to enter it so, I just removed the % just to see if it gets anywhere
    - when I try to run it, it looks like it dies ("suspended") right off bat... I am not sure if the original URL variable should be set exactly as mentioned (just replaced 'Tasks' with my list name) or something else: I realize this sounds dumb but do I enter it exactly as mentioned or do I have to use my site address? Please clarify if I need to replace "Tasks' with a full URL or something else?

    Any answer would be greatly appreciated,

  • Hi, Pauliciu!
    Do not add [%%] by hand! In the Get an item from a dictionary activity, click on the value link and open the editor. In it, add a lookup, select as source current workflow context and the variable you want.

  • If I go for the editor, I can't start with "d/results", it just picks the Index variable... what am I doing wrong?
    Also, how about the initial URL: is it literally "http://sp2013/_api/web/lists/getbytitle('Tasks')/items” where I just replace 'Tasks' with 'MyListName' or do I have to replace the whole thing with a url pointing to my list's site location? A clear example, sample would be very helpful.

    Thank you,

  • Hi, I have implemented this per your example and am getting gray hair trying to find out why my fields are coming back blank.
    Any thoughts?

  • ChuckD: what fields? Are you talking about http://sp2013/_api/web/lists/getbytitle('Tasks')/items?

  • Hi,
    i think http://sp2013..... is a public web site which using for retrieve data from Sharepoint custom list into JSON format, right ?
    If this site down, then the workflow calling this web site also be down, right ?
    Pls explain, otherwise, this article is useless. Thanks

  • Peter Petrelli: Of course, SP2013 is just a sample name which needs to be replaced by a real one...

  • I'm trying to use this workflow in the hopes of getting a count of items in the library. My goal is to have a workflow that runs and sends out an email IF there are more than 2 matching documents in one of the site libraries.

    I backstepped a bit after I was getting this error
    System.InvalidOperationException: Looking up a value using a key is not supported on an instance of 'Microsoft.Activities.Dynamic.DynamicArray'.

    I've gone all the way back and found that it's breaking first at the response content, which returns as 'unauthorized'. I'm running the workflow while logged on as a full administrator, any suggestions?

  • Hi, agreenway!
    I really can't say... I have had issues similar to those (unauthorized access to web services) and I managed to overcome them by adding this to the web.config:
    <add key="aspnet:AllowAnonymousImpersonation" value="false" />
    It may or may not be related to your issue... I suggest you go through the log files using ULS Viewer. Sorry, can't help more than this!

  • Thanks for the fast response previously! I managed to figure out what was going on last time and now I have a new exciting issue ;)

    My 'response content' is correct, it's returning exactly the items that it should. My issue is that the 'list' variable that I'm using to store the 'd/results' is returning blank, which is causing my 'count' to be 0. Any idea what could be happening during the

    "get d/results from response content and output to list"

    that would cause 'response content' to not correctly output any information to 'list'?

  • Hi, agreenway!
    Can you try to open the OData URL in your browser and see what it returns?

  • Forgot to mention: you will need to send a couple of HTTP headers, that you can't do with OOTB browser. There are a couple of browser extensions for Chrome (other browsers too, I'm sure) that allow you to set HTTP headers and "forge" requests. I suggest you try one of those, like Postman.

  • Hi, implementing this on my own list but when i perform a GET and print this to the workflow history there are no values. How can I test what values I am receiving and check that my API URL is correct.

    Is there a way to print Dictionary results?

    Thanks in advance!!!

  • Steve,
    When you browse to the OData service, do you see results (in XML)?
    The URL should be: http://<yourhostname>/_api/web/lists/getbytitle('<yourlist>')/items

  • Nice, looking forward to seeing these new posts especially with the scenario you’re building for SharePoint 2013 blogs!

  • hi...if one of the filed is person or group with multiple selection enabled, then how to get values from that field? because it will return data of type collection and where we cannot index and get value. please help me out.

    If you are not still clear what i am asking, please create a person or group filed with allow multiple values and get that in the result of your webservice. And try to print that field values in the loop.

  • Hi, Thank you for your post, it helped me a lot.
    I want to loop through a sublist like this; In the main list one column is called 'Name', and in the sublist there is a lookup column Name again and it gets value from main list's Name column.
    In the main list, if a record's (Let's say Name='X') Yes/No column is set to NO, I want to loop through in the sublist, find all the recordS whose lookup Name column equal to 'X' and make an update operation for another column of that record.
    So, how can i use the HTTP request? what should i write after the = operation below;
    .../getbytitle('Tasks')/items?$Name='value of updated main list's Name column'


    From now, thank you for your answer.
    Secil

  • Thanks for posting this. Judging by the amount, and type, of work one must put into this, I would be hesitant to say that SPD13 gives us the ability to loop through list items. But rather SPD13 no longer prevents us from coming up with a way to loop through list items. I know it's just semantics, but if it's a "feature", it should be something waaaayyyy more simpler.

  • Nice collection ...keep updating with new topics

  • Hi,
    first thank you for your post. it helped me a lot.
    i need to get the attachment to the list item and store it in a document library using Workflow. can you please tell me is there any way to do?

  • Hi, sindhuja!
    Not out of my head, have you looked at this: https://social.technet.microsoft.com/Forums/office/en-US/0a01d5bd-e3e9-47bd-8c0d-1c042f8d35d9/sharepoint-2013online-copy-list-item-attachment-to-another-list-using-sharepoint-designer?forum=sharepointgeneral ?

  • Hi RicardoPeres!
    This is very cool but I can't get it to work right now. I think I'm having the same problem as described by agreenway earlier in the comments but not resolved. I'm getting a count (second line in process tasks stage) of zero. Do you know what this could be?
    Thanks again

  • If I type 'http://<yourhostname>/_api/web/lists/getbytitle('<yourlist>')/items' into my browser then I can see the correct number of items but no information about the items whatsoever, just a number of lines with the date and time of the request.

  • I dug a little further and output the responseCode variable to the workflow log after the HTTP web service call. I get 'BadRequest'.

  • Guys,
    You need to send the accept: text/json header, don't forget about that!
    You can test it, for example, using Postman, the Chrome extension: https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop

  • I am using this solution in few places already but now I want to try this across two SharePoint collections and having issues.
    I have List1 in one SharePoint and List2 in another SharePoint. When I test my workflow for two lists on the same SharePoint collection it works fine but when I try to use http request to read from a different SharePoint collection I get 0 records. The URL works fine on the browser returning the expected number of items.

    Any leads?
    all SharePoint collections I try to use are in the same domain.

  • IC,
    To be honest, I didn't try it, but it should work, because when you are doing an HTTP request to the OData endpoint, it doesn't really matter what collection you're trying to access. Are you sure you're setting the headers properly?

  • Thank you for a great tutorial.

    Ran into issues that I can call the URL by hand and get XML returned to me, but the workflow itself failed with access denied (401) error.

    This article (http://www.learningsharepoint.com/2012/12/20/sharepoint-designer-2013-the-new-app-step/#ixzz2wQpAqsPr) really helped me out, so I thought I'd share it here.

    Thanks again,

    ~Markus

  • Following up on my previous question,
    Looks like this solution does not work across SharePoint site collections. This probably due to authentication restrictions.
    How did I confirm this?
    1. Get the workflow to work fine for two lists in a single site collection
    2. just change the URL to point to a different site collection and test ( result - zero rows processed)
    3. copy the URL from step 2 to browser and confirm that it returns all rows
    4. change the URL to point to the original list as in step 1 and confirm it still works fine
    5. Try step 2 and 3 for other SharePoint site collections and confirm that it does not work

    Thank you,
    IC

  • Hello Experts,
    I want to loop between two dates from start date to End Date.

    Please give me solution

  • I am not having an issue completing the requests. My issue is the list I am accessing is over 100 items. So the returned result is equal to 100 even though I know there are 379 items in the list. I have found through research that there is a limitation on SP that will only allow it to be 100. I believe that I have to now iterate through the list 100 at a time until end (a bit of a pain) but I don't know how to give my loop a value dynamically that knows it has reached the end if I can't get a total number of items to begin with.

  • for the '100' items constrain, add "&$top=380" at the end of the query string
    got it from here:
    http://www.mstechblogs.com/paul/quick-and-simple-sharepoint-rest-call-only-returns-100-records

  • Hi, super, thanks for the guide, it helped a lot!

    I do have some issues with the making use of the information stored in the 'item' variable (last step).
    How to correctly extract field data from the variable?
    "then Get Title from Variable: item (output to Variable: item name ) works fine. I typed 'Title' manually instead of using a Lookup or anything. Is that correct?

    The problem I'm having is that I want to use the field value that stores a Person (column name is also called 'Person'). Based on my 'source' list that I'm looping on, I wish to create new items in a 'target' list.

    My first guess would be to store that Person value from my 'item'-variable in a new variable <item person> and then use that variable when I want to create a new item in a different list. (it works for Title). But what variable do you put a Person into?
    Or maybe I should store person ID in an integer variable? (but what is the proper syntax? Get Person:ID from Variable: Item (Output....) ?

    I guess a different way would be to use a 'lookup' function when creating the new item for the target list. But then, you can't perform a lookup on the 'Item' variable, it just returns a string

    Many thanks!

  • Hi, Martin!
    Not sure, it's been a while since I last worked with SharePoint.
    Maybe you can lookup the user from the user hidden list?

  • i followd the same steps but here i want to perform this for a document library and am unable to get the Document/file(Item Name),if i want achive this then what i suppose to do?

  • i refered this Arcle to go through all items in library and it goes well but here I tried to use a call action in side loop for copying items to another library but after adding this action the loop returns only a first item instead of listing all items from library,does any one have any idea regarding this?

  • I am receiving an error when retrieving responses --

    An unhandled exception occurred during the execution of the workflow instance. Exception details: System.IO.InvalidDataException: Unable to deserialize HTTP response content. Expected ContentType : 'application/json', 'text/plain' or 'text/html',
    Received ContentType : 'application/atom+xml'. Content (truncated)

    So I tried setting dictionary response and headers fields to different values, but that is not changing recognition. I tied setting

    text/json header
    text/json
    application/atom+xml
    .
    On both dictionary fields but am receiving the same error.

  • Wonderful!

  • I'm trying to implement this workflow to iterate through a document library. Eventually, I will use it to change values in columns. However, for now I just want to make sure it is working and sends to the workflow history the values in one or two columns.

    I've implemented the workflow verbatim as described above. The correct number of documents are retrieved. However, try as I might - the value of any column as outputted to the History is empty/blank.

    I've checked my workflow, double checked it and had a colleague also check it and it is identically to that given above.

    When I enter the url in a Browser window I get the same results as Trojanian above. That is:

    'http://<yourhostname>/_api/web/lists/getbytitle('<yourlist>')/items'

    displays the correct number of Items but the displays is just a serious of lines with dates underneath.

    I've checked the Properties of the Call HTTP Web Service and the RequestHeaders Variable is set to Variable:requestHeaders and more importantly - in the Build Dictionary action both Accept and Content-Type values are set to: application/json;odata=verbose

    I'm going mad !!

    Can anyone offer any help, guidance or suggestions?

    Many thanks !!

  • Dear Recardo,

    I want to filter the items based on my choice column "PeopleSoft Access Request Status" which is equal to "Manager Review"
    How will i do it, would you please ping me the URL, how it would be?

  • Ramanjjilu: see how to build an OData URL with a filter:
    https://dev.office.com/sharepoint/docs/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

  • Thank you Ricardo for your response,
    This is my site - URL - /sites/corpfinpolicydev/PSAccess/SitePages/Home.aspx
    In designer i have given like this:
    URL - /sites/corpfinpolicydev/PSAccess/_api/Web/Lists/GetByTitle('PSAccessRequest')/Items
    is there anything wrong,
    corpfinpolicydev - site collection
    PSAccess - subsite
    Please suggest me. it is not passing URL step.

  • Hi,
    Please help me. The URL itself is not calling.
    Thank you

  • Hi Ricardo,

    I am stuck in the first step, when i run my site workflow manually, it is not returning any items.
    As per the first step, it should return list items.
    It is just starting and getting completed. i even put "lot history".
    I am creating a variable and storing my list items URL - /sites/corpfinpolicydev/PSAccess/_api/web/Lists/GetByTitle("List Name")/Items

    Please suggest me.Thansk You.

  • Hi Ricardo,

    I am stuck in the first step, when i run my site workflow manually, it is not returning any items.
    As per the first step, it should return list items.
    It is just starting and getting completed. i even put "lot history".
    I am creating a variable and storing my list items URL - /sites/corpfinpolicydev/PSAccess/_api/web/Lists/GetByTitle("List Name")/Items

    Please suggest me.Thansk You.

  • Hi Ricardo,
    am i filtering correct or wrong, this is my URL
    /sites/corpfinpolicydev/PSAccess/_api/Web/Lists/GetByTitle('PSAccessRequest')/Items?$filter=PeopleSoft_x0020_Access_x0020_Re eq 'Manager Review'
    My column is "PeopleSoft Access Request Status", do we have to get the internal name or as it is we can mention like this - PeopleSoft Access Request Status, if this is wrong, how to replace the spaces and get the correct the field name to filter name. please help me sir, this is where i am stuck, remaining everything is working fine. Thanks in advance.

  • Hey there, Thank you so much for this blog post. I have been trying to solve a looping problem for awhile now and I'm now closer than ever!

    I am using this setup to loop through list items on an on-prem sharepoint environment. My URL works in the browser and gives me the proper xml response. When I try to publish my workflow though using the same URL, the following error is thrown : 'The remote server returned an error: (503) Server Unavailable'. Has anyone else had similar issues or have any ideas on a remedy?


  • Hi, Laura S! Sorry, no idea! I guess you have to look at the server logs, as this is something on the server-side. Good luck!

  • Thanks.. It is working perfectly fine at my end!!

  • Super helpful.. Works like a charm! Thank you so much!

  • Hello,

    I'm getting the count correct, but when I get an item, it doesn't actually get anything. All fields return blank.
    Any suggestions?

  • how to trigger this workflow? if i write it inside another list , item added ? can i trigger this workflow?

  • samolpp: you should trigger it manually. You can also attach it to a list, but I don't think it makes much sense.

  • Thanks a million for this article. This helped me learn a lot, and got me on the way to my needed solution of totaling the average for all tasks in my "sub-list." Appreciate you publishing this, Ricardo!

  • Side Note: One stumbling block I had was one field not returning data. Having a percent sign (%) in the Field / Column name caused the problem. i.e., "% Complete" After removing the percent sign, all was golden. :) Just wanted to add this in case someone else runs into the same thing.

  • Thanks for the info, JeffreyC! ;-)

  • I have 2 list and want to loop though one of them based on items entered in the other list, how can I achieve this? I've stuck, help

Add a Comment

As it will appear on the website

Not displayed

Your website