Introduction
CAML (Collaborative Application Markup Language) can be used to query the SharePoint API and filter for specific values in specific fields. The CAML Query Builder built in to Lightning Forms and Lightning Tools Actions provides a way to easily filter SharePoint lists/libraries within a modern interface using extended conditions and options such as row limit, order by, and query scope. Also, within the CAML Query Builder you can see the result of your query configuration.
The CAML Query Builder is available in design mode in Lightning Forms (expressions, actions, Lookup/Data lookup, and sublist controls configuration), Lightning Tools Actions, and Lightning Tools Forms Studio (Aggregation, Scheduled Actions, Triggered Actions - in addition to the foregoing).
CAML Query Builder UI
The CAML Query Builder contains three functional tabs: Designer, XML, and Custom Expression.
Designer tab
This is the main default tab where you can configure the query specifying various conditions. Each setting is described below. The most common scenario is that you only need to specify the Where condition/s.
View scope
Specifies the scope for returning list items and folders in a list view.
Items/Documents (FilesOnly): Returns only items under the root in the list/library.
Items/Documents and Folders (Default): Returns all items and folders under the root in the list/library
Items/Documents recursive (Recursive): Returns all items in the list/library including items within subfolders.
Items/Documents and Folder recursive (RecursiveAll): Returns all items and folders in the list/library, including all items and folders within subfolders.
Order By
Specifies the sort order of the retrieved items in the list/library (Default, Ascending, Descending).
Rule/Condition
When using a CAML Query as a filter for a Lookup or Data Lookup field, or a sublist/library, you'll see this additional option to set a condition as to whether to apply the "Where" filter or not. An empty value will skip the condition and apply the filter. In order not to apply the filter condition, the expression here should return false or 0.
Example: You want to configure cascading lookup behavior for 'Country' and 'Company' columns. When the user selects the country, different companies should be dynamically displayed in the 'Company' dropdown. Additionally, it is required to display all companies in the dropdown if the field 'Country' is not selected.
To achieve this, you would use the Rule: “=[[Country]] != null”, as shown here:
Where
This is the main part of the query, where one or more query filters are specified. Select All (all of the conditions are met - i.e AND) or Any (Any of the conditions are met - i.e. OR). You can add any number of child elements within the 'Any' or 'All' context. You can add and move rows and groups using drag & drop. You can drag and drop one row, as well as a group with multiple rows in it. You can also expand and collapse sections of the query for easier reading.
A condition consists of a column operand, a compare operator, and either a value or an input operand. For value operands, the value can be specified by value or by using the Expression Builder (specific per column type).
View Fields
Specifies the сolumns you want to retrieve with the query. If View fields is not specified, then all of the columns of the list will be retrieved. The XML result of this option would appear like this:
<ViewFields>
<FieldRef Name="ID"></FieldRef>
<FieldRef Name="Title"></FieldRef>
</ViewFields>
Row Limit
The number of Rows to retrieve. By default, all rows that meet the filter criteria will be retrieved.
XML tab
On the XML tab, you can view the actual query you have built on the Designer tab in XML format. This is helpful for verification of the query, and to allow you to modify portions of the query manually.
Custom Expression tab
You can write a custom CAML query from scratch on the Custom Expression tab. You may wish to use this feature if you want to include JavaScript code in your query, as shown in the example below.
Note: The Custom Expression tab is not available in Aggregation (in Forms Studio). Also, the Designer and XML tabs will be disabled if you use the Custom Expression tab to build the query.
Run query
Using the 'Run' button at the bottom of the CAML Query Builder, you can easily see the result of your query configuration. This helps you test that you have configured the query correctly.
Note: If you use a placeholder (dynamic value) in the query configuration, the result of your query depends on the selected item on which you choose to run the query.