The Scenario
I’ve been working on a SharePoint hosted app that requires a daily workflow to run to count the number of vacant and occupied homes in each building and create a list item for each day. From there we can use this data to create occupancy charts and graphs with chartJS or Power BI but first thing’s first, I needed to write a workflow that will get me this data in a list called Occupancy Data.
The Plan
I have done a bunch of looping workflows in SharePoint Designer so logically my mind started there. My initial plan was to use REST to get all homes and then loop through the homes to count the homes that were vacant/occupied. As I thought about it I realized, why loop through all the items when I can just add a filter to my REST uri and get only the vacant or occupied homes? Then all I need to do is count the items in the dynamic value and I’m done. Great, the plan is set. I will loop through the items in the buildings list and for each one I will use REST to count the number of occupied and vacant homes for that building and spit out the results as a list item.
The Reason You’re Reading
Here is the fun part - how to use REST to get items from a SharePoint list and count the results.
Step 1
I use the LookupSPList activity to get the Items/__Deferred/uri property from the homes list. You could just use GetByTitle(‘Homes’) to get the list, but I want to make sure that I make the proper call even if someone changes the display name of the list.
Step 2
Use the BuildDynamicValue activity to create the request headers and use the BuildUri activity to build the uri. I’m really just combining the uri I got in step 1 with a variable I created for the query. The variable is called vacantHomeFilter of type String with a default of:
"?$select=Building,CurrentStatus&$filter=Building eq '" + CurrentBuilding + "' and CurrentStatus eq 'Vacant'"
So the full RESTful uri will come out to be something like:
https://[tenant].sharepoint.com/_api/web/lists(‘[guid]’)/Items?$select=Building,CurrentStatus&$filter=Building eq [thisbuilding] and CurrentStatus eq 'Vacant'
Step 3
Next is the HttpSend activity to make the call. Pass the request headers variable and uri variable from above, and output ResponseContent to a dynamic variable and RespondeStatusCode to a variable of type HttpStatusCode.
Step 4
This step is one you could easily overlook. The results are returned as one dynamic value in our variable so if you just count the responseContent variable you will always get 1. If you output that variable as a string you’ll notice it’s your typical JSON d/results. So the next thing that’s needed is to use the GetDynamicValueProperties (GetODataProperties) activity to extract the results and put them into a new dynamic variable as individual items.
Step 5
Finally the results are in a dynamic value that you can count using the CountDynamicValueItems activity and set it to a variable of type Int32.
Summary & Results
By using that process you can get all the counts you need throughout your app and create list items with those results. Once the data is in a list you can do anything you want with it. Just for fun here’s a chart built using chartJS and the results from this workflow.
