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/