CAML Query Builder in SharePoint Online + Examples

Want to learn about CAML query builder? How to use CAML query builder in SharePoint Online? Keep reading this tutorial to learn more about SharePoint CAML query builder, and various SharePoint CAML query examples.

We will see here how to use u2u CAML query builder for SharePoint Online.

What is CAML Query in SharePoint?

CAML stands for Collaborative Application Markup Language, and we use CAML to define queries against SharePoint list data or library data. And then we can use the SharePoint CAML query with CSOM or JavaScript or Server Object model. CAML is an XML-based query language.

Here is a SharePoint CAML query example.

<Query>
   <Where>
      <Eq>
         <FieldRef Name='Title' />
         <Value Type='Text'>dummy@gmail.com</Value>
      </Eq>
   </Where>
   <OrderBy>
      <FieldRef Name='Title' Ascending='True' />
   </OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='Title' />
</ViewFields>
<QueryOptions />

As a SharePoint developer, you can write the above SharePoint CAML query or we can use the SharePoint CAML query builder to write it.

SharePoint Online caml query builder

We can use the U2U CAML Query Builder for SharePoint 2013 to write the CAML query for SharePoint 2013, 2016, SharePoint 2019, or SharePoint Online. This tool will be helpful to create and test SharePoint CAML Queries.

This is a free standalone application built with the client object model code that we can install on your desktop. To use it, you just need to install Microsoft .NET Framework 4.5 (x86 and x64) on your system.

We can use the same SharePoint CAML query builder to connect with SharePoint Online and SharePoint on-premises versions.

If you are still using SharePoint 2010, you can download the U2U CAML Query Builder Solution Package for SharePoint 2010.

Double-click on set up and then you can choose from one of the below options:

• Connect to SharePoint Online: Choose this option if you want to connect to a SharePoint Online site, and then choose Custom credentials. Then, provide the username and password.
• Connect to SharePoint On Premises: Choose this option to connect to a SharePoint on-premises site like SharePoint 2013, SharePoint 2016, or SharePoint 2019.

Once you connect to the SharePoint Online site, you can see all the lists and libraries from the SharePoint Online site. Select the list or library for which you want to write the query.

Once you select the list, click on the New query button or Query with ViewFields button to build your CAML query. You can see a SharePoint online CAML query example, which is created using CAML query builder sharepoint online.

This is how to use the CAML Query Builder in SharePoint Online.

CAML query example (SharePoint CAML query example)

Let us check the CAML query example, and we will also see how to use caml query tool in SharePoint Online.

Recently, one professional who enrolled in my premium SharePoint Online Developer training course asked me how to use the CAML filter condition in SharePoint Online.

Here, I have two SharePoint lists:

• User Emails (Which is the Master List and it contains a few email addresses)
• Registration List (If the user is presented in the User Emails list, then the user can be registered to this Registration list, and if the user is not presented in the above list then the user cannot be registered here)

Here is both the lists in SharePoint Online.

The other list is below:

So here I have created a Windows application using Visual Studio 2019, which will have the below controls:

• Textbox (Here, the user will enter an email id)
• Button (User will click on the Submit button)
• Label (If the email is not presented in the User Emails list, then it will say you can not register; it will save the email into the Registration List and display a successful message).

The form will look like the following:

So here, we will use CAML query builder to create SharePoint CAML query filter conditions. We will use here C#.Net code (Client object model (CSOM)).

Here, when a user enters an email address, we will use the CAML query with CSOM to check if the email address is presented in the User Emails list or not.

If the user exists, then we will insert the item to the Registration list with the CSOM code.

SharePoint Online CSOM CAML query filter example

Now, let us check out the SharePoint Online CSOM CAML query filter example.

Here, I am using Visual Studio to create the Windows application. Open Visual Studio and then click on Create a new project like below:

Then select Windows Forms App (.NET).

Then, give a Project name, choose the location, and provide a Solution name. Then click on the Create button.

This will create the solution.

Once the solution is created, the first thing we need to add is the two DLLs below:

• Microsoft.SharePoint.Client
• Microsoft.SharePoint.Client.Runtime

If you have installed SharePoint Online Client Components SDK in your local system, then you can refer to the above dlls from your system.

You can also refer to the DLLs from the NuGet packages.

Then, we need to design the form. Here, from the Toolbox, I have added a textbox button and two labels.

The form looks like the following:

Then you can write the code on the button click, and the complete code looks like below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.SharePoint.Client;
using Form = System.Windows.Forms.Form;

namespace SharePoint_Client_Demo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
           bool exists= IsEmailExists();
            if(exists==true)
            {
                InsertItem();
                lblResult.Text = "User registered successfully!";
            }
            else
            {
                lblResult.Text = "User email id not exists in the master list.";
            }
        }

        bool IsEmailExists()
        {
            bool isexists = false;
            using (ClientContext ctx = new ClientContext("https://tsinfotechnologies.sharepoint.com/sites/SPGuides/"))
            {
                try
                {
                    ctx.Credentials = new SharePointOnlineCredentials("bijay@tsinfotechnologies.onmicrosoft.com", GetSPOSecureStringPassword());
                    Web web = ctx.Web;
                    List list = web.Lists.GetByTitle("User Emails");
                    var q = new CamlQuery() { ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+ txtEmailID.Text +"</Value></Eq></Where></Query><ViewFields><FieldRef Name='Title' /></ViewFields><QueryOptions /></View>" };
                    var r = list.GetItems(q);
                    ctx.Load(r);
                    ctx.ExecuteQuery();
                    if(r.Count>0)
                    {
                        isexists = true;
                    }

                }
                catch (Exception ex)
                {
                    isexists = false;
                }
                
            }
            return isexists;
        }

        void InsertItem()
        {
            using (ClientContext ctx = new ClientContext("https://tsinfotechnologies.sharepoint.com/sites/SPGuides/"))
            {
                try
                {
                    ctx.Credentials = new SharePointOnlineCredentials("bijay@tsinfotechnologies.onmicrosoft.com", GetSPOSecureStringPassword());
                    Web web = ctx.Web;
                    List list = web.Lists.GetByTitle("Registration List");
                    ListItemCreationInformation newlistitem = new ListItemCreationInformation();
                    ListItem item = list.AddItem(newlistitem);
                    item["Title"] = txtEmailID.Text;
                    item.Update();
                    ctx.ExecuteQuery();                   
                }
                catch (Exception ex)
                {
                   
                }

            }
        }

        private static SecureString GetSPOSecureStringPassword()
        {
            try
            {
                var secureString = new SecureString();
                foreach (char c in "**********")
                {
                    secureString.AppendChar(c);
                }
                return secureString;
            }
            catch
            {
                throw;
            }
        }
    }
}

Now, you can run the Windows form application, which will open the Windows form like below. You can see here that I entered an email ID, and it displayed the message “User email ID not exists in the master list” because the email ID is not in the master list.

Below the screen, you can see I enter an email address, and that is presented in the master list, so the email is added to the Registration list.

Also, if you open the SharePoint Online list, you can see the email ID entered in the SharePoint list.

This is how we can work with CAML query filter conditions with CSOM in SharePoint Online.

CAML Query for SharePoint Boolean Field using Client Object Model (CSOM)

Now, let us see how to use CAML query for boolean fields in the SharePoint client object model (csom) for the SharePoint Online site.

We had one document library where we added one field, “IsCopied” which is of type Boolean. We wanted to query from the SharePoint document library based on True/False. So, we wrote the query below:

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>TRUE</Value></Eq></And></Where></Query></View> }

But it did not return the result for us. We thought it would return results where IsCopied is TRUE.

Actually, the Boolean field in SharePoint works in 1 (TRUE) and o (FALSE).

So we have to modify the code like below:

CAML Query SharePoint Boolean Field for True Condition

We can write the below CAML query to check the boolean field for the True condition in SharePoint.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>1</Value></Eq></And></Where></Query></View> }

CAML Query SharePoint Boolean Field for False Condition

We can write the below CAML query to check the boolean field for False condition in SharePoint.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>0</Value></Eq></And></Where></Query></View> }

Now if you will query it will return the result correctly.

CAML Query SharePoint Boolean Field – Another Approach

Instead of writing Type=’Boolean’, we can change to Type=’Bool’ and then the query will work fine.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Bool'>True</Value></Eq></And></Where></Query></View> }

This is how to use CAML query for SharePoint boolean field in SharePoint Online.

Cannot complete this action. please try again. SharePoint CAML query

Let us see, how to solve cannot complete this action. please try again in SharePoint CAML query error. The error was coming in SharePoint Online, at the same time, you might have the same error while working with CAML in SharePoint.

Recently, I was trying to retrieve SharePoint Online list items using the client-side object model (CSOM) with CAML query.

Below is the SharePoint CSOM code.

string fieldInternalName="Tsinfo_EmailId";
string fieldValue="youremail@gmail.com";

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='" + fieldInternalName + "'/></Eq><Value Type='Text'>" + fieldValue + "</Value></Eq></Where></Query></View>";
						
var itemColl = oList.GetItems(camlQuery);
ctx.Load(itemColl);
ctx.ExecuteQuery(); 

Cannot complete this action. please try again an error in SharePoint Online

I was searching for a solution but didn’t get anything.

Then, I realized there was a problem with the CAML query syntax. I have added one extra before starting with <Value Type….

So I modified the CAML query syntax to like below, and it worked properly.

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='" + fieldInternalName + "'/><Value Type='Text'></Eq>" + fieldValue + "</Value></Eq></Where></Query></View>";
						
var itemColl = oList.GetItems(camlQuery);
ctx.Load(itemColl);
ctx.ExecuteQuery();

Now, the CSOM CAML code works perfectly.

Cannot complete this action. please try again error in CAML OR condition in SharePoint

If you are using the OR condition in CAML like below, then you will get the same error. Because in CAML, you can have only a maximum of two conditions within an or in Condition.

<Where>
    <And>
       <Or>
         <Eq><FieldRef Name='Approver1' /><Value Type='User'>User1</Value></Eq>
         <Eq><FieldRef Name='Approver2' /><Value Type='User'>User2</Value></Eq>
         <Eq><FieldRef Name='Approver3' /><Value Type='User'>User3</Value></Eq>
        </Or>
      <Eq><FieldRef Name='FullName' /><Value Type='Text'>Alex</Value></Eq>
     </And>
 </Where>

The above CAML query will give an error as “cannot complete this action. please try again.”

Modify the code like below, and it should work fine.

<Where>
  <And>
     <Or>
        <Eq>
           <FieldRef Name='Approver1' />
           <Value Type='User'>User1</Value>
        </Eq>
        <Or>
           <Eq>
              <FieldRef Name='Approver2' />
              <Value Type='User'>User2</Value>
           </Eq>
           <Eq>
              <FieldRef Name='Approver3' />
              <Value Type='User'>User3</Value>
           </Eq>
        </Or>
     </Or>
     <Eq>
        <FieldRef Name='FullName' />
        <Value Type='Text'>Alex</Value>
     </Eq>
  </And>
 </Where>

I hope this helps to solve “cannot complete this action. please try again.” SharePoint online error.

In this SharePoint tutorial, we learned how to use SharePoint CAML query filter condition with CSOM. And also, we discussed:

• What is SharePoint CAML query?
• How to use caml query builder in SharePoint Online
• How to create a windows application to work with CAML in SharePoint Online
• SharePoint Online CSOM CAML query filter example
• CAML Query for SharePoint Boolean Field using Client Object Model (CSOM)
• Cannot complete this action. please try again. SharePoint CAML query
• u2u CAML query builder for SharePoint online

You may like the following tutorials:

• Delete All SharePoint List Items using PowerShell
• JavaScript Object Model
• SharePoint Column Validation Formula Examples
• SharePoint Rest API
• Error=Value=QuotaExceeded in SharePoint