Query Multiple Lists Across Multiple Sites & Group Results Using SharePoint 2013 & Office 365 Workflow

In this article I want to continue one of my previous articles, Dynamic text and HTML templates using workflows for SharePoint 2013 or Office 365. Just in a few words as a reminder, in the last article we created a workflow that collects data from one list and makes two types of reports, an individual and a summary. These reports are delivered to the user and the administrator by email.

Now I will describe our new workflow action, Get Items by Query (many lists). As you can see in documentation, it is an analogue of the SPSiteDataQuery command that exists in the Server Object Model (SSOM) but it is not available in the client (CSOM). Moreover, it has additional features, like grouping and sorting, that I will cover in this article.

Let's imagine that we have something like a Document Management System (DMS) based on SharePoint Online. Each of our departments has their own site (HR, Sales, Finance, IT and so on) and each department on their own site has a document library structure specific to them. As in the last case, we want to build a checked-out documents report, but now it should show the data from all the document libraries and from all sites.

In this article I will describe how to query multiple lists across SharePoint sites. You can group results by specific field and then iterate through the groups.

As a proof of concept I will create two workflows that send reports about checked-out documents across multiple sites to individual users as well as a summary report to the administrator.

This is a case where Get Items by Query (many lists) will be very useful, because it allows us to run one CAML query to many lists and as a result, it will be a single Dictionary object.

Figure 1: Get Items by Query Many List

Moreover, we can sort and group list items by one of the fields. For example, you group checked-out documents by user. The result of the query can be structured like this:

Figure 2: Get Items by Query from many lists and group results

checked-out documents - Administrators' Report

So, let's return to our Document Management System based on SharePoint. To build a report of checked-out documents for an administrator we need to query for all the checked-out documents and compose an email for the administrator, in the following you can see the entire workflow process:

Figure 3: SharePoint Checked-out documents report

Click on the picture to enlarge it.

The workflow is divided into two parts. The first part is the configuration. It contains the email account and password. Also it contains an Email Template that I provide below:

Please review the list of documents which are checked out:
<br />
<br />
<table>
<thead>
<td>
<strong>User Name</strong>
</td>
<td>
<strong>Document</strong>
</td>
</thead>
<tbody>
{{#each Documents}}
<tr>
<td nowrap>{{CheckoutUser}}</td>
<td>
<a href="{{SiteUrl}}{{FileRef}}">{{FileLeafRef}}</a>
</td>
</tr>
{{/each}}
</tbody>
</table>
<br />
<br />
Thank you,
<br />
SharePoint Notification System.
<br />

The second part of the workflow contains the main business logic. It is more interesting to us. To get the required data I use Get Items by Query (many lists). The workflow action settings contains a few settings:

CAML Query: it is a query that will be run on each list.
BaseTemplate: the base template of the lists where the query will be run, in my case it is Document Library.
Web URLs: by default, the query will be run on the current site but you can specify from where to collect the data. In my case I have specified web URLs of HR, Marketing and other subsites.

In the following I provide a sample of the CAML query that I use:

<View Scope="RecursiveAll">
<ViewFields>
<FieldRef Name="CheckoutUser"/>
<FieldRef Name="FileRef"/>
<FieldRef Name="FileLeafRef"/>
</ViewFields>
<Query>
<Where>
<IsNotNull>
<FieldRef Name="CheckoutUser"/>
</IsNotNull>
</Where>
<OrderBy>
<FieldRef Name="CheckoutUser"/>
<FieldRef Name="FileLeafRef"/>
</OrderBy>
</Query>
</View>

The next important step that is required for the Render Text Template is to build the DataDictionary in the following way:

Figure 4: SharePoint Build a Dictionary Dynamic Value

I just created a new variable and placed the collection of checked-out documents and site URLs into separate properties of the dictionary. Now we can use this composed object as data for our HTML template.

The last step that I must do before sending the email to the administrator is to prettify the ugly user name. To replace "ID;#" to just dash I use a Regular Expression Replace workflow action.

checked-out documents: Users' Report

The second workflow should send individual notifications to users, it uses the same principles as the previous with the only difference that it uses a loop to iterate the received data. You can see the entire workflow in the figure below:

Figure 5: SharePoint Checked-out documents user report

Click on the picture to enlarge it.

The loop iterates groups with selected users. This is required because I use the "group by" option in a Get Items by Query (Many Lists) activity and our data looks as in the picture at the beginning of the article. Inside the loop, we receive documents that were checked out by this user and create an individual email notification for the user.

Other steps are very similar to the previous report and I don't think I should explain it more. One little detail is the Build Dictionary workflow action that is configured in the following way:

Figure 6: SharePoint Build a Dictionary Dynamic Value

The CAML query in this workflow is the same as in previous workflow, but the HTML template is a little different.

Hello {{UserName}}
<br />
<br /> Please review the list below and please check-in unused documents:
<br />
<ul>
{{#each Documents}}
<li>
<a href="{{SiteUrl}}{{FileRef}}">{{FileLeafRef}}</a>
</li>
{{/each}}
</ul>
<br />
<br />
Thank you,
<br />
SharePoint Notification System.
<br />

Conclusion

In this article I described how to query multiple lists across SharePoint sites. You can group results by specific field and then iterate through groups.

As a proof of concept I created two workflows that send reports about checked-out documents across multiple sites to individual users as well as a summary report to the administrator.

I hope this example will help you to implement your own workflows much easier. Feel free to comment.

The original article is available at the link Query multiple lists across multiple sites and group results using SharePoint 2013 and Office 365 workflow.