Using SharePoint REST API with CAML to return Search Results in a Flow

Flow allows you to easily manipulate your SharePoint content.  However, as I discussed in my previous article Manipulating SharePoint content using Flow, there are times where the basic actions will not suffice.

The Send an HTTP request to SharePoint action enables you to design your specific queries to perform various content management actions.  Some common scenarios include creating, modifying, and viewing items and documents in lists and libraries.  In some instances, however, you may need to leverage the SharePoint REST API to perform a search.  One way to structure the search is by using a Collaborative Application Markup Language (CAML) query. Here's how you do it...

Perform the Search

We start by using the Send an HTTP request to SharePoint action.  There are a couple of elements to pay particular attention to:

  • Site address - the site where your list or library resides
  • Method - make sure it's set to POST
  • Uri - You need to change the items in red for your specific needs
    _api/web/Lists/GetByTitle('[Your list name]')/GetItems(query=@v1)?@v1={"ViewXml":"[Your CAML query]"}

    for example, in my case, I'm searching for items that contain my search string (search) in either the Title and Description fields from a list called FAQ.  @{triggerBody()?['search']} is the search term being passed into the Flow.

    _api/web/Lists/GetByTitle('FAQ')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><Or><Contains><FieldRef Name='Title'/><Value Type='Text'>@{triggerBody()?['search']}</Value></Contains><Contains><FieldRef Name='Description'/><Value Type='Text'>@{triggerBody()?['search']}</Value></Contains></Or></Where><RowLimit>500</RowLimit></Query></View>"}


Only keep what you need

The query will return a lot if information in JSON format, some of which you may not even need.  In my example, I'm only interested in two fields - Title and Description.  So, you can use the Select action to only keep those fields you're interested in.  In the Select action, make sure to reference the results element within the JSON structure.  To get to the results themselves, you need to reference the results object, body('Send_an_HTTP_request_to_SharePoint')['d'['results'].  


Once you have the fields captured in the Select action, you can use them in your Flow.

Changing connections in a Flow

One of the best practices for Flow is to have key workflows run under a service account that has elevated permissions and possibly a higher Flow license.  This provides for more flexibility and potentially shorter workflow completion times.

At the same time, it is not preferable to give all Flow makers within an organization access to the service account (for obvious reasons).  Through this approach, you can have your makers develop and test the Flows.  Once they are ready for production use, they then need to be updated to use the service account connections.  But how do you do this?

There are a few approaches you can take to achieve this as discussed below.

Sharing Flow with Service Account

Sharing a Flow with the service account allows an admin to log in with the service account credentials and see and modify the service account.  Owners of the flow will have full access to all connections in the flow and the content within the connected accounts. Owners are not required to add connections to their own accounts, and can take any actions in existing connections and their content.

If they add new connections, they will default to using the original creator's account.


This approach works, but you need to carefully consider the implications of having some actions run under the Flow maker's account rather than the service account.

Changing Action Connections to Service Account

A second option available is to change the connection for the actions that require to be updated to that of the service account.  First though, an admin will have to log in once in order to create that connection and make it available to the Flow.  If the Flow is a simple one with only a few actions, then you can take this approach; however, if the Flow is complex with dozens or more actions, then this approach may not be feasible.

There are two side-effects you need to consider with this approach:

Resetting of Default Values

When changing the connection on an action, some fields will reset to their default values.  This can be frustrating, especially if your actions have many fields (such as in Dynamics 365 or SharePoint actions).



Changing Connections fails

If you've tried changing connections on an action to another existing action, you may have noticed the following behaviour - the the Flow canvas will blank out and you're not able to proceed.


Don't worry; you did nothing wrong.  Looking at the browser developer tools reveals that there's some issue with the way Flow is handling this request.


So what do you do if you still need to change the connection for an action?  A work-around is to create a new connection.  You may end up with multiple connections for the same account, which is fine.  Alternatively you can delete old connections for the same account.

Creating New Connections

The way you create a new connection for an action is by selecting the + Add new connection from the action menu.

Once you've authenticated with the connection service, you can then use it in your Flow.

Deleting Old Connections

From the Flow menu, select Connections.  


Then, select the duplicate connections you no longer need and delete them.  When deleting old connections you need to carefully consider if any of your other Flows are using them, as these Flows may stop working until their actions referencing the connections are re-authenticated.

Exporting and Importing Flow

A third option you have is to export your Flow from the current account and then re-import it.  During the import process, Flow will ask what connections should be used in the Flow.  At this point, you have the opportunity to select the Flow service account you have created.  All actions that were using a specific account before will be using the newly selected account.

Exporting your Flow

Start by exporting your flow into a Zip file as shown below.


Once selected to export, you will be prompted for a Name and be able to set the Package Content.  You can leave the latter as the default values as you have the option to modify them during import.


After you've exported, log onto the service account, open Flow, and from the top menu, select Import.  Select the file that you exported in the previous step.  Now, you will be presented with the screen below. 

Go ahead and update the import option (Update or New) as needed.  The related resources section is where you have an opportunity to change the different account(s) you want to use in the flow.  Once you've updated them with the correct accounts, click Import and you will now have a copy of the Flow to be used with the service account credentials.

Microsoft Ignite | The Tour (Toronto)

If you've missed the Microsoft Ignite event last September in Orlando, FL, no worries.  You still have a chance to get a lot of the information right here in Toronto next week.  Microsoft Ignite | The Tour  (Toronto) will be taking place at the Metro Toronto Convention Centre on Jan 10-11, 2019.

There are over 100 deep-dive sessions scheduled.  I'm excited to be presenting two breakout sessions on two of my favourite apps - PowerApps and Flow.


BRK3454 - Advanced workflow and business process management with Microsoft Flow

Get a deep look into advanced techniques and expressions used to build complex and robust workflows with Microsoft Flow. This intermediate session will cover a number of topics such as error handling, working with complex types and lists, and looping. Finally, discover how you can take it up to Azure Logic Apps when it makes sense.

BRK3464 - Take your PowerApps skills to the next level

Take your PowerApps capabilities to the next level with the latest in server-side logic on the Common Data Service for Apps, advanced expressions in canvas apps, using Flow to return values to apps, and more. Get inspired by this tour-de-force of hands-on demos and handy tips you can use to build business apps that will wow your users.




Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...