Query multiple lists across multiple sites and group results¶

This article will describe the action Get Items by CAML Query (Many Lists) . As you can see in documentation, it is an analogue of SPSiteDataQuery command which is exist in Server Object Model (SSOM) but it is not available in Client (CSOM). Moreover, it has additional features like grouping and sorting, which I will cover in this article. This approach works for SharePoint 2013 / 2016 / 2019 as well as for SharePoint Online in Office 365.

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

This will show you how to query multiple lists across SharePoint sites. You can group results by specific field and then iterate through groups.

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

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

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

Checked out documents – Administrators’ Report¶ So, let’s back to our Document Management System based on SharePoint. To build report by checked out documents for administrator we need to query all checked out documents and create composed email for administrator, below you can see the whole workflow process: The workflow is divided into two parts. First part it is configuration. It contains email account and password. Also it contains Email Template which 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. The second part of workflow contains main business logic. It is more interesting for us. To get required data I use Get Items by CAML Query (Many Lists) . The workflow action settings contains a few settings: CAML Query – it is query that will be run on each list.

BaseTemplate – 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 current site, but you can specify from where collect data. In my case I have specified web URLs of HR, Marketing and other subsites. Below I provide a sample of CAML query which 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 > On next important step that is required for Render Text Template we build DataDictionary following way: I just created new variable and placed collection of checked out documents and site URL to separate properties of the dictionary. Now we can use this composed object as data for our HTML template. The last step which I have to do before send email to administrator it is prettify ugly user name. To replace ‘ID;#’ to just dash I use Regular Expression Replace workflow action.