Caml Designer 2013

CAML Designer for SharePoint 2013

Andy Van Steenbergen and I worked very hard to revamp the CAML Designer and finally the tool is ready to be released: here is the CAML Designer for SharePoint 2013. This version is much more user friendly and no hasle with resizing of the different panels anymore. The design is metro style based.

CD2013

The functionality of this version of the tool is similar to what it offers you for SharePoint 2010:

  • you can build CAML queries for single lists
  • you can build queries that can be executed with SPSiteDataQuery
  • beside the pure CAML queries, you can also get code snippets for the server-side object model, the .NET client-side object model, the JavaScript client-side object model and last but not least code snippets when working with REST.

With this first version of the tool you are not able to connect to SharePoint 2013 online yet, but it is definitely something we are going to look at for next version.

You can download the tool from the BIWUG site. Click the Downloads tab and select the CAML Designer. From here you will be able to download the CAML Designer.

When the CAML Designer is launched, you’ll find a menu button bar at the top right corner of the main screen. You can click the download button to check if a click-once update is available or not.

CD2013 update button

Normally the click-once deployments will notify you when an update is available or not, but we also see that people download the offline package as well.

The offline package is also a bit different. Now it contains the .application file and “Application Files” directory as well. So you can install CamlDesigner now instead of just run the exe.

Login

When you start the CAML Designer application you get the following screen:

Caml Designer 2013

Before you can start working with the CAML Designer, you have to connect to a SharePoint site. Click on the connection button to get the connection panel:

CD2013 connection button

You can connect to following SharePoint sites:

  • a SharePoint 2010 site
  • a SharePoint 2013 site
  • an O365 site

Connecting to SharePoint 2013

To connect to a SharePoint 2013 site, click the Connection button at the top right menu of the main form. This will open a panel where you can define to which SharePoint site to connect. The SharePoint 2013 button is selected by default. You can specify your URL and choose how to connect:

  • using the server-side object model when the CAML Designer is installed on the machine running SharePoint 2013
  • using the .NET client-side object model when the CAML Designer is installed remotely
  • using the plain old web services of SharePoint

Toggle the Current Credentials button if you want to enter credentials.

Clicking the Connect button will establish a connection with the SharePoint site of your choice.

CAML Designer 2013

When you enter a URL, a small pre-check is done. If you entered a wrong URL, meaning a URL that can’t be found by the CAML Designer, you get following error message:

Connection UrlIsWrong

If you enter credentials that cannot be accepted by the SharePoint site you want to access, you get the following error message:

Connection IncorrectCredentials

If you already connected to the SharePoint site, you can choose the tile from the history list, representing the saved connection.

When the CAML Designer 2013 was able to connect to the specified site, the treeview in the left panel is populated with the lists of your SharePoint site. A spinning wheel is visible as long as the retrieval process runs and a status message in the bottom right corner of your screen informs you that a connection is being established.

Connecting to SharePoint 2010

You can also use the CAML Designer 2013 to connect to a SharePoint 2010 environment. In that case select the SharePoint 2010 button. You will see that you can only connect using the client-side object model or the web services.

connect to SP2010

Because the CAML Designer 2013 is compiled against the .NET framework 4.5, you are not able to connect to SharePoint 2010 with the server object model BUT the code snippets for the server-side object model remain available.

Once connected to the SharePoint 2010 environment, you will only get the code snippets that are available on SharePoint 2010; i.e. no REST snippets and limited set of code snippets for the client-side object model (which are lot extended in SharePoint 2010).

Connecting to Office 365

You can also connect the CAML Designer to your Office 365 environment and build your queries. Specify your URL to your environment and your credentials to log on and click the Connect button:

Connect to 0365

You can only connect to Office 365 environments that are already upgraded to SharePoint 2013. O365 that still have SharePoint 2010 underneath, are not supported by this tool.

If you would encounter problems when connecting to O365 sites, and you wish our support, you can do the following:

1) navigate to your site and append /_vti_pvt/service.cnf at the end of the url, i.e. https://TENANT.sharepoint.com/_vti_pvt/service.cnf.

2) Send the response to camlfeedback@biwug.be

This way we can try to find out what’s going wrong.

In the Recent list your O365 environment(s) are represented by a key icon.

When hoovering over the panes in the Recent list, you can see a tooltip informing you on how you connected to this site before:

recent connection tooltip

Clearing the history list

If you think that the list of available recent connections becomes too long or if there are a lot of obsolete connections in it, you can clear this history list.

Click the settings button on the menu bar:

CD2013 settings button

There you have the possibility to clear the list of recent connections:

clear history

Start to build queries

Expand the treeview to see all the lists:

SharePoint lists treeview

In this version of the CAML Designer, there is no ribbon anymore. The right pane is divided in two parts: an upper part where you can construct your query and a lower part where you can view the CAML query. You can also view code snippets for the different object models with which you can query SharePoint lists.

To construct your query, you can toggle the tabs in the upper panel:

  • ViewFields: toggle this button if you want to define the columns that you want to be returned in your result set (it corresponds to the SELECT clause of a SQL query).
  • Where: toggle this button if you want to define one or more filters.
  • OrderBy: toggle this button if you want your result set to be sorted.
  • Query Options: toggle this button if you want to set additional query options.

To be able to construct a CAML query, you have to select a list from the treeview. Each tab contains a panel that gets populated with available fields from the selected list.

Each clause will be documented in detail in the following sections, and how you can use the CAML Designer to build up each of these clauses.

The lower panel contains tabs to list the different code snippets based on the CAML query that is constructed in the upper panel. Following code snippets are provided:

  • The Caml tab shows you the pure CAML query.
  • The Server OM tab will show you a code snippet using the server-side object model to execute your CAML query.
  • The CSOM .NET tab will contain a code snippet that you can use when developing f.e. a WPF application that needs to connect to a SharePoint site.
  • The CSOM Rest tab will contain a code snippet that retrieves data from SharePoint using REST.
  • The web services tab will display a code snippet that executes the CAML query using the lists.asmx web service.
  • The PowerShell tab will display a code snippet that executes the CAML query using PowerShell.

No other types of code snippets are available yet. Code snippets are only provided in C#.

Hidden fields

In some cases you want to build a filter based on a value in a hidden field. By default the CAML Designer doesn’t show hidden fields. To solve this issue we added the check box “Show hidden fields” to the user interface just above the list treeview. By default the hidden fields are not displayed, but you can click this check box to get them displayed.

Show hidden fields

Long Display Names

One of our users als reported that there were issues with long display names. It would ask a lot of redesign to have this properly displayed so we chose to add a tooltip that shows the complete display name. We hope that this suits your needs.

Long display names

The OrderBy clause

If you need a sorted result set, you have to define a sort order. To define a sort order for your query, you have to click the Order By tab in the upper panel.

Click the field in the left panel on which you want to sort and drag it to the right of the panel. The field will appear in the right panel with an image indicating the sort order. Just click the image if you want to change the sort order.

CD2013 OrderBy

While you are building the sort order by clicking around, you will see your CAML query evoluate at the bottom of the screen. When in first instance the Last Name field is added, the CAML looks like the following:

<OrderBy>
  <FieldRef Name='Title' />
</OrderBy>

While the available list fields are displayed with their display name, the internal name of the field is used to build the OrderBy clause.

If no sort order is specified, the result set will be ordered in ascending order.

Select a second field from the left panel if you want to sort on more than one field. Your CAML query will immediately change as follows:

<OrderBy>
  <FieldRef Name='Title' />
  <FieldRef Name='Company' /> 
</OrderBy>

If you want to sort in descending order, click on the image next to the field name in the right panel and your CAML will look like this:

<OrderBy>
  <FieldRef Name='Title' />
  <FieldRef Name='Company' Ascending='FALSE'/> 
</OrderBy>

If you want to remove a field from the order by clause, drag it back to the left panel.

You can also change the order of the selected fields, you could for example drag and drop the Company tile to the top of the list; the CAML snippets will automatically be updated.

Unlike the CAML Designer for 2010, the query is not constantly executed to show you the results of the query you are building. Whe removed this functionality because a lot of developers encountered performance problems. If you want to see the result of your query, you can click the Execute button at the top right corner of the screen:

CD2013 execute button

Or click the Test tab next to the Query Options tab:

CD2013 test tab

Your results will be displayed in a grid:

CD2013 OrderBy results

The Test tab displays also a label that indicates the number of rows that are returned by the result set. Once the number becomes visible, you know that the retrieval has finished. This is handy when your query does not return a result and no grid becomes visible. The number zero will indicate that the query is finished but that no results have been found.

In the bottom pane you can inspect the CAML query and the different code snippets. By default the CAML tab is activated. Click the other tabs to inspect the different code snippets.

CD2013 code snippets

Click the Server OM button if you want to learn how to set the sort order and pass it to an SPQuery object:

SPList spList = spWeb.Lists.TryGetList("Developers");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
    qry.Query= @"<OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>";
    SPListItemCollection listItems = spList.GetItems(qry);
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Developers"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
            <Query>
                <OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

In this version of the CAML Designer, also code snippets for REST are generated:

.ajax({     
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",     contentType: "application/x-www-url-encoded", 
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

As you can see in the screenshot, you can choose if the results need to be returned in JSON format or in ATOM format:

CD2013 code snippet rest

If you choose to have results returned in JSON format, you have to set the request header to ….

.ajax({
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

If you choose to have results returned in ATOM format, you have to set the request headers as follows:

.ajax({     url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",      
   contentType: "application/atom+xml;type=entry",
   headers: {"accept": "application/atom+xml"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The difference lays in the way the results are returned in the response. If you sent your request for json, the results are returned as follows:

CD2013 rest atom response

If you sent your request for atom, the results are returned as follows:

CD2013 rest json response

If you need to pass your CAML query throught the plain old lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Execute button

As explained a few paragraphs before, you can click the Execute button to view the results of your CAML query.

CD2013 execute button

You can change the CAML manually in the text box in the lower pane and clicking this execute button will show you the results of your manually adapted query. The button will be disabled when you are in the other tabs with the code snippets.

The ViewFields clause

When you execute a query, the result set contains by default all the fields from the default view, plus a number of system columns like ID, Created, and Modified.

If you need only a limited set of columns returned in your result set, you will have to define a ViewFields clause.

CD2013 viewfields

If you want to remove a field from the ViewFields clause, drag it back to the left panel.

You can also change the order of the selected fields, you could for example drag and drop the Job Title tile to the top of the list, it will automatically change the CAML snippets.

Your CAML query looks like the following:

<ViewFields>
    <FieldRef Name='Title' />
    <FieldRef Name='FirstName' />
    <FieldRef Name='Company' />    
</ViewFields>

You can use the following code snippet for the Server object model:

SPList spList = spWeb.Lists.TryGetList("Developers");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
 qry.ViewFields= "<FieldRef Name="Title" /><FieldRef Name="FirstName" /><FieldRef Name="Company" />"; 
    qry.Query= "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>"; 
    SPListItemCollection listItems = spList.GetItems(qry);
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Developers"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
 <ViewFields> 
 <FieldRef Name="Title" /><FieldRef Name="FirstName" /><FieldRef Name="Company" /> 
 </ViewFields>
           <Query>
                <OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

The REST snippet will look as follows:

.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?
      $select=Title,Company,FirstName,JobTitle&$orderby=Title,Company desc", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The PowerShell snippet shows the following:

$spweb = get-spweb http://your-site 
 $splist = $spweb.Lists.TryGetList("Developers") 
if ($splist) 
{ 
   $query = New-Object Microsoft.SharePoint.SPQuery; 
   $query.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='Title' /><FieldRef Name='Company' /><FieldRef Name='JobTitle' />"; 
   $query.ViewFieldsOnly = $true; 
   $items = $splist.GetItems($query); 
}

If you need to pass your CAML query throught the lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='FirstName' /><FieldRef Name='Company' />"; 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = 
   listsWS.GetListItems("Developers", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

The Where clause

To avoid the retrieval of all the list items in a list or document library, you can filter data by defining a Where clause. Constructing a Where clause in CAML can be rather complex, especially in cases where you need more than one filter.

Click the Where tab on the upper panel and drag a field from the left panel to the right panel. The field control now consists of 3 parts: the field name, an operator button and a control in which you can specify a value.

CD2013 where clause

CAML contains following operators:

  • Eq: equal
  • Geq: greater than or equal
  • Gt: greatoer then
  • Leq: lower than or equal
  • Lt: lower than
  • BeginsWith
  • Contains
  • IsNull
  • IsNotNull
  • Includes (new in SharePoint 2010)
  • In (new in Sharepoint 2010)

Right-click the operator button if you want to change the operator.

CD2013 change operator

As of the moment that you start typing a value in the text box, you will see your CAML change, and the designer starts executing your query in the background.

A simple Where clause in CAML looks like this:

 <Where>
     <Eq>
        <FieldRef Name='Title' />
        <Value Type='Text'>Test 1</Value>
     </Eq>
 </Where>

Boolean fields

There was also a problem with querying boolean fields. This issue is now solved:

  <Where>
      <Eq>
         <FieldRef Name='VeryTall' />
         <Value Type='Boolean'>1</Value>
      </Eq>
   </Where>

I tested and retested, and this query returns rows:

Boolean field query result

Choice fields

Depending on the data type, the control where you can enter the value to filter on, will be different. You get a text box for fields of type text, note, number, counter and computed. If you want to filter on a boolean field, you get a check box.

But if you want to filter on a choice field or a multi choice field, you get a list box with all values configured for your choice field. You can select one of the values.

CD2013 choice field

Once you select a value, the CAML query is constructed in the left bottom panel and executed against the list, showing the results in the left right table. A filter criteria on a choice field looks as follows:

 <Where>
     <Eq>
        <FieldRef Name='Choice' />
        <Value Type='Choice'>Choice 2</Value>
     </Eq>
 </Where>

Lookup fields

If your field is a lookup field or a multi lookup field, you also get a list box with all values of the lookup list.

CD2013 lookup field

Once you select a value, the CAML query is constructed in the left bottom panel and executed against the list, showing the results in the left right table. A filter criteria on a lookup field looks as follows:

 <Where>
     <Eq>
        <FieldRef Name='Countries' />
        <Value Type='Lookup'>France</Value>
     </Eq>
 </Where>

It is possible you want to filter on the ID of a lookup value, because values can change over time. In that case you can check the Query by ID check box and your CAML query will be changed into the following:

 <Where>
     <Eq>
        <FieldRef Name='Countries' LookupId='True'/>
        <Value Type='Lookup'>1</Value>
     </Eq>
 </Where>

For the .NET client object model, the snippet will look as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Developers"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery(); 
    camlQuery.ViewXml = 
        @"<View> 
            <Query> 
                <Where><Eq><FieldRef Name='Country' /><Value Type='Lookup'>France</Value></Eq></Where>
            </Query> 
          </View>";
    ListItemCollection listItems = spList.GetItems(camlQuery); 
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

But the REST snippet is a bit different:

.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Cities')/Items?
      $expand=Country/Title&$filter=Country/Title eq 'France'", 
    type: "GET",      
    headers: {"accept": "application/json;odata=verbose"},
    success: onDataReturned;
    error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The construction of the REST call is explained in more details in the blog post of Andrew Connell “Applying Filters to Lookup Fields with the SharePoint 2013 REST API“.

Cross-site lookup fields

Some lookup fields get there data from a parent site. In that case the field definition contains a WebId with the Guid from the SharePoint site where the lookup list is located. CAML queries based on cross-site lookup fields can also be handled by the CAML Designer. The CAML itself is the same as for a normal lookup field; only the values need to be gathered from a different web.

DateTime fields

Filtering on a DateTime field is a bit special in CAML, so the user interface of a DateTime field offers a number of additional configuration options.

CD2013 datetime field

You can choose to query on today’s date or on a specific date. If you choose for today’s date, the CAML looks as follows:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'><Today /></Value>
     </Lt>
 </Where>

You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days. You can also add an offset to the date:

CD2013 datetime field 2

Your CAML then looks as follows:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'><Today Offset='5' /></Value>
     </Lt>
 </Where>

If you choose to filter on a specific date, a calendar is displayed.

CD2013 datetime field 3

Pick a date from the calendar and watch the CAML query change:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'>2012-12-17T12:00:00</Value>
     </Lt>
 </Where>

This only works on dates. This query will return all list items with a start date before May 17th 2012, but not those starting before 12 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element or on the Value element (I tested it out, it works both ways):

 <Where>
     <Lt>
        <FieldRef Name='StartDate' />
        <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-17T12:00:00</Value>
     </Lt>
 </Where>

When you click the Include Time Value check box in the user interface, you will be able to enter a time value in the format xx:xx:xx:

CD2013 datetime field 4

You can view the code snippet for the Server object model:

SPList spList = spWeb.Lists.TryGetList("Tests"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Query =    @"
      <Where>
         <Lt>
            <FieldRef Name='StartDate' /> 
            <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-12-17T10:30:00Z</Value>
         </Lt>
      </Where>";
    SPListItemCollection listItems = spList.GetItems(qry); 
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query> 
               <Where><Lt><FieldRef Name="StartDate" />
              <Value Type="DateTime" IncludeTimeValue="TRUE">2012-12-17T10:30:00Z</Value></Lt></Where>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

And the REST snippet looks as follows:

.ajax({     url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle(Tests)/Items?
       $select=Title,StartDate&$filter=StartDate lt '12/05/2012 10:30:00'", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

If you need to pass your CAML query throught the lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
 queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
   + "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

User fields and UserMulti fields

When you choose a User field or UserMulti field to filter on, you can choose between filtering on the current user, on a specific user, or on membership (read the blog post of Christopher Clementen where he clearly explains how the <Membership> element works).

User field

If you choose to filter on the current user, your query will look as follows:

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' />
         <Value Type='Integer'>
            <UserID />
         </Value>
      </Eq>
   </Where>

If you choose to filter on a specific user then you can enter or the name of the user or its ID. If you enter the name of a user, your query looks as follows.

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' />
         <Value Type='User'>Karine Bosch</Value>
      </Eq>
   </Where>

If, at the other side, you want to filter on the user ID, the following query is generated, indicating that you are passing an ID:

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' LookupId='True' />
         <Value Type='Integer'>4</Value>
      </Eq>
   </Where>

If you choose to filter on Membership, you get an additional dropdown where you can select one of the following options:

  • CurrentUserGroups: this will retrieve all tasks that are assigned to groups to which the current user belongs.
  • SPWeb.Groups: this will retrieve all tasks that are assigned to groups.
  • SPWeb.AllUsers: this will retrieve all tasks that have been assigned to users and not to groups.
  • SPWeb.Users: this will retrieve all tasks that have been assigned to users that don’t belong to a specific group but that have been granted access to the site directly.
  • SPGroup: this will retrieve tasks that have been assigned to a specific group.

User field - membership

We’ve also foreseen a small description field that explains the selected option:

User field - membership CurrentUserGroups

When selecting the SPGroup option, another additional dropdown becomes available. This dropdown will list all groups:

User field - membership SPGroup

Selecting a group from this dropdown will retrieve all tasks that have been assigned to the selected group. The CAML query looks like the following:

   <Where>
      <Membership Type='SPGroup' ID='5'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

When you select the CurrentUserGroups option, the CAML query will look as follows:

  <Where>
      <Membership Type='CurrentUserGroups'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

For the SPWeb.Groups option, the CAML query looks like this:

  <Where>
      <Membership Type='SPWeb.Groups'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

For the SPWeb.AllUsers options, the following CAML query is generated:

  <Where>
      <Membership Type='SPWeb.AllUsers'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

And when you select the SPWeb.Users option, you will get the following CAML:

  <Where>
      <Membership Type='SPWeb.Users'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

Membership queries are a special case for REST requests; in this case the REST request needs to pass the CAML query itself. Additionally this REST request needs to be executed as a POST. This is well described in this blog post of CleverWorkarounds. Such a REST snippet generated by the CAML Designer looks like this:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Tasks')/GetItems(query=@v1)?@v1={\"ViewXml\":\"<View><Query><Where><And><Neq><FieldRef Name='Status' /><Value Type='Choice'>Completed</Value></Neq><Membership Type='SPWeb.Groups'><FieldRef Name='AssignedTo' /></Membership></And></Where></Query></View>\"}", 
   type: "POST", 
   headers: { 
         "X-RequestDigest": $("#__REQUESTDIGEST").val(), 
         "Accept": "application/json;odata=verbose", 
         "Content-Type": "application/json; odata=verbose" 
   }, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

Managed Metadata fields

You can also filter on managed metadata fields. If you select such a field from the list of available fields, you will get a text box and a more button.

Taxonomy Field

You can directly type in the term you want to filter on, but when you’re logged on using the SharePoint server object model and you click the … button, a dialog with a treeview appears. This treeview is populated with the values of the term store and the term set to which the field is configured:

CD2013 taxonomy popup

If your managed metadata field is a normal taxonomy field, the selected term will be displayed in the selected text box. This results in the following CAML:

 <Where>
     <Eq>
        <FieldRef Name='Technology' />
        <Value Type='TaxonomyFieldType'>SharePoint 2010</Value>
     </Eq>
 </Where>

The results grid looks like this in my case:

CD2013 taxonomy test

Retrieve a Managed Metadata field by its ID

You can also retrieve list data based on the ID of a taxonomy field. The difficulty here is that a term has a name and an guid in the term store. You can retrieve list data based on a term name but this is dangereous as term names can change over time.

Taxonomy Field

When a term is used to tag a list item, this term is stored in a hidden list on site collection level. At this time, the term gets an ID, which is stored internally as WssId. Select the Query by ID option to indicate that you want to filter on the WssId of the term. The value in the textbox will immediately be changed into its ID.

Taxonomy Field by ID

And also the query instantly changes as follows:

 <Where>
    <Eq>
       <FieldRef Name='Technology' LookupId='True' />
       <Value Type='Integer'>1</Value>
    </Eq>
 </Where>

It will retrieve the same list items as when you would have queried on the term “SharePoint”.

Multi-select Managed Metadata fields

If your managed metadata field is a multi select field, all selected terms are listed in the text box, separated by a + sign:

CD2013 taxonomy multi popup

This results in the following CAML query:

 <Where>
     <Eq>
        <FieldRef Name='MultiTechnology' />
        <Value Type='TaxonomyFieldTypeMulti'>Dev;IT Pro</Value>
     </Eq>
 </Where>

Of course, a where clause written like this does not always result in the expected results. In my case the results grid stayed empty. I could make it work by changing the operator from Eq to In.

CD2013 taxonomy multi in operator

This translates in the following CAML code:

 <Where>
      <In>
         <FieldRef Name='MultiTechnology' />
         <Values>
            <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
            <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
         </Values>
      </In>
   </Where>

And now I have results showing up in my result grid.

The snippet for the server-side object model looks like this:

SPList spList = spWeb.Lists.TryGetList("CAMLTestData"); 
if (spList != null) 
{ 
   SPQuery qry = new SPQuery(); 
   qry.Query = 
   @"   <Where>
      <In>
         <FieldRef Name='MultiTechnology' />
         <Values>
            <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
            <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
         </Values>
      </In>
   </Where>"; 
   qry.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='MultiTechnology' />"; 
   SPListItemCollection listItems = spList.GetItems(qry); 
}

The code snippet for the client-side object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("CAMLTestData"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query>
                <Where>
                   <In>
                       <FieldRef Name='MultiTechnology' />
                       <Values>
                           <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
                           <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
                       </Values>
                   </In>
                </Where>
             </Query>
             <ViewFields>
                 <FieldRef Name='Title' />
                 <FieldRef Name='MultiTechnology' />
             </ViewFields>
       </View>"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
 }

Taxonomy fields are a special case for REST requests; in this case the REST request needs to pass the CAML query itself. Additionally this REST request needs to be executed as a POST. This is well described in this blog post of CleverWorkarounds. Such a REST snippet generated by the CAML Designer looks like this:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/GetItems(query=@v1)?"
        + "@v1={\"ViewXml\":\"<View><ViewFields><FieldRef Name='Title' /><FieldRef Name='FirstName' /><FieldRef Name='Company' /></ViewFields>"
        + "<Query><Where><Eq><FieldRef Name='Technology' /><Value Type='TaxonomyFieldType'>SharePoint</Value></Eq></Where></Query></View>\"}", 
   type: "POST", 
   headers: { 
         "X-RequestDigest": $("#__REQUESTDIGEST").val(), 
         "Accept": "application/json;odata=verbose", 
         "Content-Type": "application/json; odata=verbose" 
   }, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

The snippet for the web services gives you this:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query"); 
queryNode.InnerXml = 
     "<Where><In><FieldRef Name='MultiTechnology' /><Values><Value Type='TaxonomyFieldTypeMulti'>Dev</Value>"
   + "<Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value></Values></In></Where>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='MultiTechnology' />";
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("CAMLTestData", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

If you’re connected using the Client Object Model, the support for managed metadata is rather limited. When you click the … button you will get a dialog that shows all terms that have already been used for the field:

When you’re connected using the SharePoint web services, you will only get a text box where you can enter a term.

The FileRef field

There is also a section below that details how to build queries to retrieve documents but there are cases that a simple query does the trick. In that case the users prefer to stay on the Where tab and to build a query based on the FileRef field. When you drag and drop the FileRef field on the Where tab, you get a lookup displaying all folders in the selected document library.

FileRef field

Multiple filter criteria

If you want to specify two filter criteria you also have to specify a join operator And or Or. By selecting a second field from the available fields panel, this join operator is added automatically. The default value is And.

CD2013 multiple where and

Click on the control to toggle it to Or.

CD2013 multiple where or

In CAML it looks as follows:

<Where>
   <Or>
      <BeginsWith>
        <FieldRef Name='Title'
        <Value Type='Text'>Test</Value>
     </BeginsWith>
     <Lt>
        <FieldRef Name='StartDate' />
        <Value Type='DateTime'><Today /></Value>
     </Lt>
   </Or>
</Where>

You can add as many filter criteria as need.

CD2013triple where

In CAML the filter criteria are nested in a very specific way:

 <Where>
      <Or>
         <And>
            <Eq>
               <FieldRef Name='MultiCountries' />
               <Value Type='LookupMulti'>Belgium</Value>
            </Eq>
            <Eq>
               <FieldRef Name='MultiCountries' />
               <Value Type='LookupMulti'>Luxembourg</Value>
            </Eq>
         </And>
         <Eq>
            <FieldRef Name='Title' />
            <Value Type='Text'>Test 1</Value>
         </Eq>
      </Or>
   </Where>

For each extra criterion you have to add an extra join operator at the outside of the query and add the criterion at the end:

 <Where>
    <Or>
        <Or>
           <And>
              <Eq>
                 <FieldRef Name='MultiLookup' />
                 <Value Type='LookupMulti'>Antwerp</Value>
              </Eq>
              <Eq>
                 <FieldRef Name='MultiLookup' />
                 <Value Type='LookupMulti'>Ghent</Value>
              </Eq>
           </And>
           <Eq>
              <FieldRef Name='MultiLookup' />
              <Value Type='LookupMulti'>Breda</Value>
           </Eq>
        </Or> 
        <Eq>
            <FieldRef Name='Country' />
            <Value Type='Lookup'>India</Value>
        </Eq>
    </Or>
</Where>

Remark REST snippets:

A where clause built up of multiple fields, does not seem to work with REST.

Remove a field

If you want to remove a field from the Where clause, just drag it back to the left fields list.

The QueryOptions

Executing a query is not only about CAML. When working with the SPQuery object you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element.

Remark: Not all query options work with REST snippets. I’ll mention each time what works and what not.

Include mandatory columns

When specifying a ViewFields clause, only values for these fields are returned, together with a few system columns like ID, Created and Modified. You can also indicate that you want to have the required fields returned too in the resultset. You can do this by setting the IncludeMandatoryColumns to true.

In the CAML panel you will see an additional <QueryOptions> node.

But in the server object model, this information must be passed by setting the IncludeMandatoryColumns property to true:

SPList spList = spWeb.Lists.TryGetList("Tests"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Query =    @"
      <Where>
         <Lt>
            <FieldRef Name='StartDate' /> 
            <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value>
         </Lt>
      </Where>";
    qry.ViewFields = "<FieldRef Name="Title" /><FieldRef Name="StartDate" /><FieldRef Name="Countries" />"; 
    qry.IncludeMandatoryColumns = true;
    SPListItemCollection listItems = spList.GetItems(qry); 
}

If you need to use the lists.asmx web service to execute your CAML query, you have to pass a QueryOptions node:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
 queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
   + "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";

XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Countries' />"; 

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<IncludeMandatoryColumns>True</IncludeMandatoryColumns>"; 

System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Remark: this options doesn’t seem to work with the Client Object model and with REST.

Row Limit

Another query option is the row limit. It can be used to limit the number of rows returned in the result set.

CD2013 rowlimit

When working with the server object model, you can set the RowLimit property of SPQuery:

 qry.RowLimit = 2;

When working with the web services, you have to pass the value as follows:

System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

Also with the Client Object Model you can specify a row limit:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query> 
               <Where><Lt><FieldRef Name="StartDate" />
              <Value Type="DateTime" IncludeTimeValue="TRUE">2012-05-15T10:30:00Z</Value></Lt></Where>
             </Query>
             <RowLimit>2</RowLimit>
       </View>"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

And also REST requests can send the row limit to the server by adding $top=3 to the URL:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title&$top=3", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

For a detailed explanation of the other query options like, I refere to my other blog post on the CAML Designer for SharePoint 2010. These query options only work for the server object model and the web services. They don’t work for the client-side object model nor for REST requests.

Files and folder options

There are also a number of query options for working with files and folders in a document library. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with all fiels and folders from the root folder. In my case, this is the content of the root folder of my Shared Documents library:

CD2013 all files and folders in rootfolder

The user interface of the CAML Designer gives you a wide range of options that you can configure.

CD2013 all files and folders query options

Clicking the first option “Query all files and folders in root folder” will result in an empty query as this is the standard behavior.

The same query would look like the following for the server-side object model:

SPList spList = spWeb.Lists.TryGetList("Documents"); 
if (spList != null) 
{ 
   SPQuery qry = new SPQuery(); 
   qry.Query = 
   @"   <OrderBy>
      <FieldRef Name='Modified' Ascending='FALSE' />
   </OrderBy>"; 
   SPListItemCollection listItems = spList.GetItems(qry); 
}

The code snippet for the client-side object model for .NET looks as follows:

ClientContext clientContext = new ClientContext("your site");
 Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Documents");
 clientContext.Load(spList); clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0)
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query>
                <OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
 }

And the web services code snippet is the following:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Documents", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

REST reacts a bit differently: if you send a normal request (as described above) to a document library, you will get all files and folders in all sub folders in return. You will find more information on REST in a future article. If you want to query all files and folders in the root folder of a document library with REST, your request needs to look as follows:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Documents')/RootFolder", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

If you only want to retrieve the folders in the root folder, you have to add a Where clause that indicates that you only want to query the folders:

 <Where>
     <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>1</Value>
     </Eq>
 </Where>

In my case this results in the following result set:

This CAML query is generated for you by clicking the second option “Query all folders in root folder“. The generated code snippets are the following:

Server-side object model:

SPList spList = spWeb.Lists.TryGetList("Documents"); 
if (spList != null) 
{ 
   SPQuery qry = new SPQuery(); 
   qry.Query = 
   @"   <Where>
      <Eq>
         <FieldRef Name='FSObjType' />
         <Value Type='Integer'>1</Value>
      </Eq>
   </Where>
   <OrderBy>
      <FieldRef Name='Modified' Ascending='FALSE' />
   </OrderBy>"; 
   SPListItemCollection listItems = spList.GetItems(qry); 
}

Client-side object model for .NET:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Documents"); 
clientContext.Load(spList); clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query>
                <Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where><OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery(); 
}

Web Services:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where>"
        + "<OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Documents", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

REST:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Documents')/RootFolder/Folders", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

Selecting the option “Query all files in root folder” generates this CAML:

 <Where>
     <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>0</Value>
     </Eq>
 </Where>

To get files and folders from the root and its sub folders with CAML, you can set the ViewAttributes property. This property is not translated in REST because a standard REST request already returns files and folders from the whole folder structure of the queried document library. As this property works as before, I refer to my article CAML Designer for SharePoint 2010 for more details.

But you can also query a sub folder of a document library. The CAML Designer offers an option where you can fill out the folder on which you want to query:

CD2013 queroptions specific folder

When you check the Query sub folder check box, an additional part of the query options control is expanded. You can fill out the relative path of the sub folder, starting with the name of the document library:

CD2013 queroptions specific folder 2

But the CAML query will only be constructed and executed after you selected one of the radio buttons below the check box. When you select the first option to query all files and folders in this folder, your CAML only contains the following:

<QueryOptions>
     <Folder>/Shared Documents/Folder A</Folder>
 </QueryOptions>

This translates in following snippet for the server object model:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder A"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

In the .NET client object model it looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
 camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder A";

    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

And the call to the GetListItems method on the lists.asmx web service looks like this:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<Folder>/Shared Documents/Folder A</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

The REST code looks like:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/GetFolderByServerRelativeUrl('/Shared Documents/Folder A')", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

Previous query returns all files and folders in the specified sub folder, but it is possible that you only want to query the files in this sub folder. In that case the CAML query contains an additional query options element:

 <QueryOptions>
     <ViewAttributes Scope='FilesOnly' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

The code snippet for the server object model looks like this:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.ViewAttributes = "Scope='FilesOnly'"; 
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder 1"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

The generated code snippet for the .NET client object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml = @"<View Scope='FilesOnly'></View>";
    camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder 1"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

The code snippet when working with the SharePoint web services is the following:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ViewAttributes Scope='FilesOnly' /><Folder>/Shared Documents/Folder 1</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

And the REST request looks like this:

$.ajax({
     url: _spPageContextInfo.webAbsoluteUrl 
          + "/_api/web/GetFolderByServerRelativeUrl('/Shared Documents/Folder A')/Files",
     type: "GET",
     headers: {"accept": "application/json;odata=verbose"},
     success: function (data) {
        if (data.d.results) {
           // TODO: handle the data
            alert('handle the data');
        }
     },
     error: function (xhr) {
        alert(xhr.status + ': ' + xhr.statusText);
     }
  });

It is also possible that you only want to query the files in this folder and its sub folders. In that case your CAML query changs into:

 <QueryOptions>
     <ViewAttributes Scope='Recursive' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

The code snippet for the server object model looks like this:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.ViewAttributes = "Scope='Recursive'"; 
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder 1"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

The generated code snippet for the .NET client object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml = @"<View Scope='Recursive'></View>";  
    camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder 1"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

The code snippet when working with the SharePoint web services is the following:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ViewAttributes Scope='Recursive' /><Folder>/Shared Documents/Folder 1</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

This seems not to be possible with REST.

If you want to query all files and folders in this folder and its sub folders. In that case you have to set the scope to RecursiveAll:

 <QueryOptions>
     <ViewAttributes Scope='RecursiveAll' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

Also here it seems not possible with REST.

31 thoughts on “Caml Designer 2013

    • Parth Patel

      Its a great tool 🙂 Helps a lot but unfortunately it does not work with Web Application on which Form Based Authentication is enabled. Getting below error in Log file.

      LoginScreen: Failed to loginThe remote server returned an error: (403) Forbidden. – at System.Net.HttpWebRequest.GetResponse()

      Is there any setting which is missing ? Please help.

      • meligo

        hey, I will see if we have tested this and come back to this.

        kind regards

      • Parth Patel

        Any updates on issue reported of this tool with FBA Web Application

  1. Howard Bash

    First let me say that this is a great tool. I have been using it on and off for several years and the Office365 doesn’t disappoint.

    I am having an issue though with the SharePoint Blog Template Comment list. When I try to set the where clause for the Post ID, the field doesn’t allow me to enter a value.

    Also, I am finding once I add a column for a where clause, I cannot remove it. The only way I have been able to “clean” this up is to select another list and then go back to the list of interest.

    • meligo

      hey Howard

      We will investigate this issue, I’ve sended it to Karine..
      It is true that you can only select fields but not remove them again. We are working on this to implement it into the next update which is long overdue.

      kind regards

    • David Piscopo

      I am having the same issue. This occurs for ViewFields, Order By and Where. It used to work for previous versions!

  2. Fernando Hunth

    Will you add the feature to make JOINs ?

    • meligo

      hey Fernando

      We have asked ourselves this question as well. Trouble is making it visible and workable via a kind of wizard. So at the moment it is not in our planning on creating this, first is making the product stable and than we will look back at this.

      Kind regards
      Andy

  3. Howard Bash

    Hi,

    I just installed CAML Designer (downloaded zip and placed in folder). When I launch it the initial designer page displays and then it closes.

    Please advise. I am running on windows 10 pro.

    Thanks,
    Howard

  4. Howard Bash

    Hi,
    When I launch CAML Designer on another machine running Windows 10 Pro (as usual), the start screen pops up, closes and the program doesn’t launch.

    Please advise.

    Thanks,
    Howard

  5. David Piscopo

    Hi, Is there a way to select all fields (Viewfields) in one hit rather than selecting fields one by one and dragging them to the right. Sort of get RSI on a list with a large number of columns

    • meligo

      hey David

      Sadly not but next week I am going to sit together with Karine to talk about vNext of the tool. All these issues we will take into account to fix (most likely a new platform instead of an update of the WPF)

      kind regards
      Andy

  6. Yu

    Does this work with SharePoint 2016 ?

    • meligo

      hey Yu

      It should work if you are selecting SP2013. Authentication and CSOM haven’t changed much with this version.

      kind regards
      Andy

  7. Hi,
    I was using this tool before without any errors, but since recently i cannot login to my sharepoint site(Office365), it says invalid credentials. But my credentials are correct and even any of the global administrators cannot connect to the site with the tool (Same error message). The response which im getting after navigating to “/_vti_pvt/service.cnf” is
    “vti_encoding:SR|utf8-nl
    vti_extenderversion:SR|16.0.0.6216 ”

    Would appreciate your support on this.

    Thank you.
    Nevertheless its a great tool.

    Regards,
    Sudan.

    • meligo

      hey Sudan

      Did the authentication changed to O365, now working via ADFS?

      I will check in the code how this works (it’s been a while) and see what things we need to do to fix this.

      kind regards

      • Arvinda Sudan

        Hey Meligo,
        First of all thank you so much for taking a moment to reply for my question. Nope from the begining itself it was via ADFS only. It worked perfectly before. Also is there any options that has to be checked in my SharePoint end? if there is any option that has to be enabled to connect with this tool i can check on it.

        Thanks again,
        Sudan.

      • Arvinda Sudan

        Hey Meligo,

        Apparently had to enable(True) “LegacyAuthProtocolsEnabled” in my sharepoint online with the powershell. And i installed “SP 2013 Server SDK”(as per https://www.biwug.be/resources) in my pc and then i was able to authenticate to Sharepoint via CAML designer 2013. Thank you so much,
        Its a wonderful product.

        Sudan.

        • meligo

          hey Sudan , are you able to login now with the updated CamlDesigner?

          kind regards

          • Arvinda Sudan

            Hey Meligo,

            I haven’t tried the updated version, i will check and let you know.

            Thank you,
            Sudan.

  8. John Doe

    Hi
    In the Where section if you select a field as a condition, and then you realize that you are wrong, it is not possible to remove it.
    That is to say once you pass it to the right side you can not return it to the left side.
    Is it planned to repair this problem?
    Thanks and regards

    • meligo

      hey

      This is already fixed in the beta release but we still need to roll it out.

      kind regards

  9. Pablo

    Hi! thanks for this app!
    I’d like to know if the code is available, because I’d like to collaborate to improve it.

    • meligo

      hey

      I will talk it over with Karine and will come back to this 🙂

      kind regards
      Andy

  10. Thanks for the tool! Could you please add one more authentication option? I cannot connect to my SharePoint site via CamlDesigner, but I can do this via a console app with the additional request handler:

    using (var ctx = new ClientContext(siteUrl))
    {
    ctx.ExecutingWebRequest += TenantCtx_ExecutingWebRequest;
    . . . . .
    }
    private static void Ctx_ExecutingWebRequest(object sender, WebRequestEventArgs e)
    {
    e.WebRequestExecutor.WebRequest.UserAgent = “Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)”;
    e.WebRequestExecutor.WebRequest.Headers.Add(“X-FORMS_BASED_AUTH_ACCEPTED”, “f”);
    }

    • meligo

      hey

      I will check tonight if it’s easy to implement, if possible can I contact you directly to test it?

      kind regards
      andy

      • Burst

        Yes, sure. I’d be happy to

  11. […] A detailed explanation on how to use the tool can be found here. […]

  12. Thierry

    Seems like a great tool but having various issues which makes it unusable for me.

    1. Cannot delete a field that’s been dragged over to the selected part. This applies to View, OrderBy, Where, etc…
    2. Duplicate field names are displayed and you have no idea which one is which.
    3. Why aren’t fields like FSObjType no listed

    Modifying the text does not reflect when running a testing and since I can’t delete fields, it means that every time I have to switch over to another lists and start over which isn’t feasible. Hopefully these few issues will be resolved over time.

  13. Matthew

    I have several different task lists in an O365 SharePoint site. I want to display links to task lists that contain overdue tasks.

    I have the logic set to display in a Content Query Web Part where Due Date < Today, and Task Status =! Completed.

    This displays all the individual tasks that are overdue.

    However, I actually want to display links to the entire task lists that contain overdue tasks, rather than the tasks themselves.

    Does the CAML Designer have this capability?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>