Workflow Loop Through Multiple SharePoint Items
Finally! It can be done with a SharePoint Designer 2013 workflow! This is something that I’ve been wanting to be able to do for YEARS, and now it’s possible… and I’m just now blogging about it. No, this can’t be done in any previous versions unless you go buy a 3rd party tool or get a dev to write something custom.
Think about any time you’ve wanted to be able to edit multiple items in a list according to some condition. Think about lookups. Say you’ve got a contact list of customers, and you’ve got a document library where each document has a lookup column to a specific customer. Have you ever wanted to trigger a workflow at the customer level that would automatically go do *something* on all of the items in the other list/library that are related to that customer?
The example I’m going to use here is a very common one that you all ask me about. I want to just get ONE email letting me know about all of my currently active tasks in a task list, instead of getting all of those individual emails for each separate task. This is very complicated, so it will help tremendously if you’ve done workflows before. This post isn’t written geared to someone who has never created a workflow, because I don’t explain what any of these concepts are (like variables, etc.), I just show the steps.
So, here’s what we have.
- A list of tasks (just using the task list template)
- A list of people. This is a custom list (here I named it Loop through tasks). I added a person/group field, and called it “Assigned To” (using the existing site column). I’ve set the Title field so that it’s not required.
The idea is that the workflow will be created on the Loop Through Tasks list, and for each list item where the tasks is not completed, and the due date has passed, it will send an email to the person that lists all of those tasks. For reference, here’s the first big chunk, and scroll down to see all of the individual steps.
- Create your workflow on your list of people. I named my workflow “Test loop through tasks”, and set it so that it automatically starts when an item is created. You’ll be using a LOT of variables in here, so let’s just create them ahead of time. (Use the Local Variables button in the ribbon) Here are their names and types:
Name Type dictionary Dictionary responseCode String JSONresults Dictionary dataset Dictionary index Integer count Integer myID Integer calc Number Index Plus One Number MyStatus String StringOfItems String myTitle String myAssigned String DueDate Date/Time Add One Number - In Stage 1 of your workflow, add your first action. Build a dictionary. Click the blue word this, and your dictionary is going to have 2 items in it:
- The Build a dictionary item will then look like this. Click OK.
- The next action to add is Call HTTP Web Service. Click the first this, and put this in it:
YOURSHAREPOINTSITEURL/_vti_bin/client.svc/web/lists/getbyid(guid’THE_GUID_OF_THE_LIST_YOU_WANT_TO_LOOP_THROUGH’)/Items
Mine looks like this:
http://rtm.contoso.com/customer/_vti_bin/client.svc/web/lists/getbyid(guid’11728079-25BE-43A9-9178-C58A988BD6FF’)/Items
(How do you get the GUID? In this case, I want to look through my task list, so one way is to go to my task list in SharePoint Designer, and in the List Information section it says List ID.)
The HTTP method is HTTP GET.
- Click the blue word response, and choose your JSONresults variable. At the end where it says Variable:responseCode2, just change that to the responseCode variable (step 1).
- Click the drop-down box on this action to call the web service. Choose Properties. In the RequestHeaders box, choose the variable called dictionary. The screen should look like this (of course with your own website in the address). Click OK.
- Next, add the action Get an Item from a Dictionary. Click on the item by name or path. Type this: d/results
- Click dictionary, and choose the variable JSONresults. Output to the variable called dataset.
- Add the action Count items in a dictionary. Click on dictionary. Choose the variable called dataset. Output to the variable called count. This variable is going to tell me the total number of items in that other list, my task list.
(After this action, for fun I like to add a Log to history list action, and just log the variable called count. Logging, just in general gives me more info on my workflow status page later.) - Add the action called Set workflow variable. Click workflow variable and select the one called index. Click value and type the number 0
This variable is going to allow me to loop through all the items in the other list, starting at zero, up to the total number of items in the list, the count. - Add another Set workflow variable. Set the StringOfItems variable to say:
Email Text:
This is the variable that we’re going to use to append information to it. For each task, later we’re going to append text to this so that eventually this variable can be inserted into an email that will go to the person and list all of their tasks. - Next, it’s time to add the loop. So in the ribbon, click the Loop button and choose to Loop with Condition.
- At the top of the loop, click the first value. Select the workflow variable called index. For the operator, select is less than. For the second value, choose the variable called count. Click OK.
Here’s a screenshot for reference, of what the whole loop is going to look like, and now we’ll go through the steps:
- Inside the loop, add the action called Get an item from a dictionary. What I originally wanted to do was to make it only get the items where the assigned to field (in the task list) equals the name of the assigned to field in my list of people here. I couldn’t for the life of me get this to work with a people field. So, instead I’m using the Status field to get only tasks that aren’t completed, and getting the due date field so that I can get just the overdue ones.
Click item by name or path. Use the string builder to do this. Most of this text is typed in there, except for the variable index I used the Add or Change Lookup button.
See where it says Status… that’s the status column in my task list. This word may be different in your own solution if you’re not dealing with tasks or statuses. How did I know to just type the word Status, when the field is called Task Status? This is important. Go to your task list settings page, and click the name of the Task Status column. Look at the URL. See the part where Field= will tell you the actual name of the column.
This part can get a bit more complicated if you have fields that have spaces or other weird characters in them. If I put a space in my column name when I created it, it will look something like Column%5Fx0020%5Fname
You then have to decode it to get what you need. Go here and put your column name in the Encoded box and click URLDecode. Then it will give you this in the Plain box: Column_x0020_name
That value is what you put in the spot where I put Status. - Still on the Get item from dictionary action, click the dictionary. Select JSONresults. Output to, click item and choose your variable called MyStatus.
- Add another action to Get item from dictionary. Instead of the status field, this time we’ll be getting the due date. Here’s what these last two actions look like:
I’m using these two fields because that’s my “Where”. I am only going to want tasks that have a certain status and a certain range of due dates. The fields used here will potentially be different in your own solution. - Now it’s time for a condition. Insert the condition If any value equals value. Again, this is where we use those two fields in steps 14 and 15. If the variable MyStatus not equals Completed.
- With the orange line directly under that last condition, insert another If any value equals value. And the variable DueDate is less than Today (Current Date). This will narrow down my list to only get items that are overdue and haven’t been completed.
- Inside of this IF section in the workflow, insert an action Get item from dictionary. This is similar to step 14, except this is getting the ID of each item that hasn’t been completed and is overdue.
- Next add an action to Set Workflow Variable. Set the variable MyAssigned. Click value, and click the fx (function) button. This is where I choose the Workflow Tasks (the list I want to loop through), and I want to get the Assigned To field for the items where the ID is in my list of IDs of the items that aren’t completed and are past due. The only reason I’m doing the Assigned To part here is because I wanted to do it at step 14 and that portion wouldn’t work with a people field.
- Add another action to set workflow variable. Set the variable called myTitle to the value of the task name.
- Add the action Log to History List. Log the variable called myID.
- Next, add an IF condition: If Any Value equals value. If the variable myAssigned equals Current Item: Assigned To. This will narrow down the list to only items in the task list that are assigned to the same person in the Assigned To field in the list we’re running this workflow from.
- Add an action to Set Workflow variable. Set the variable StringOfItems to this:
What the heck is all of this? This is where you create what you want the email to look like. Be sure to hit the enter key (carriage return) after you insert that first lookup to the StringOfItems. Basically I want it to show the title of each task as a hyperlink to that task, and I want to show when it’s due. This is going to loop through all of the person’s tasks that are not completed and overdue, and put them all in one big list in this variable. - Put the cursor at the very bottom of your Loop, still inside it, and insert an action Do Calculation. Calculate the Variable: index plus 1
Output this to the variable called Index Plus One. - Add an action to Set Workflow Variable. Set the index variable to the value of the Index Plus One variable.
This causes our loop to loop again until it’s finished going through all of the items in the task list. - Done with the loop. Put the cursor underneath (outside of) the loop, and add an action to Log to History List. Log the variable StringOfItems. This way you can see what will get sent in the email, here in your workflow logs.
- Add an action to Send an Email. Use Workflow lookup to send it to the Assigned To field from this list (called Loop Through Tasks). In the body of the email, insert the StringOfItems variable. Make the subject say whatever you want.
- In the transition to stage section, GoTo end of workflow. Publish the workflow.
COOL!
Now go add some fake tasks in your task list, and assign them to people, and then go to your list of people (the list you created this workflow on, I called it Loop Through Tasks) and add a person and watch this workflow run. Remember, don’t try running a workflow as the system account, it won’t work. After I run the workflow, here’s what I see on the workflow status page:
The count: 7 is the total number of tasks in the list. All this might take a minute if your list has lots of items in it. Then it always shows a zero, then it shows me that tasks 1, 6, and 10 are the ones that are not completed and are overdue. Then, only two of those tasks are actually assigned to my Assigned To person in the item I ran the workflow on. Those two get appended into the email text, with a link to each item and its title and due date!!
Okay here’s the next issue. How do you make this happen every day or every week or however often you want it to send these people their list of open tasks? Should be easy, right? Should just be an information management policy that would kick off the workflow. Nope, 2013 workflows can’t be triggered my an information management policy for some reason. So the easiest way is just going to make the workflow pause for whatever your duration is. Like, pause for 2 days or 7 days or whatever, using the Pause for Duration action.
Here’s how:
- Add a new stage after your first one, and call it Pause until tomorrow. Put a single action in it, to pause for duration, such as 1 day.
- In the Transition to stage section, Go To Stage 1.
- In Stage 1, change the transition to stage so that the Go To makes it go to the stage called Pause until tomorrow.
- Publish again.
Here’s that last portion of the workflow
That first screenshot in this post, plus the screenshot at step 13, plus this last screenshot, all together that’s the whole workflow. It just won’t fit on one screen here to do one screenshot.
The next issue is, how do you auto-populate the list of people each time tasks get added to the task list? Well, you’d think you could just create a workflow that creates a person in the list if they’re not already in it. Then, each time an item was added, it would trigger the workflow that runs on item creation, and then the loop would start.
Unfortunately, SHAREPOINT 2013 WORKFLOWS CANNOT BE TRIGGERED FROM ANOTHER WORKFLOW. This means that if a new item gets added to the list via workflow, my looping workflow would not start. THIS SUCKS, because a lot of my business solutions depend on the concept of a workflow adding or changing something in a list, and that triggers another workflow to run on that item. SHAREPOINT 2013 WORKFLOWS ALSO CANNOT BE TRIGGERED VIA INFORMATION MANAGEMENT POLICY, WHICH ALSO SUCKS.
So, the solution to this is to manually create the items in the Loop through tasks list.
I did end up having to add an App Step and move all my actions into it, because I tested running this workflow as a non-admin, and it gave errors like access denied. So, here’s how to add an App Step. Skip to steps 2 and 3 in this post.
This ability to tap into web services is very powerful, and here are some other blog posts that people have written about it:
http://sharepointryan.com/2013/09/10/create-a-sharepoint-site-spweb-using-rest-in-spd-2013-workflow/
This is a big reason why I want to upgrade to 2013. So many times have I needed/wanted to do a loop in 2010, but just can’t.
Thank you, exactly what I was looking for. I am working on a workflow very close to this. What I want it to do though is look through all of the tasks and send an email of tasks to each person who might be assigned one. So for example Sally might get an email with 4 tasks, Tom might get one with 10 tasks. I want to be able to do this using the assigned to field on the tasks and not having to use another list of people. Any tips?
This is working for me to an extent. It works great if the conditions in the loop are met < 3 times. The email will send with the StringOfItems like it should. However, if there are 3+ hits on the conditions of the loop, the workflow ends up cancelling. By logging, I can see that it runs through the entire workflow up until after the loop/before email is sent. Then it errors out and cancels (System.ApplicationException: HTTP 500…).
By logging what happens with StringOfItems, I see that it works correctly for the first two items. After that, it isn't appending anymore of the items. It continues to run until the loop is done, but doesn't add anything else to the string.
What would be the difference in it working perfectly for 2 or less items but nothing after that?
Twitter for response: @JordanAaberg
Scott,
That was the original way that I wanted to do this one, but I couldn’t figure out the logic, which is why I created the separate list.
Jordan,
In the test list I used, there were 50 or so items, and any given time it was successfully looping through 7 or 8 items. How many total items are in the list? I’ve seen somewhere where people mentioned that there are issues with lists with hundreds of items.
I’m hoping you will respond even though it has been nearly 4 months since you published this. I need a workflow that looks at a calculated field (modified+7), then emails a reminder to the person assigned every 7 days until status equals CLOSED. I tried a loop, but I get an error saying something couldn’t be null. Here is what I did:
if current status not equal CLOSED
pause until Update By (the calculated field that adds 7 days to the last modified field)
email Assigned To
wait 7 days if last modified is older than 7 and status is not equal to CLOSED email Assigned To
My users are just getting started on this kind of workflow and they kept getting stuck at Step 4 – Call HTTP Web Service. I wrote this generator to help them: https://github.com/kpereyra/SharepointRestUrlGenerator
Hope you find it useful also.
We have a new requirement and need your advise to see how to go about.
Currently, as per our design, we use ‘Document Sets’ and the workflows are configured for Document Sets.
However, in our Some specific region, they produce lot of logic drawings. Each drawing is a deliverable.- So, typically we can have around 1000 drawings in a project.
If we created a document set/document as per the current design, it will mean running workflows for 1000 document sets, 1000 emails etc.
This is not an acceptable option and hence we need to find alternatives.
Requirement:
We should be able to run the workflows in bulk.
Please help what options we have and how we can go ahead with this.
Thanks for this – it was a great help. The only issue I’m encountering is that the workflow fails when the contact has an external E-mail address. Does anyone know of any workaround for this?
Twitter: @stevieb100
Thank you,
Stephen
I need to implement similar workflow using visual studio. But I am stuck at d/results(0)/Title. Can’t figure out out to replace 0 by a variable.
Beyship,
I’d try putting the pause at the very beginning of the workflow, and you wouldn’t have to make it modified + 7, you could just do a pause for 7 days. If it’s pausing, the workflow’s not going to re-start each time it gets modified anyway. I’m not sure what the null error is about, just make sure all the fields involved have values in them?
KPereyra,
Thanks so much!!
Vivek Kumar Pandey,
That sounds kinda like what SharePoint alerts can do. That also sounds like this solution. Send a single email about a whole bunch of items just listed in the email. So follow the steps in this post, except instead of tasks, yours will be document sets.
Stephen,
External emails can’t be sent except in Office 365. Can you use a people picker so that it won’t let them type an external email address, they just have to pick someone in your company.
Nikhil,
I’m not a dev and I don’t know anything about Visual Studio.
I am trying to accomplish something and google directed me here. It’s not exactly what I was looking for. Have you ever been able to generate 1 email that has links to 4 related list items in 4 different lists? All these lists are part of one larger process, so they all go together.
Great article, excellent stuff.
The only tricky part, which you documented VERY well (now that I reread it knowing how it works) and still took me forever to figure out, is the name of columns with spaces.
Encoding the visible name is not it, nor is the field name in the field settings URL (which appears encoded). That is the encoded version of the non visible name.
My field: Item Id
Field = in url: Item%5Fx0020%5FId
Decoded name (for use in Workflow above): Item_x0020_Id
After assuming I knew what I was doing for 30 mins and encoding every random name I could find, I figured out what you meant, I guess I was due to do some of my own research!
Thanks! Very useful example.
HI,
(Posted this to the wrong page originall- both yours so if it looks familaiar – sorry :))
I’m trying to parse a list and then create entries in a second list based on the first. Unfortunately the first list is quite large, with 1100+ items.
I’ve got the first bit of the workflow working (test as I go) to read the list. Unfortunately the count always stops at 100. I’ve changed the item count on the “All Items” view of the list from 100 to 9999 but that doesn’t appear to of made a difference.
Any ideas where the 100 item limit is being set?
Also, can you prefilter the list to reduce the initial return count by either adding parameters to the http URL or building against a list view.
Thanks in advance,
Matthew
JSON is limited to returning only 100 items in d/results. If you view d/results in an online JSON editor (e.g. http://www.jsoneditoronline.org/) you’ll see that after the 99th item JSON gives you the REST API call to make for the next d/results and any additional 100 items left in the list
Will this workflow send separate emails if multiple people are added to the list of loop through tasks list at once?
I followed every line to recreate this workflow in Office 365 environment, but stuck with errors with the url string in step 4.
First I got an error saying:
expression \”web\/lists\/getbyid(guid’11504EDE-9297-4F4D-B3F0-FF92C78D66BE’)\/Items\” is not valid
it was odd, since the workflow replaced the original site url with just “web” and added ‘\’.
I changed the url few times following information from google search, but still keep getting:
Retrying last request. Next attempt scheduled after 24.03.2015 22:08. Details of last request: HTTP InternalServerError to https://mysitesname.sharepoint.com/_api/web/lists(guid'3fb38c9b-1a60-45fa-803b-6c3c32cc667b‘)/Items
Correlation Id: 38363fa0-9956-1a6a-bfad-35d835ccd592 Instance Id: 6d67bc82-c6cf-486e-9932-f9df872392b3
Invalid text value.
Please, could you explain in more detail how to build the correct url string for the 4th step?
Hi,,
im using Office 365 environment and getting the same error ..
can you please refere to the solution?
thanks
Receiving same error message when trying step 4 as well. Office 365 as well.
Figured it out. I removed all the ‘Log to History’ where I was trying to log dictionary variables. Seems there is a limit to how many characters can be written to the log using this action. Though, I still cannot get the workflow to pull data. Always returns 0 count items, workflow completes, never any errors that I can tell.
that works perfectly for me. I was looking for same. Thanks.
Yo estoy intentando crear un elemento de confirmación. Una circular. Pero desde el workflow, el campo “Destinatarios” lo deja vacío.
El origen de este campo es un control de selección de personas (admite varias) de un formulario Infopath.
Todo versión 2010.
Gracias.
Thanks for this post. Seeing a working example like this opens up the doors (for me) to plugging lots of REST API functionality into workflows.
As for filtering by a people picker field, did you use the Odata $filter option in your endpoint?
site/_api/web/Lists/getByTitle(‘TasksList’)/Items?$filter=(AssignedTo/Title eq ‘PersonsName’)
Anyhow, thanks for the post.
Best regards,
Joe
Hello –
I have a list which takes choices on inputs on what trainings they want for example .. Topic1, Topic2, Topic3 these are updated into a list item as comma separate values.. they have option to enter custom values too.
Now, I have created a another list which can keep a count of number of times a topic is requested. .. I would want to update the second list whenever I enter new value is it possible ?
I realize it’s been quite long since you originally wrote this post, but I’m having an issue I was hoping you could help me out with in regards to the topic.
For some reason the workflow get suspended whenever I try to fetch the actual data (First action within the loop) my workflow get’s suspended. I can’t quite decipher the error message from the workflow, but part of it goes like this:
Looking up a value using a key is not supported on an instance of ‘Microsoft.Activities.Dynamic.DynamicJsonArray’
Perhaps you can cast some light on it?
Thanks a lot for great article.
BR
Morten
According to MSFT Support.
“The maximum allowable length for strings in workflow text fields is 255 characters. If you set your text field to exceed this limit, its content will be truncated to 255 characters” , this means that it contents will be truncated , but it doesn’t guarantee that your work approval will be completed successful as your workflow already been prompted error. And the best alternative is to reduce your content to a supported number of characters (<255 characters).
see https://community.office365.com/en-us/f/154/t/241704
This is great and helped me get past a hurdle I was facing for a full day on the solution I’m building. Thanks!!!!
This post helped me a lot. Thanks!!
Hi laura,
Its a great post, but i am facing a issue.My requirement is to trigger workflow on a monthly basis so it should run manually for all list items.
I used your concept and tried to build the workflow.But the issue is when i am triggering the workflow manually it is reflecting changes only to that item on which i ran my workflow.
Thanks in advance,
I like it Laura, both your blog, and this idea. It’s going to help a lot once I get my head around it. I’m just going through the actions to add to my list initially, so I’m just getting started.
I’m running into a wall on step 8 Click dictionary, and choose the variable JSONresults.
The only variable available to me is Variable: Dictionary, or create a new one.
I must be missing something.
I thought I had followed the first 7 steps. My only thing I didn’t get was the GUID.
Looking at Jordan’s question from the top, and I had the same issue, (getting the Workflow Suspended with the HTTP 500 error after so many items). What I found was that it wasn’t because I had a certain number of items, it was because the StringofItems string variable was longer than it’s allowed to be (I believe strings can only be 255 characters, but could be wrong). For me, I was running find and my StringofItems printed out 230 characters to my email. I added one more task and received the error.
So, my fix (not sure if it’s the best fix or not, depends on your application) was to add 2 string variables called StringofItems2, and StringOfItemsLength in the local parameters.
Then inside of Loop 1, after the line that says “If Variable:MyAssigned equals Current Item:Assigned To” I added the following:
– Set StringofItemsLength to [%Variable: StringofItems%]^ (Note, you have to use the string editor)
– then Find ^ in Variable: StringofItemslength (Output to Variable:index1)
Now, you have index1 which is telling you that there is a “^” character at the end of your text and index 1 has the position of it.
Then, I did the following right after the lines above:
– If Variable: Index1 is greater than or equal 200
Set Variable: StringofItems2 to [%Varaible StringofItems2%]… (you set this part up like you did for String of Items)
– If Variable: Index 1 is less than 200
Set Variable: String of Items to [%Variable:StringofItems%]…(you already have this line in your code, but you need to move it inside of this IF statement)
So basically what is happening is, you are filling up the variable StringofItems with your loop, then it is getting too big for a string variable. So, once it is more than 200 characters, you will start filling up your new variable StringofItems2. Be sure to go to your email and add the [%StringofItems2%] or they won’t get emailed out. Also be sure to add it to your log history so you can see if it errors again.
Using this method I was able to work around that error mentioned above. I actually used StingofItems 5 times, because for my situation the characters add up quickly because I have the title of a task and the priority. I also added a similar item which would be like a ‘StringofItems6’ but if it get there I just set that to a message that says it was too long to fit and the user needs to open the SP list to see everything.
There’s a blog post from Sridhar Raghunathan on MSDN about how to start another workflow through the REST API. In this way your looping workflow could start if a new item gets added to the list via workflow.
FIX: SharePoint 2013 Workflow recursion prevention – Part 2:
http://blogs.msdn.com/b/sridhara/archive/2014/08/21/fix-sharepoint-2013-workflow-recursion-prevention-part-2.aspx
I know it’s been long but two things I faced when doing this are:
*remove the Curly brackets from GUID — {}
*the index in get item from dictionary should be wrapped in parenthesis
For eg: Get d/results(0)/Status
Laura , U are amazing. Your blogs are perfect and help me always..
Thanks a lot! As a beginner i didn’t face any challenges as it was neatly documented.
That is totally genius!
Just written a workflow to delete items from a list – really easy to do.
Brill!
THANK YOU! Extremely helpful!
Thanks for this excellent tutorial! I was able to use the concepts to create a workflow to auto-generate folders and documents for a complex idea management workflow using Templates library with columns identifying which go with the various process steps.
Now, if I could just figure out how to call this SharePoint Designer 2013 workflow from another SharePoint Designer 2013 workflow, so I don’t have to repeat the steps within each stage. It would be much better to call this from the other workflow with parameters, but I haven’t been able to figure that out. Any tips or ideas?
Hi Laura, great tutorial but I’ve hit a wall with the 100 item return limit from the call http web service. I’ve tried the ?$top=xxxx route and while my URL call can pull more than 100 results in a browser, the workflow only pulls the first 100 items…..every time.
Here is my url for reference:
“/_vti_bin/client.svc/web/lists/getbyid(guid’xxxx’)/items?$filter=((field1%20eq%20’IT’)%20and%20(Status%20eq%20’In Progress’))&?$top=10000
Thanks for any suggestions on this!
@nessimhalioua (twitter)
There is no top=10000… JSON is limited to returning only 100 items in d/results. If you view d/results in an online JSON editor (e.g. http://www.jsoneditoronline.org/) you’ll see that after the 99th item JSON gives you the REST API call to make for the next d/results and any additional 100 items left in the list
I can’t get this to work with office 365, should it?
Also where do you attach the workflow as that is not clear either. Is it to the list or a site workflow?
Hi Darren, it’s a list workflow. As I mentioned in the post, “create your workflow on your list of people”. It’s created in SharePoint Designer, directly on the list, so there’s no need to “attach” anything.
Hi Laura! Great post here and I’ve used it many times over. However, I recently hit a wall– the dictionaries work fine with a list but when I had a workflow against a list using an Infopath form, it doesn’t return any dataset at all. Should I change the “Accept” string header for building a dictionary?
Hi Emmanuel, that’s great that it’s been useful to you, but I’ve never tried it on an InfoPath form library, so I have never seen that particular issue. I’m not sure what the answer is.
Hi Laura , many thanks for this post and for all the great Power Hours ! Would you please guide me how is it possible to use a loop , and update a particulare field for all items in a SharePoint 2013 list ? many thanks in advance ! You are great, pretty and clever Laura !
Hi Laura, this post is great and has helped immensely with a project I’m working on. However, one road hump I’m facing is that when I GET the results of one of my variables, which is a string, from the dictionary it appears with quote marks either side when sending via email and logging to the SharePoint History list. It doesn’t however appear with double quotes in the SharePoint list itself.
Do you know what I can do to remove the quotes round the string items when retrieving from the dictionary and displaying via email?
Hi Laura,
your post is very helpful and it works very well .Can you please suggest how to get lookup column value .The value of this column would come from other list ,It can be multivalued lookup column as well. I tried but unable to get the values . Even though in the Query string I am able to get the column value using SITEURL/_vti_bin/client.svc/Web/Lists(‘GUID of list’)/Items?$select=Lookupcolumn/Title&$expand=Lookupcolumn/Title . It will be really helpful if you can help to get lookup column value
Hi Laura ,
Can you please suggest how to get multiple value of a lookup column (column of different list ) using REST API in workflow . I tried and I am able to get the value in the query httSITEURL/_vti_bin/client.svc/Web/Lists(‘GUID of list ‘)/Items?$select=Lookupcolumn/Title&$expand=Lookupcolumn/Title ,but when I try to get it via d/results/(%Variable:index)]/lookupcolumn/Title I don’t get any results .
Sorry, I’m not sure, I’ve never tried that.
@Rachana is the internal name of the lookup column ‘lookupcolumn’? d/results will only return items by the internal names it has stored for them… I use a combo of a ‘Modify Headers’ chrome add-in and a online JSON Editor to make the REST API web call in chrome, copy the results into the editor (which formats them in user friendly tree), and then am able to actually view d/results and the object inside. This helps find the internal names of every property associated with an item in a SP list.
Are you sure the internal name of the column is ‘lookupcolumn’? d/results is only going to reference columns by their internal names. If you view d/results in an online JSON editor you can see the items and the internal names of every property (including but not limited to the list columns)…
To view online I first use a ‘Modify Headers’ chrome extension wherein I repeat the same process of building a dictionary herein, use this to make the same web call from my workflow in my chrome browser, copy the d/results from my browser to an online JSON editor (http://www.jsoneditoronline.org/) which will format d/results in user friendly tree that I can then use to actually view the dataset variable from my workflow and see what all the property/column/metadata-attribute internal names are.
Hi Laura, Do you know how to achieve this looping in Visual Studio?
Nope, I’ve never used Visual Studio.
Hi Laura,
Sorry to drag up an old post, but have you ever used this method to copy documents from one library to another? The reason I ask is that I an putting share point document management in place for the Academic Committes at the University I work at.
We have 2 document libraries set up for each of our committees. In the first one (Admin Library) all of the administrative work is handled (documents put up and pulled down before a final agenda of papers is set). In the second one we have the “published” papers from the committee. The second library can be viewed on a read only basis by every member of the University.
So what we have been doing is using document set content types in each library (a document set functions as one instance of the committee meeting, e.g. ‘Academic Board 1st Aug 2016’) with a designated committee paper content type as the default within the document set. All items in library 1 have a column called Status: Public or Confidential.
I have created a small workflow to copy the document sets to the Public Library, and delete the entry in the ‘Admin’ Library.
I am now trying, so far not very successfully, to modify your loop workflow to run as a separate first workflow that iterates through the document set and deletes and copies any documents with the status ‘Confidential’ to a third confidential library with very limited access, and then deleting the original document in the ‘Admin Library’.
Then the copy document set workflow can be initiated safe in the knowledge that only papers with a ‘Public’ status can be seen.
As I’ve not managed to get it to work yet, I was wondering if maybe this kind of loop action just doesn’t play well with the copy and delete items in a list or library action.
Any thoughts?
Hi, you can add a filter to your web service call like in this example I have two filters https://xyz.sharepoint.com/sites/test/_api/web/Lists/getbyid(guid'**list**‘)/items?$filter=((Position_x0020_Number eq ‘**field**’) and (ID ne **id**))
Here’s the reference I use:
https://msdn.microsoft.com/en-us/library/office/fp142385.aspx
Thanks Laura.
Much obliged to you
Hi Laura, this is a great post and wanted to know if this would work at a higher level. I have a number of task lists for different teams (sub sites) and management would like a roll-up of those “past due” in a single weekly email. I can use a roll-up tool like Bamboo but they want the email reminder weekly. The solution above is for a single site/list… ideas for multiple sub sites rolling up into single email?
Oooh that’s a great question. No, I can’t think off the top of my head, of how that could be accomplished. It’s easy to roll them all up and show in a content search web part, but sending in an email is a whole other ball of wax.
Thank you for the reply… sigh… I was afraid of that. Only other thought I have is to create a few PowerShell scripts to do the same thing and then let it send out emails.
Hi Laura, following your instruction I got pretty far. What I could overcome is to retrieve values for columns whose names contain blanks. For example, I could not retrieve “Task Name” and “Assigned To”. Could you comment on general approach for that? Thx!
Column name (seen as column heading) is not equal to field name (referenced in the code). I read more carefully, and step #14 did emphasize that.
Hi Laura i hve refered this article n it helpd me well.inside the loop am calling the call action which but after calling this action am unable to iterate all items then it gets only first item n after that it is getting executed untill its limit but not going through all the items am trying ti iterate through one of document library
Hii Laura,
i want to iterate through all items from Library which is almost accomplished by following you’re article I’m very thankful for that but here another thing i want to do and that is copying items from one library to another library,for that am iterating througham using Loop and using REST API to copy items to another library ,here inside the loop am getting the Item/fole name in JSONResults Dictionery variable and then storing it into the variable Name and paiing this variable name to My REST API .it’s like “https://MyServerURL/sites/appdev/_api/web/folders/GetByUrl(‘MySourceLibrary’)/Files/getbyurl(‘[%Variable: Name%]’)/copyto(strNewUrl=’/sites/appdev/MyDestinationLibrary/[%Variable: Name%]’, bOverWrite=true)”
but after after execution of loop it returns only single(first) item/file name which am puting in log for checking purpose and am also puting the ResoponseCode variable in log within the Loop there for fisrt iteration it shows “OK” status and for the rest of iteration it shows “BAD REQUEST”,here that loop got iterated as per count of Items but returns only single(i.e first one).
But when am removing this Call Action of REST API then it iterates all items/files names and show into the Log history and Status of response code is “OK” for all these items.
so i want to know that ,why is this happening?do you have any idea of this?
Thanks
Now if I could only find a way to loop through a repeating table in my infopath form and write the entries to a list.
U nailed like a champ…God bless u
Hi Laura,
Thank you for the wonderful post, it is very easy to follow, however, I could realy use your help. Anytime I try to use the “Call an HTTP Web Service” on this workflow and others I get the following Responce Code Not Found, Count 0. When I use and outside Rest Client I get the message:
{
error: {
code: “-2147024891, System.UnauthorizedAccessException”
message: {
lang: “en-US”
value: “Access denied. You do not have permission to perform this action or access this resource.”
}-
}
I have full control of the site I am trying to use this workflow on, using SP online.
Thank you.
I have given the workflows on this site full cuntrol app permissions and have placed the call into an App Step in work flow but am still getting the reponce code unauthorized.
Thanks
Hi Laura,
Very Nice post. 🙂
I have a same requirement I followed all the steps above which you have provided but got stuck in 28 step.Can you please help me providing some screenshot of 28th step.
Waiting for your reply.
Hi Laura, I think I am doing something wrong. I have a sharepoint list(datasheet format), and I have a calculated column in there that flags items as Overdue after 7 days, if action hasnt been taken on that particular line item. I want a workflow to loop through every week and send an email to the “assigned to” person with a list of all the things they have overdue. I followed your instructions to the T, but it is only sending an email with the last item on the list. I could use some help. What am I doing wrong?
Thanks!
I think I might be able to help you without using a looping action. Please let me know if you’re interested.
Yes please! 🙂
I have done a few lists with a need to send multiple reminders, and I have sometimes had the loop work just fine, but I wanted to customize a few stages for 1st warning, 2nd warning and then escalation, but I’ll attempt to show you a simpler way.
We’ll need 3 stages: Pause Until Due stage, Send a notice & Restart Notice
Pause Until Due: I would simply pause until current item: Due Date. For the transition: If Due Date is equal to or less than today go to Send a Notice. Else go to end of workflow
Send a Notice: Email the person that is due. Pause 1 day. Transition: If due date is greater than today go to end of workflow else go to Restart Notice
Restart Notice: Log last updated on Modified. transition: go to Send a notice.
Notice that the 2nd stage will email and then pause to check the due date again before going to restart which shoots you back up to sending the email again. I will contact you on your web site to share one that is a little more detailed.
I am just doing my first workflow with Loops – I need two conditions to satisfy a loop and I can’t seem to get that in there…can you help me figure it out.
I did same as given steps. but my count is not getting increased. and thus I am not getting any email notification.
what d/results contains?
So does it work on SharePoint Online (under Office 365) or everyone successfully used it only in on-prem? Scroll+Read all the comment but didn’t came to success stories.
I thought I would add some comments for 2020:
First this was a great post that really helped me bridge my knowledge regarding the use of Rest inside a workflow. I was able to successfully create a workflow to send reminders on a periodic basis pulling in information from 2 other lists.
We use SharePoint 365. I use SharePoint Designer 2013 and created my workflows on the 2013 platform. Don’t waste any more time on SharePoint 2010.
Some experiences that contradict comments made here:
1) I was able to access person field extended data as long as the field was for a single user
2) I am able to start a workflow in a different list
3) I did not have the “StringofStrings” size imitation that others encountered. I was able append 10 URLS to the variable and insert into an email. No issue or error
Other points:
1) I am able to get the workflow to start at a specific time each day
2) I noted that the timer code above is confusing, and you need to decide if you will pause until tomorrow or wait till the next day. Both at the same time is not applicable
3) I recommend you create a central list with one row. Associate this workflow to it instead of using a user list. You will need to expand this posts concept and pull in data from your user list and your item list. The current design expects you to start the workflow manually for each user and this is not practical. I created a System List with one row. I run the workflow against that row. I get user and item data, then loop through users and examine associated item information
4) Pull in specific list data through REST…not the entire list of rows. Use filter= and select= to trim the result set. More efficient and faster
5) Build your workflow in stages or you will get lost
6) Log variable values to make debugging easier
For 2020. You all need to really really stop using SharePoint Designer workflows as soon as you can, and move to Flow instead.
Hi Laura, I have looked at Flow and I don’t see the same capability/flexibility. Same with Form vs Infopath. Can you recommend a resource that would explain the new Microsoft products well? I am doing things with REST like incorporating data from multiple lists, sending reminders and starting workflows. I did not get the impression Flow is mature enough to handle those things. Thx
Power Apps and Power Automate can do all of those things, actually much more easily than with SPD and InfoPath. You really just have to learn them, and you’ll see. It’s all done in a completely different way, so there isn’t a this = that type of comparison. I teach all of that, methodically, in a very organized way, in my courses at iwmentor.com. It’s only $40, and it’s about 20 hours worth of Power Apps and flows. Teaches you all those concepts you just mentioned. It’s so much better than the old way, and flow is much more mature now.