I have read in a number of forums posts that there are challenges using SharePoint list with columns of type choice together with the SharePoint integration pack (IP). The challenge is that if you use the Get List Item activity together with a choice column you will notice the result is blank/null, no data is returned by the activity. Other column types, for example Single line of text, works fine. Choice columns is columns with a menu to choose from, for example drop-down menu, radio buttons or checkboxes. This blog post will show how to get data from Choice columns with the SharePoint web service.

In this example I have a list named Virtual Machines. I use this list to manage my virtual machines in my sandbox, for example if I create a new item Orchestrator will notice that and create a new virtual machine based on the list item. This list includes two columns of type Choice, one for virtual machine size and one for virtual machine location.

Figure 1 SharePoint list settings and SharePoint form

Figure 2 show the runbook monitoring the Virtual Machine list in SharePoint for new items. When a new item is found the runbook use the Ask web service (Invoke REST Service) activity to query SharePoint for all information about the new list item. Most of the data the runbook gets from the Monitor activity but not data input in a Choice column, in this example the Monitor activity publish Size and Location as blank/null values. Figure 3 show the configuration of the Ask web service activity. The Title value in the URL is the server name inputted in SharePoint as a single line of text, which is picked up fine by the Monitor activity. The Ask web service activity will return all data for the specified server, including data inputted in a Choice column, the Query XML activity filter out and return only the Location attribute. Figure 4 show the configuration of the Query XML activity. Once the runbook have the location it will either invoke Virtual Machine Manager (VMM) to build the virtual machine or Service Management Automation (SMA). I use the SMA feature of Orchestrator 2012 R2 for cross cloud management, building virtual machines in Azure. I could build the Azure virtual machine from a Orchestrator runbook too, but it is handy to use PowerShell workflows in SMA for that. I use VMM for building virtual machines in my private cloud.

Figure 2 Runbook monitoring SharePoint list

Figure 3 Query web service settings

Figure 4 Query XML to get server location

Note that this is provided “AS-IS” with no warranties at all. This is not a production ready solution, just an idea and an example.

NOTE: THERE IS A UPDATE VERSION OF THIS IP. THAT WORKS WITH LIST ITEMS, DOWNLOAD