What do i really mean?
Now that I have got your attention with that eye catching title, let me expand and qualify it. So, what I will be doing exactly is
- Inspecting a List (a Task List actually) for Tasks that are Due and Overdue.
- I will be checking them nightly (every 24 hours to be exact)
- If task are Over Due, then I will take ‘some’ action on them, in this instance, I will be changing a KPI field but this can be quite exhaustive based on your own particular use case. see below for one i have in mind.
- Pause the Workflow for 24 hours and Repeat.
By the way, this CAN also be used in SharePoint Online
In this use case I have been charged to monitor a Task list (multiple task list from varying SharePoint Sites/ Site Collections) and aggregating them. Once complete, then we need to serve up the tasks that are overdue into a KPI dashboard and send the email link to managers of those offending taskees.
For my POC demo here I will show how to get 1 Site Collection but to get multiple all you have to do is make a separate REST call to that particular Lists API, and the rest is easy.
Pre-Requisites and Technologies Used
In this example I am using an On Premises SharePoint 2013 with Workflows enabled, I am also using SharePoint Designer 2013 as well, that is if for tooling. i.e. NO CODE. I will be using the SharePoint 2013 REST API to read the SharePoint Task Lists as my Entry Point URI. The generic url will be /_api/web/lists/getListByTitle(‘’)/items">/_api/web/lists/getListByTitle(‘’)/items">http://<sitename>/_api/web/lists/getListByTitle(‘<listNameHere>’)/items which fetches List Items from a Named list using oData. Now by default this will be returned to your in XML, so in order to use this in SharePoint Designer which requires JSON inside the Dictionary Object (see my post here if you need a refresher on that) you will need to modify the headers to accept JSON using the Accept Headers. The rest is a matter of using Loops to iterate through the List Items returned, and pausing for the time allotted.
As usual I put my URI inside a Variable to obscure the name & any API Keys if any, this time there was no need but it just makes for better programming especially if there are a lot and I want to swap them out from time to time without affecting the core program
the full list of properties from the Call HTTP web service is located below since it is truncated in the image
The Request Headers are “Accept: application/json; odata=verbose” and you can place that into a string type as well and call it into the HTTP Web Service Properties as well inside a Workflow Dictionary Variable
Once you have done that, then as seen in the first image and in my previous blog post on the same topic, you parse the JSON results to get the part of the dataset that you want, in this case i want what is under the “d/results” node and to do that I use the Get Item from Dictionary and parse out what i need then i count the items for good measure, save the “Count” as a variable to be used as my upper bound in my Loop Counter and log it out.
Do Business Logic
After setting up the stage to get the URI, return the JSON data, Parse It to the Node you want, and get a Count, the next thing is to do the business logic.
What you will see below in the image is us using the Variable Count as the upperbound in our Loop and then getting the JSON data into Local Variables to be manipulated in the Workflow Logic.
So, i have been talking about this for a while as most people who have known me for a long time, know be because of my work and efforts in SharePoint BCS. But i have actually moved my thoughts in some aspects to what BCS allows (even in 2013) and what Workflows gives you interms of interacting with External Data. You see my point is, if you do not need to look at that data, i.e. External List, or need to Search on that Data i.e. External Content Type as a Content Source, I THINK that it is to your best interest from a simplicity standpoint, a performance standpoint, best of breed standpoint… to do this as a Workflow calling External Web Services and manipulate the data how you want and then get rid of it (i.e. the Variables) when done. That way the data is only used for its intended purposes then goes away.
Back to our story, what we do next is inspect the Due Date and use Conditional Statements to affect changes to the Fields for what you need. in this instance I am updating a KPI field based on how far along or how overdue a Due date is on a Task List. Now the Use case here is that I can do this on Any Amount of List both On PREM and in Office 365 in the same logic, I have Scale and Scope down to a Tee 🙂
The logic loops through each item does a Check and then updates the loop counter until it hits the “Count” variable number then exits. Since this is a Timer Job then the next thing we need to do is Pause it and wait, see below
Now, this workflow was designed as a Site Workflow so it can be ran independently of any Library or Lists and this guy can run forever.
So the next time someone ask you to Do a Timer Job for them, especially if they say they want it on Office 365 (SharePoint Online) and it involves a use case similar to mine or at the very least is looking at affecting changes to a List/Library, consider doing a Site Workflow with Conditional Logic and Pause Duration. Its really is that simple.
If this was being done in SharePoint Online, you would also need to capture and pass along the oAuth Tokens FedAuth and rfTa in your dictionary object.
Cheers and Happy SharePointing
Year after year the organizers and volunteers that put on SharePoint Saturday New York City #spsnyc do an amazing job and the intensity and excellence keeps getting better year after year. Fantastic Job Rebecca “Becky” I, Greg G, Jason G, Tasha S, Tom D, Brandon B, Casey S, and the rest of the crew too many to name.
This year it felt like on all level it came together even with the late lunch 🙂 The audience was exceptionally knowledgeable and engaging, we had fun while learning; i guess it helps that I actually have been seeing them for many years know and we treat each other like extended family. Even though next year we will be in a new Venue, I will miss the Microsoft facility across form the Hilton where the Halal GUys have their truck.
Regarding my Preso, Deck, Session Information
So, as noted in my presentation, and for those of you that know the way I deliver sessions, I do a lot of demos, hardly any slides. This time however, my session was based on a 2 part blog post I did, I requested that my session attendees not take notes because the blog post will be more information that you had in the session, so please pick it up here
Finally a SharePoint Designer that Developers and BAs will Love – This is the Part 1 of what would have come before the session you sat in and
Part 2 of Finally a SharePoint Designer that Developer and BAs will Love – This is reflective of the session you had
Thank you all for attending, hope to see you again next year.
Purpose of this Blog Post & Primer for this Post
This post follows up on a previous post below where we discussed Workflows in SharePoint 2013 through the eyes of SharePoint Designer 2013 and Visio Professional 2013. Part 1 is linked below, this is Part 2
In the above post (this one) I will go about showing you the tooling and various simplistic but apropos examples designed solely to drive the usage home, in Part 2 we will use everything in a practical REAL WORLD sense
Part 2 – Put it all together with HTTP Service Calls via REST and the Dictionary Object/ Variable
In this post I will take a Publicly Available REST API probably the Weather API or Twitter API and use the HTTP Web Request along with the Dictionary Variable in SPD 2013 to surface it in a SharePoint Promoted Links App (List)
What is the Dictionary Object/ Variable
Simply put a Dictionary Variable is an Array i.e. a collection of related items that is indexed/indexable but in more detail it is a container designed to hold a collection of other variables which can be of a different data types. Specifically we have the following actions out of Dictionary
- Build Dictionary
- Count Items in a Dictionary
- Get an Item from a Dictionary
Now before we get too deep into The Dictionary Variable/Object, lets take each of these actions one at a time in a simple scheme and see what they do exactly; kick the tires so to speak, in our real world example we will NOT be creating the Dictionary Object as much as consuming JSON data and dumping the JSON data inside a dictionary object. You get it right? JSON data usually comes across as a JSON Array or JSON object, in our case we want that JSON array, even if it is one item, the Dictionary Object/Variable EXPECTS to see an array, I actually wrote a blog post https://fabiangwilliams.wordpress.com/2012/12/31/limitations-when-using-sharepoint-2013-workflows-to-return-json-data/ explaining that issue, which i think is a BUG that should be handled by the SharePoint Team. But lets leave that on one side for the moment and get back into our intended post.
It is ALL explained Here
As mentioned earlier, a Dictionary Object may be considered a collection of related items. Now to drive this point home lets use what we have at our disposal, in fact we will use two (2) examples, the first of which is to simply build on what we did in Part 1 using the Bank of Fabian Example. You see, there are ALOT of post out there that do a good job of explaining to you how to get Weather Data, Twitter Data, ITunes Data, you know all the public REST JSON available stuff, and granted I will do one here as well in example number two in the spirit of being complete. However, what you don’t often see is showing how to use SharePoint own REST OData examples, so I will do one here. Lets set it up for you.
Our Use Case
Now I’m a big believer in Use/Business cases when doing Demos’ and not doing parlor tricks for the sake of “Hey! Look at this cool new stuff I can do”, so The idea here is that
1. You can consume data from ANY SharePoint List as long as you have an account that has permission or an App that does, and then act on or manipulate data from inside your Site Collection [assuming a Site Workflow here]
2. You want to expose data/information to an audience that doesn’t have access to certain data or hosted elsewhere
Web Site: http://adotob.sharepoint.com (this is my Office 365 Dev Site)
List Instance: https://adotob.sharepoint.com/_layouts/15/start.aspx#/Bank%20of%20Fabian
So from the above we can tell that we have a list called …/bank%20of%20fabian and it has currently 5 items inside it yes? Ok, good. Now in order for us to get to that data we can call on our SharePoint oData service located here
Now you should understand that SharePoint 2013 REST APS doesn’t understand the $json options so, it will render its result in XML, however there are quite a few tools out there that can convert your XML to JSON. In fact if you adjust your request header on your call yourself in Fiddler or in browser extension such as Chrome or FireFox, you can get the visual effect you are looking for as well. So lets see what happens when you click on the link here for the REST API call
Now that is NOT going to be useful to us, so I will use this Chrome extension called ChangeHTTPRequest and after putting in my header i need which is
I get the below
Now admittedly that is also difficult to read, so you should invest in the FREE JSON Viewer I always use and its Waaayyy cleaner, just cut and past the entire content and you can see what I mean.
So now that we have established some “Trust but Verify” credibility, next lets look at what we need to do in SharePoint Designer 2013 (SPD2013) to move this forward. We will be Demonstrating the following Capabilities in this Example
- How to use Dictionary Variable/Object
- We will build a Dictionary Variable to hold our HTTP Header information so we can get JSON Data
- We will Count Items in a Dictionary Variable so we can get the “Count” value of all items in there to assist us in our “Loop” that we may iterate through all the items in the Dictionary
- Get Items from the Dictionary that we may use to (as in our case) Write to the Log History so we can see what we are consuming, but in our Part 2 Example coming up using PUBLIC REST JSON data we will use that data to load into a ListItem
- Call a HTTP Web Service
- We will call that webservice we discussed above to get oData back
- We will discuss the other Dictionary Variables we will need in Support of this Call such as our ResponseContent and our subset of Data we want to inspect
- Do a Loop
- We will use the total items count that we got from our “Count Items in a Dictionary” Action as our loop counter max value in our Loop construct
Lets Build out our Workflow
The first thing we will do is create a Site Workflow
So in keeping with “Fabian’s
Best Better Practice” we will stub out our Stages and Get some basic Logic out of the way
Once that is done the first thing we will do is build a Dictionary Variable to hold two items we need in order to get JSON data to be returned; we need to set the Accept and Content-Type HTTP header so the browser knows what we expect. We click Action then under Core Actions you will find Build Dictionary, because I use it a lot it is in my Recent Actions also
Once you click on ‘Build Dictionary’ you will get…
1 – You will then click on ‘this’ which will open up the dialog in  and in turn you will click the Add button. In doing so you will enter two Variables into this Dictionary Variable of String Data Type as aforementioned, in  I am showing you how i do it for the Accept header attribute.
When you are done, click the Ok Button. AND NOW YOU HAVE SEEN HOW TO CREATE A DICTIONARY VARIABLE and an acceptable use of it.
Next we will “Call the HTTP Service” by again clicking Actions in the Ribbon and then…
So again by clicking  ‘this’ we get the dialog box to enter the information about our HTTP REST Service which in this case we click on the ellipse  and put our workflow variable we created earlier that holds our URI, then we click  the OK Button.
Oh… i forgot something, but we can do that now… so you see in the above figure the second line has the (Output to Variable:dictionary) right? so I like to tidy up my Variable name, so what I do is click on the word ‘dictionary’ and create a NEW variable called RequestHeaders 
Now that that is done, we want to further configure the Service Request Call so we will ‘Right Click’ on our ‘Call’ and then Click on Properties..
Then I want to set the “RequestHeaders” Property to my Dictionary Variable “RequestHeaders”
and then click the OK Button.
Next we will need to ALSO create another Dictionary Variable to actually HOLD the data that’s coming back, if I haven’t said it before, that data comes back in the form of a JSON Array and the Dictionary Variable is the only object suitable to accept that data. So
and we name it ‘JSONResults’ so if we inspect the properties again you will see that we have everything we need now to call out to our HTTP Service and get meaningful results back
So now we can actually got GET the DATA now, by using yet another Action in the SPD2013 Ribbon, this time its the ‘Get Item from Dictionary’ Action
Once we do that we will want to click the ‘item by name or path’ link and give it the SUBSET or WHOLE path to the data we inspected in our JSON Viewer or whatever tooling you have to determine what you need. In our case we want the “d/results” subset then we will use the Index variable to get each line item. Furthermore we will want to store this information AS WELL into another Dictionary Variable.
So in  you have me setting the path based on what i want from evidence in the JSON Viewer, then in  I am showing you the relationship of use getting the values from the HTTP Call made earlier and finally  I’m dumping that data into another Dictionary Object.
if you forgot here is what i need to build that path take a look above at , then  to get my Array. In  I will use that below using the Index Variable inside a Loop Construct.
So that brings us to the last unique item we will be doing in our Dictionary Item and that is to get a COUNT of all the items in the Dictionary We Need. So again we go to the SPD2013 ribbon
and we now need to tell the action which Dictionary Item to count, REMEMBER we want the one that we did the Subset of, not the one returned from the Service Call.
Above  we are referencing our Subset Data and we are creating a new Variable of Integer type  to put that Number in. NOW this is an important distinction as well because what we have here is our UPPERBOUND number in the Array for out Loop Counter 🙂 smart huh LOL
Next we will enter the Stage “Loop and Write out our Data we Need” and put a Conditional Loop inside there that will use the Index value of 0 to start and STOP when we have reached the UPPERBOUND value of items in the array
Now when that’s all done, WE are done, one more Screen Shot and some guidance and you can take a mental break..
Now admittedly its a bit busy, and yes, its been hours now since I started on this blog and fatigue is setting in 🙂 but I think i can capture everything I need to say here, and I will wrap up as usual with a video. So we are setting the Initialize and Upper bound of our Loop counter in  and  respectively. In  we are actually going to WRITE OUT TO THE LOG the information garnered from the above two lines and the remainder is the Loop Counter which you saw in Part 1, so now time for the video of it all.
So this blog post is long enough, what I will do for the External Data Call is do it in another Post and I will Link to it here. Hope you learned something new.
Up Next – Addendum to Part 2 showing an External HTTP Service Call using REST/JSON [Hyperlink forthcoming]