Retrieve Array of SharePoint List Choices via Cloud Flow

Have you ever had the need to retrieve an array of Choices for a SharePoint column? There is a little known REST call to the SharePoint API which will allow you to retrieve such information and format in an array to use later in your Cloud Flow.

Here is an example Choice Column in SharePoint:

Using the endpoint http://myserver/_vti_bin/ListData.svc where myserver is the SharePoint site URL, you will retrieve an XML formatted list of all collections of information available on this site. Here you can browse a list of the various data collection names.

Then, if there is a specific collection you wish to query further, you can simpy append the name to the endpoint and you will retrieve an entity data model in XML. For example, should I wish to query the choices column (appropriately called Choices) on the List called LISTA, I could append LISTAChoices to the URL. Note that this is case sensitive. The response to the query https://mytenant.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/LISTAChoices is as follows:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="https://mytenant.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">LISTAChoices</title>
  <id>https://abdndamodev.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/LISTAChoices/</id>
  <updated>2021-05-16T16:48:31Z</updated>
  <link rel="self" title="LISTAChoices" href="LISTAChoices" />
  <entry>
    <id>https://mytenant.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/LISTAChoices('Bird')</id>
    <title type="text">Bird</title>
    <updated>2021-05-16T16:48:31Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="LISTAChoicesValue" href="LISTAChoices('Bird')" />
    <category term="Microsoft.SharePoint.DataService.LISTAChoicesValue" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Value>Bird</d:Value>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>https://mytenant.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/LISTAChoices('Damien')</id>
    <title type="text">Damien</title>
    <updated>2021-05-16T16:48:31Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="LISTAChoicesValue" href="LISTAChoices('Damien')" />
    <category term="Microsoft.SharePoint.DataService.LISTAChoicesValue" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Value>Damien</d:Value>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>https://mytenant.sharepoint.com/sites/DamoBird365/_vti_bin/ListData.svc/LISTAChoices('DamoBird365')</id>
    <title type="text">DamoBird365</title>
    <updated>2021-05-16T16:48:31Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="LISTAChoicesValue" href="LISTAChoices('DamoBird365')" />
    <category term="Microsoft.SharePoint.DataService.LISTAChoicesValue" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Value>DamoBird365</d:Value>
      </m:properties>
    </content>
  </entry>
</feed>

Note that the choices are stored under the key named “value”.

Retrieving this data in a cloud flow

Using the Send An HTTP request to SharePoint as the first action, you are able to call the required endpoint. Then using a select action you are able to repurpose the body in order to retrieve the choices from the collection you have specified.

Get choices via HTTP Request Action and repurpose via Select Action

The response to the HTTP request to SharePoint is in the form of a JSON Object, which you can then pass to the select action as an array. Build up your expression from the body to include [‘d’]?[‘results’].